SQL Expressions generating errors on CRXI and Oracle DB

[Davest11]

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:

( 
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:

(
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:

(
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.

 

[Davest11]

Hmmm...is this just not being seen, or does no one use SQL Expressions? I've searched and found a few old threads on this subject, but none of them ever came to a resolution. Seems odd...this is a really powerful piece of functionality, if it really works.

 

[Davest11]

Well, that's disappointing...not a single reply? I used to use this site all the time back about six years ago, and it was always a great source of info. I tried to be as thorough with my post as possible...is there something missing?

 

[PeterDimitrov]

What is the report SQL?
Are you sure "PS_GENL_DEDUCTION" is part of the report ?

as a workaround (and if you have permissions) , you can try to create a view
select INNERALIAS."DEDCD",INNERALIAS."EMPLID" , max (INNERALIAS."EFFDT") as MaxEFFDT
from "PS_GENL_DEDUCTION" INNERALIAS
where INNERALIAS."EFFDT" <= SYSDATE
GROUP BY INNERALIAS."DEDCD",INNERALIAS."EMPLID"

and use this view in your report.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[lbass]

Versions 9 to XI do not work with alias tables referenced within the summary, so try this kind of setup instead:

(
select max("amount") //no table alias reference
from table A
where A."customer" = "table"."customer"
)

-LB