SQL Expressions generating errors on CRXI and Oracle DB

[#1]

I'm having ongoing issues with using SQL Expressions, and I'm hoping that there's some help to be had here. I'm using Crystal v11.5.10.1263, which is SP4 (I know, I know, I'm working with my vendor to get it updated.), against an Oracle 9.2.0.6 DB. The problem is that I keep getting this error:

Error in compiling SQL Expression:

Failed to retrieve data from the database.

Details: ORA-00904:"PS_GENL_DEDUCTION"."EMPLID": invalid identifier

[Database Vendor Code: 904].

I get this across a wide variety of SQL Expressions. Here's the one that generated the error above:

CODE --> SQL

( select max (INNERALIAS."EFFDT") from "PS_GENL_DEDUCTION" INNERALIAS where INNERALIAS."DEDCD" = "PS_GENL_DEDUCTION"."DEDCD" and INNERALIAS."EMPLID" = "PS_GENL_DEDUCTION"."EMPLID" and INNERALIAS."EFFDT" <= SYSDATE ) 

It's clearly the reference to the table that's in the Database Expert that's causing the issue, as when I replace those table references with discreet values:

CODE --> SQL

( select max (INNERALIAS."EFFDT") from "PS_GENL_DEDUCTION" INNERALIAS where INNERALIAS."DEDCD" = 'C1002' and INNERALIAS."EMPLID" = '00021' and INNERALIAS."EFFDT" <= SYSDATE ) 

...it runs fine, and generates the value expected.

Oddly, other SQL Expressions that are quite similar run without issue. For example, this expression is from another report:

CODE --> SQL

( select max("PS_ACTN_REASON_TBL"."EFFDT") from "PS_ACTN_REASON_TBL" where "PS_ACTN_REASON_TBL"."ACTION" = "PS_DM_EMPL_HIST_VW"."ACTION" and "PS_ACTN_REASON_TBL"."ACTION_REASON" = "PS_DM_EMPL_HIST_VW"."ACTION" and "PS_ACTN_REASON_TBL"."EFFDT" <= SYSDATE ) 

...runs fine. There's another that links to the same view as this one does ("PS_DM_EMPL_HIST_VW") that generates the same error as above. I've tried using aliases and not, and using quotation marks and not...neither makes a difference.

This is a major issue for me, as I'm tasked with converting a stable of reports from an antiquated system to Crystal, and it's critical that I be able to verify that the data pulled is "top of stack" - that the date of a record is the max date for a particular entity, and that the sequence code is the maximum for a particular date. I know that I could accomplish this using subreports, but that's the difference between a report taking a few minutes to run and a few hours. I've tested the SQL code in another tool, and the issue definitely isn't there. I suspect that there's something about the way Crystal is communicating the table references to Oracle...has anyone else experienced this?

On a side note, I tried using similar SQL expressions in Crystal 2011 on an SQL Server DB, and it worked with no issues at all.

To summarize, my questions are:

1)  Is this a bug?  I've seen other threads online discussing this issue, but none have come to resolution.

2)  Could this problem be caused by the fact that they're running SP4?  Might upgrading to SP6 resolve the issue?

2)  Why will very similar SQL Expressions behave differently in different reports, one running properly and the other not?  I've tried to find differentiators between them, and I haven't been able to.

3)  Does anyone know of syntax changes I can make to resolve this issue?

Thanks!

[Former Member · Accepted solution]

FYI, I've never gotten correlated SQL Expressions to work against an Oracle database.  I've used SQL Expressions for years (even gave a presentation on them in 2006 for the user conference) and have successfully correlated report data to the external query in SQL Server, DB2 and  other databases, but not Oracle.  I haven't tried them with every combination of database connection type and driver, but I've tried enough to get me to just pass and go straight to SQL Commands as my report data source when I need the functionality.

[Former Member · Accepted solution]

Yeah, that's what it's looking like.  It must have something to do with the way Crystal is sending the expression to the database when the expression is first saved, as that's where the failure takes place.  I submitted this issue to their idea board...I'm hoping the developers will find a fix.

[JWiseman · Accepted solution]

hey David,

have you tried to move the where clause syntax between your two tables to the join instead...not sure if it will work or not but perhaps worth a try?

e.g.

(
select max (INNERALIAS."EFFDT")
from "PS_GENL_DEDUCTION" INNERALIAS

INNER JOIN PS_GENL_DEDUCTION
ON INNERALIAS."DEDCD" = "PS_GENL_DEDUCTION"."DEDCD"
and INNERALIAS."EMPLID" = "PS_GENL_DEDUCTION"."EMPLID"
WHERE INNERALIAS."EFFDT" <= SYSDATE
)

also as a test, if you were to

     1) take the sql from your report  and

     2) create a new report off of a comand and

     3) then try to add in the sql from your expression (you'll probably have to modify it slightly) as a subquery,

is there an error returned?

[Former Member · Accepted solution]

Thanks for the thoughts.  I hadn't tried using the explicit join eyntax, but I just tried it and it generalted the same "Invalid Identifier" error that the implicit syntax did.

I have tried similar queries as commands, and they do work.  As I said above, I think the problem is that, when the SQL Expression is saved, it isn't done in the context of the larger report, so the database doesn't have the whole picture.  It doesn't have the reference to the included report that's in the main report, so it sees it as an invalid identifier.  I've also tried including the table in the FROM clause of the SQL Expression, and it does save without the error, but when the report runs the expression doesn't link to the main report the way it should - it runs with a separate instance of the table.

[JWiseman · Accepted solution]

that's too bad about the error. the unfortunate part about this is that a sql expression with a select in it is not officially supported so getting customer assurance to look at this would be difficult...i've tried to convince product group for crystal to support this as a sql expression can be quite valuable especially when they avoid duplication of records, provide performance gains, etc.  and rewriting a report to use a command object is not always an attractive option.

the only other thing i can suggest is to try weed through the past forum posts to see if there's another oracle poster who has found a solution...i know there's been a few. and you may also wish to add this to idea place