CR Oracle error - Oracle Server driver

[sharinad]

Our system is oracle 8i and CR/CE 9. I have a report that runs fine using ODBC and the only thing I changed was the data source to use the Oracle Server driver instead. We have other reports using this driver that work. Errors after setting data source on all the tables: (on the client using crystal reports 9)
#1 Failed to open rowset
#2 Query engine error: 'ORA-0911: invalid character '
#3 Not Supported. Details: Fail to execute SQL Statement. OCI call: OCIStmtExecute

Any ideas? I am trying to speed up response time, and I thought this driver might help

 

[synapsevampire]

The report is likely using a data type unsupported by the Native Driver.

You might try CASTing the offending field using a SQL Expression. A common problem I had was Timestamp with Timezone, for which I would ste up a View to Cast it to a conventional datetime.

-k

 

[sharinad]

Thanks! I will try that. Unfortunately I can't use sql expressions as the report won't run on eportfolio then, and that is our distribution method.

 

[Turkbear]

Hi,
Can you post the Sql that Crystal creates ( using the Show Sql Query option under the database menu in CR)

Did you do a 'verify database' after changing drivers?

 

[synapsevampire]

I've used SQL Expressions on CE.

-k

 

[sharinad]

Yes, but the errors occur right after closing the datasource window.

Did you have to do anything special to get the sql expressions to work on ce?

Before:
SELECT "APVENMAST"."VENDOR_SNAME", "PURCHORDER"."PO_DATE", "PURCHORDER"."PO_NUMBER", "PURCHORDER"."VENDOR", "POLINE"."ITEM", "POLINE"."DESCRIPTION", "POLINE"."LINE_NBR", "POLINE"."EXTENDED_AMT", "POLINE"."QUANTITY", "MMDIST"."ACCT_UNIT", "MMDIST"."DIST_PERCENT", "MMDIST"."DIST_AMOUNT", "PURCHORDER"."PROCESS_LEVEL", "MMDIST"."DIST_QTY", "MMDIST"."ACCOUNT", "ICCATEGORY"."INV_ACCOUNT", "GLCHARTDTL"."ACCOUNT", "GLCHARTDTL"."ACCOUNT_DESC", "ICCATEGORY"."INV_ACCT_UNIT", "MMDIST"."ACTIVITY", "MMDIST"."DOC_TYPE", "MMDIST"."ACCT_CATEGORY", "ACACTIVITY"."DESCRIPTION", "ACACTIVITY"."ACCT_CATEGORY", ({fn LTRIM(
{fn LEFT("PURCHORDER"."PROCESS_LEVEL",2 )} *100 )}), ({fn IFNULL({fn SUBSTRING("MMDIST"."ACCT_UNIT",5 ,3 )}, {fn SUBSTRING("ICCATEGORY"."INV_ACCT_UNIT",5 ,3 )}
)})
FROM "LAWSON"."PURCHORDER" "PURCHORDER", "LAWSON"."POLINE" "POLINE", "LAWSON"."APVENMAST" "APVENMAST", "LAWSON"."MMDIST" "MMDIST", "LAWSON"."ITEMLOC" "ITEMLOC", "LAWSON"."GLCHARTDTL" "GLCHARTDTL", "LAWSON"."ACACTIVITY" "ACACTIVITY", "LAWSON"."ICCATEGORY" "ICCATEGORY"
WHERE (((("PURCHORDER"."COMPANY"="POLINE"."COMPANY") AND ("PURCHORDER"."PO_NUMBER"="POLINE"."PO_NUMBER")) AND ("PURCHORDER"."PO_RELEASE"="POLINE"."PO_RELEASE")) AND ("PURCHORDER"."PO_CODE"="POLINE"."PO_CODE")) AND ("PURCHORDER"."VENDOR"="APVENMAST"."VENDOR") AND ((((("POLINE"."COMPANY"="MMDIST"."COMPANY" (+)) AND ("POLINE"."PO_NUMBER"="MMDIST"."DOC_NUMBER" (+))) AND ("POLINE"."PO_RELEASE"="MMDIST"."DOC_NBR_NUM" (+))) AND ("POLINE"."PO_CODE"="MMDIST"."PO_CODE" (+))) AND ("POLINE"."LINE_NBR"="MMDIST"."LINE_NBR" (+))) AND ((("POLINE"."COMPANY"="ITEMLOC"."COMPANY" (+)) AND ("POLINE"."ITEM"="ITEMLOC"."ITEM" (+))) AND ("POLINE"."LOCATION"="ITEMLOC"."LOCATION" (+))) AND (("MMDIST"."ACCOUNT"="GLCHARTDTL"."ACCOUNT" (+)) AND ("MMDIST"."SUB_ACCT"="GLCHARTDTL"."SUB_ACCOUNT" (+))) AND ("MMDIST"."ACTIVITY"="ACACTIVITY"."ACTIVITY" (+)) AND ((("ITEMLOC"."COMPANY"="ICCATEGORY"."COMPANY" (+)) AND ("ITEMLOC"."LOCATION"="ICCATEGORY"."LOCATION" (+))) AND ("ITEMLOC"."GL_CATEGORY"="ICCATEGORY"."GL_CATEGORY" (+))) AND ("PURCHORDER"."PO_DATE">={ts '2004-07-01 00:00:00'} AND "PURCHORDER"."PO_DATE"<{ts '2004-08-01 00:00:00'}) AND ({fn LTRIM(
{fn LEFT("PURCHORDER"."PROCESS_LEVEL",2 )} *100 )})='6300'
ORDER BY {fn LTRIM(
{fn LEFT("PURCHORDER"."PROCESS_LEVEL",2 )} *100 )}, {fn IFNULL({fn SUBSTRING("MMDIST"."ACCT_UNIT",5 ,3 )}, {fn SUBSTRING("ICCATEGORY"."INV_ACCT_UNIT",5 ,3 )}
)}

