Convert varchar2(2000) to varchar2(255) in a View?

[synapsevampire]

I need to supply a datasource for a Crystal Report, and I'd like to *convert* it to a varchar2(255) so that Crystal can do more with it, is there a way to do this within a view?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Turkbear]

Crystal should not care about the length, it will only print the size that actually has characters..If not, then use

{FIELD_NAME}[1 to 255]

in your report...

( Converting within a view would be:

create view shortfield as select substr(thefieldtoshorten,1,255) from thetable;

)
Using this will mean that no index on that field will be used, unless you create a function-based one..

hth,

 

[synapsevampire]

Thanks for the response, I hadn't thought of just using a substr, I thought that it would require a convert or some such.

Crystal cares about the length of fields. It allows you to display it if over 255, but you can't use it in a formula.

Thanks for the quick response.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Turkbear]

Hi again,

Correct, I meant in the report output...

The 'use in a formula' limitation is a failure of Seagate to keep up with Oracle - It is a holdover from the LONG datatype days of Oracle...

 

[synapsevampire]

It's a failure of Crystal to keep up with any database... Formulas are limited to 255 fields, and 255 output.

But they still have the most versatile report product on the market.

Hopefully CR 9 will address this, until then, I just break large fields into multiple 250's to work around this.

<shrug>

I just switched back to an Oracle contract after a couple of years of SQL Server, so I'll be popping in for more expertise.

Thanks again.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com