creating SQL to convert memo to text type

[fancyface]

Hi there. I'm using Crystal 10 off an Oracle database. I have a memo type field. To get around a truncating issue I'm looking at trying to CAST the memo field to text. My memo field will never be more than 256 characters anyways. I've been reading some posts to use the CAST function but I need some help please. Do I do this is a SQL view within my select statement? I've been struggling with the right syntax. I am confused as to whether to use the cast function in a SQL expression within the Crystal Report or PRIOR to the designer stage, i.e. use it in a view. Thanks.

 

[synapsevampire]

CR 10 supported strings greater than 254 in length, so this shouldn't be required.

If there is a truncation issue, it may be the result of your Oracleclient or the type ofconnectivity being used from Crystal.

You should try the native (listed as Oracle Server) if you're using ODBC, or try ODBC if you're using Native.

In either case, post the actual data type of the field in Oracle, and yes, a CAST in a SQL Expression might work as well, the syntax of which should be something like:

CAST(table.field as varchar(256))

-k

 

[fancyface]

Thank you. From other posts which you have assisted on exploring the data connection, ODBC, etc. is not an option. When I try to create a SQL expression, it doesn't recognize the CAST command. I'm assuming you mean once in Crystal Designer create a new SQL expression? I would like to try the cast in my SQL view/query and then design a report tied to that. My struggle with is the syntax of selecting the table and using the cast all in one statement.

 

[synapsevampire]

I don't know what you mean by "From other posts which you have assisted on exploring the data connection, ODBC, etc. is not an option.". ODBC can work, I just don't suggets using it in older versions of CR dueto performance issues.

OK, well you need to post what you tried and where you tried it...

Download Toad or some such for developing queries, rather than using Crystal.

-k

 

[IanWaterman]

I think your best bet is to convert field using a view. No need to use CAST, TO_CHAR seems to do the job.

I just ran this simple create view script

create or replace view IJWTEST as
select documentid, batchno, to_char(rtftext) textchar from wprtfdocument

The field rtftxt is a clob and the view converted it into a VARCHAR2(4000)

YOu can then use this view inplace of the table and the filed should be available to work on in Crystal Formulae if that is what you are trying to achieve.

Ian

 

[fancyface]

Thank you Ian for being so helpful. I appreciate it. Synapse you have responded to me on every post in the last week that I have asked about with ODBC connections - that's what I was talking about.

 

[fancyface]

Ian, what do you mean run a view script? I am not allowed direct access to the server. I can only connect through a business view or designer.

 

[IanWaterman]

Perhaps I misread your post, I thought you could create Views on your Oracle database.

You would need to build a create view script similar to the one I posted earlier. Obviously you will need to do this within the constraints set by your Oracle DBA.

Alternatively
If your SQL is good enough you could actually build a SQL command to bring back all the data you require, which can be loaded within Crystal. Within that command you would then convert your clob/memofield To Char as above.

Ian

 

[synapsevampire]

As previously suggested, use a CAST.

The syntax should be:

CAST(table.field as varchar(256))

You say ODBC, etc. is not an option. So how are you connecting?

If you want to post specifics, someone can help. Note that Oracle doesnot have a MEMO type.

Try installing Toad to assist with sql development.

-k

 

[fancyface]

Sorry Ian, I wasn't clear enough. When I say I can create views I meant Business Views within the Crystal Enterprise. Sorry for the confusion Ian. As far as an Oracle View, I have to go through red tape with a DBA in our organization for that. I'm going to try a SQL command.

Here is my syntax so far:
SELECT A.GIFT_NO, A.EMPLID, A.COMMENTS
FROM PS_UWO_RECEIPT_DTL A


I'm struggling with the syntax on the A.COMMENTS field which is my memo field.

Ian can you assist further?

 

[fancyface]

Here is what I'm doing and I'm getting the error:
inconsistent data types...

SELECT A.GIFT_NO, A.EMPLID, CAST(A.COMMENTS as varchar(256)) FROM PS_UWO_RECEIPT_DTL A

 

[synapsevampire]

What is the Oracle data type of COMMENTS?

And are you going against a Business View, or?

Cast doesn't support CLOB, so use the to_char as Ian suggested.

-k

 

[fancyface]

could you please confirm my syntax within the select statement?

SELECT A.GIFT_NO, A.EMPLID, to_char(A.COMMENTS) FROM PS_UWO_RECEIPT_DTL A

is what I am putting... I still get the same error message of inconsistent data types.

My DBA tells me COMMENTS is of type LONG

As mentioned I'm trying this within a SQL command.

I'm at the point I'm about ready to buy someone a sports car if they can help me get this.

 

[synapsevampire]

Oh, I thought you said clob, I guess that was Ian...

Try:

substr(table.longfield,1,256)

-k

 

[fancyface]

Here's what I put:

SELECT A.GIFT_NO, A.EMPLID, substr(A.COMMENTS,1,256) FROM PS_UWO_RECEIPT_DTL A


still getting the inconsistent data types error

 

[synapsevampire]

Very strange.

You might try downloading Toad and using it to create your queries as it will show you the exact data types,and it's free to download.

-k

 

[fancyface]

I will try that. I've never heard of Toad. Thank you I will give that a try. It's sometimes a little frustrating with the differences between SQL syntax off an Oracle Database in a Crystal Application. I look forward to giving Toad a try. Thank you for your patience.

 

[synapsevampire]

btw, I'd start a new report and try it, perhaps the issue is that the dataset is already defined and the error is in Crystal.

btw, where are you placing this SQL? I assume a SQL Command, NOT a SQLExpression?

-k

 

[fancyface]

yes in a SQL command. In installed the Toad I think but it's impossible to figure out....

 

[IanWaterman]

If Crystal can connect to the database then Toad should link up easily.

Use same database name, user name and password as you use for Crystal and it should connect. You may want to discuss with your DBA first as they can be a bit twitchy about users have such powerful access to the database.

When connected run this query

desc PS_UWO_RECEIPT_DTL

That will detail exactlt what the comments field is. Then we can decide what your problem is.

You can then test your select statement and make sure it works in Toad then paste into Crystal Command.

K
It was me who introduced clob, I used to_char on my database and all memofields are stored as clobs.

Ian