After:
SELECT "APVENMAST"."VENDOR_SNAME", "PURCHORDER"."PO_DATE", "PURCHORDER"."PO_NUMBER", "PURCHORDER"."VENDOR", "POLINE"."ITEM", "POLINE"."DESCRIPTION", "POLINE"."LINE_NBR", "POLINE"."EXTENDED_AMT", "POLINE"."QUANTITY", "MMDIST"."ACCT_UNIT", "MMDIST"."DIST_PERCENT", "MMDIST"."DIST_AMOUNT", "PURCHORDER"."PROCESS_LEVEL", "MMDIST"."DIST_QTY", "MMDIST"."ACCOUNT", "ICCATEGORY"."INV_ACCOUNT", "GLCHARTDTL"."ACCOUNT", "GLCHARTDTL"."ACCOUNT_DESC", "ICCATEGORY"."INV_ACCT_UNIT", "MMDIST"."ACTIVITY", "MMDIST"."DOC_TYPE", "MMDIST"."ACCT_CATEGORY", "ACACTIVITY"."DESCRIPTION", "ACACTIVITY"."ACCT_CATEGORY", ({fn LTRIM(
{fn LEFT("PURCHORDER"."PROCESS_LEVEL",2 )} *100 )}), ({fn IFNULL({fn SUBSTRING("MMDIST"."ACCT_UNIT",5 ,3 )}, {fn SUBSTRING("ICCATEGORY"."INV_ACCT_UNIT",5 ,3 )}
)})
FROM "LAWSON"."PURCHORDER" "PURCHORDER", "LAWSON"."POLINE" "POLINE", "LAWSON"."APVENMAST" "APVENMAST", "LAWSON"."MMDIST" "MMDIST", "LAWSON"."ITEMLOC" "ITEMLOC", "LAWSON"."GLCHARTDTL" "GLCHARTDTL", "LAWSON"."ACACTIVITY" "ACACTIVITY", "LAWSON"."ICCATEGORY" "ICCATEGORY"
WHERE (((("PURCHORDER"."COMPANY"="POLINE"."COMPANY") AND ("PURCHORDER"."PO_NUMBER"="POLINE"."PO_NUMBER")) AND ("PURCHORDER"."PO_RELEASE"="POLINE"."PO_RELEASE")) AND ("PURCHORDER"."PO_CODE"="POLINE"."PO_CODE")) AND ("PURCHORDER"."VENDOR"="APVENMAST"."VENDOR") AND ((((("POLINE"."COMPANY"="MMDIST"."COMPANY" (+)) AND ("POLINE"."PO_NUMBER"="MMDIST"."DOC_NUMBER" (+))) AND ("POLINE"."PO_RELEASE"="MMDIST"."DOC_NBR_NUM" (+))) AND ("POLINE"."PO_CODE"="MMDIST"."PO_CODE" (+))) AND ("POLINE"."LINE_NBR"="MMDIST"."LINE_NBR" (+))) AND ((("POLINE"."COMPANY"="ITEMLOC"."COMPANY" (+)) AND ("POLINE"."ITEM"="ITEMLOC"."ITEM" (+))) AND ("POLINE"."LOCATION"="ITEMLOC"."LOCATION" (+))) AND (("MMDIST"."ACCOUNT"="GLCHARTDTL"."ACCOUNT" (+)) AND ("MMDIST"."SUB_ACCT"="GLCHARTDTL"."SUB_ACCOUNT" (+))) AND ("MMDIST"."ACTIVITY"="ACACTIVITY"."ACTIVITY" (+)) AND ((("ITEMLOC"."COMPANY"="ICCATEGORY"."COMPANY" (+)) AND ("ITEMLOC"."LOCATION"="ICCATEGORY"."LOCATION" (+))) AND ("ITEMLOC"."GL_CATEGORY"="ICCATEGORY"."GL_CATEGORY" (+))) AND ("PURCHORDER"."PO_DATE">=TO_DATE ('01-07-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "PURCHORDER"."PO_DATE"<TO_DATE ('01-08-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND ({fn LTRIM(
{fn LEFT("PURCHORDER"."PROCESS_LEVEL",2 )} *100 )})='6300'
ORDER BY {fn LTRIM(
{fn LEFT("PURCHORDER"."PROCESS_LEVEL",2 )} *100 )}, {fn IFNULL({fn SUBSTRING("MMDIST"."ACCT_UNIT",5 ,3 )}, {fn SUBSTRING("ICCATEGORY"."INV_ACCT_UNIT",5 ,3 )}
)}

 

[sharinad]

I think I might have discovered something. It looks like a patch was installed that now has different ORACLE commands - before when I had the following it was valid {fn IFNULL({fn SUBSTRING("MMDIST"."ACCT_UNIT",5 ,3 )}, {fn SUBSTRING("ICCATEGORY"."INV_ACCT_UNIT",5 ,3 )}
)}
but now there doesn't seem to be an IFNULL available, and when I click on SUBSTRING I no longer get the {fn substring(,) } like before but just SUBSTRING(,)

we are now
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production

Does anyone know if there is an equivalent to IFNULL?

 

[sharinad]

After the completed upgrade, both the native driver and the sql expressions are now working. Anyone have a suggestion for complex sql expressions since there doesn't seem to be a if or case statement available from within crystal using the native driver for oracle 9i for sql expressions?