Having problem getting complex SQL to run In CR2013

[Former Member]

I just installed Crystal Reports 2013, we didn't have Crystal Reports before this installation.  I am connecting to an Oracle 11g database with hundreds of tables.  We will be doing a lot of reports with complex SQL.  I have not been able to get Crystal Reports to like any complex SQL that I have created at this point.  I can do a select from a single table and it works fine.  Once I add tables and joins in the where clause, it tells me "missing expression'.  I can cut and paste the same SQL into a SQL*Plus window and it runs fine. The following is the select that I am using:

SELECT DISTINCT LIC.LIC_NBR, NAME.ORG_NME, LIC.ORIG_DTE, ADDR.STR_ADDR_NBR, ADDR.ADDR_LINE1, ADDR.ADDR_LINE2, ADDR.ADDR_CTY, ADDR.ST_CDE,
        ADDR.ADDR_ZIP, PHNE.PHNE_NBR
  FROM VR_001.LIC, VR_001.ADDR, VR_001.PHNE, VR_001.NAME
WHERE LIC.CLNT_CDE = '3901'
   AND ADDR.SUBSYS = 'L'
   AND LIC.XENT_ID = ADDR.XENT_ID
   AND LIC.XENT_ID = PHNE.XENT_ID
   AND LIC.XENT_ID = NAME.XENT_ID
   AND NAME.CUR_NME_IND = 'Y'
ORDER BY LIC.LIC_NBR;

Does anyone have any suggestions as to what to do? Thanks

I received a replies from Jamie Wiseman & G Dhananjay yesterday, both of which I tried and I still get the same results, "Error in compiling SQL Expression: Failed to retrieve data from the database. Details: ORA-00936: missing expression [Database Vendor CODE:936 ]."

[former_member292966]

Hi Dwaine,

If the query works in SQL*Plus then I suspect the database driver Crystal is using to connect to Oracle may be bad.

Are you using ODBC to connect to Oracle?  Crystal installs an Oracle driver as well.  Try changing to a different driver.

If you have multiple versions of Oracle installed, again, you may be using the wrong version of the driver.

Your example is pretty basic so Crystal should not be having this problem.  That's why I suspect the driver.

Good luck,

Brian

[Former Member]

Thanks Brian,

Oracle 11G database is a 64 bit, CR 2913 is 32 bit, so I had to install a 32 bit Oracle Client to even access the database.  I haven't tried to install an ODBC driver of any kind, just using what came with the CR 2013.

[JWiseman]

hi Dwaine,

first try Brian's suggestion. you may also wish to use the Wire Protocol (datadirect) driver for Oracle as it doesn't require a client.

if you still have the issue, create a new report off of tables instead of a Command. use the same tables & joins (that you have in the Command. i.e. establish the joins in the database expert

     LIC.XENT_ID = ADDR.XENT_ID

   AND LIC.XENT_ID = PHNE.XENT_ID

   AND LIC.XENT_ID = NAME.XENT_ID

add in the filters to the Report > Selection Formula > Record...i.e. establish a filter using the db fields and the values 3901, L, Y.

     {your LIC.CLNT_CDE field} = '3901'

       AND {your ADDR.SUBSYS field} = 'L'

       AND {your NAME.CUR_NME_IND field} = 'Y'

change the order using the Sort Expert using the lic nbr field.

go to the Database menu and choose Select Distinct Records.

preview the report and then go to the Database menu and choose Show SQL Query. copy this and then create a new report using a Command object. paste the syntax in as your base query. you can get rid of extras like quotes around table.field combos.

-jamie