SQL Expression field throwing Error

[tekniks]

I had this SQL Expression field working in CR 8.5/Oracle 9.2.0.6 based on advise in the posts here since last 2yrs.
However when I switched the report to point to different database instance of the same Oracle version 9.2.0.6, it throws an error: Error in Compiling SQL Expression - Invalid Field found here.
Given below is the Syntax:

{fn SUBSTRING(PR_STATUS_VW."ACTUAL_KIT_NUMBERS",1 ,254 )}

The same Expression is working well in the same report with other database instance.
I tried re-creating the SQL Expression Fields by manually typing the field in but to no avail.

Any help will be greatly appreciated.

Tek

 

[synapsevampire]

You're probably using a different connectivity to the new database, and the connectivity doesn't like it.

A common mistake is to use the Oracle supplied ODBC driver instead of the Crystal Oracle ODBC driver.

In either case, use the real SQL rather than Crystal's version:

SUBSTR(PR_STATUS_VW.ACTUAL_KIT_NUMBERS,1 ,254 )

But again, you probably have the wrong connectivity, change it. Generally native is best.

-k

 

[tekniks]

Syn,

In both cases the Driver being used is CR ORACLE 8v3.6 for the connectivity, then why should there be an issue. Is it intrinsic to the SQL Expression field or the Report has to be re-designed??

Tek

 

[synapsevampire]

I would guess that you have the wrong syntax for the field name given what you've now shared.

Perhaps the table has a differeent name?

Try:

SUBSTR(ACTUAL_KIT_NUMBERS,1,254)

And in either case, test in Toad or SQL Developer first.

-k

 

[tekniks]

Syn,
Actually CR 8.5 doesn't like the Syntax
SUBSTR(ACTUAL_KIT_NUMBERS,1,254)
and it throws syntax error although it gives result back using TOAD as well as SQL.

I'm in dire need of the solution as this has to be promoted to production righ away.

Will appreciate a fix for this.

Thanks
Tek