Error message when saving SQL Expression

[Davest11]

I'm very experienced with Crystal (10+ years), but I've never made use of SQL Expressions before, and I'm running into issues. I'm using Crystal XI (11.5.10.1263) with an Oracle database.

The strange part is that some work and some do not, and I'm unable to figure out any rhyme or reason as to why. Not sure if things like full report listings will be helpful here, so I'll post them if asked.

Here's an example of one that works in one report:

(select max("PS_LOCATION_TBL"."EFFDT")
from "PS_LOCATION_TBL"
where "PS_LOCATION_TBL"."LOCATION" = "PS_DM_EMPL_CURR_VW"."LOCATION")

And here's an example of one that does not in a different report:

(select max (INNERALIAS."EFFDT")
from "PS_DM_EMPL_HIST_VW" INNERALIAS
where INNERALIAS."EFFDT" < "PS_DM_EMPL_HIST_VW"."EFFDT" and
INNERALIAS."EMPLID" = "PS_DM_EMPL_HIST_VW"."EMPLID")

The error I get is:

Error in compiling SQL Expression:
Failed to retrieve data from the database.
Details: ORA-00904:"PS_DM_EMPL_HIST_VW"."EMPLID": invalid identifier
[Database Vendor Code: 904].

I know you might not have enough information to come up with an answer here, but I'm not sure what other details will be helpful; I'm glad to provide more information as needed.

Thanks!

 

[Davest11]

To be clear - that error message is coming up when I try to save the SQL Expression. I've also tested the SQL code itself in another SQL tool, and it works fine.

 

[PeterDimitrov]

Do you have table PS_DM_EMPL_HIST_VW in the report ?

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[Davest11]

Yes...it's actually the only table in the report.

 

[Davest11]

What I'm looking to do there (if it's not obvious) is find the previous record by date, so that I can select based on the value of a field in that previous record - I need to find all records coded with a particular value, where the previous record was coded with a different value. I could do it using a subreport, but it would be horribly slow. From what I can see, this *should* work.

 

[PeterDimitrov]

I believe is should work too. Crystal might be confused by the use of PS_DM_EMPL_HIST_VW with alias. BTW this approach will be slow too because the SQLExpresion will be included as a subquery and will be executed for each row of the recordset used inside the report. This will work like a cursor. Did you consider to use a view instead of PS_DM_EMPL_HIST_VW table ? View similar to this one should return everything from PS_DM_EMPL_HIST_VW + the previous EFFDT for each record

SELECT data.*,maxPrevValues.MaxEFFDT
FROM PS_DM_EMPL_HIST_VW data
INNER JOIN
(
SELECT a1.EFFDT,MAX(a2.EFFDT) as MaxEFFDT
FROM PS_DM_EMPL_HIST_VW a1
INNER JOIN PS_DM_EMPL_HIST_VW a2 on a2.EFFDT<a1.EFFDT AND a1.EMPLID = a2.EMPLID
GROUP BY a1.EFFDT
) maxPrevValues on data.EFFDT=maxPrevValues.EFFDT

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[Davest11]

I actually just got it going a few seconds ago. Seems that Crystal requires that all tables referenced in SQL Expressions (at least in my application) must be referenced in the Database Expert. I added another instance of the PS_DM_EMPL_HIST_VW table (PS_DM_EMPL_HIST_VW_1), and used the following SQL code:

(select max ("PS_DM_EMPL_HIST_VW"."EFFDT")
from "PS_DM_EMPL_HIST_VW"
where "PS_DM_EMPL_HIST_VW"."EFFDT" < "PS_DM_EMPL_HIST_VW_1"."EFFDT" and
"PS_DM_EMPL_HIST_VW"."EMPLID" = "PS_DM_EMPL_HIST_VW_1"."EMPLID")

Works like a charm, and very speedy. Thanks for your help, though...always good to bounce things off someone!

 

[Davest11]

I actually just got it going a few seconds ago. Seems that Crystal requires that all tables referenced in SQL Expressions (at least in my application) must be referenced in the Database Expert. I added another instance of the PS_DM_EMPL_HIST_VW table (PS_DM_EMPL_HIST_VW_1), and used the following SQL code:

(select max ("PS_DM_EMPL_HIST_VW"."EFFDT")
from "PS_DM_EMPL_HIST_VW"
where "PS_DM_EMPL_HIST_VW"."EFFDT" < "PS_DM_EMPL_HIST_VW_1"."EFFDT" and
"PS_DM_EMPL_HIST_VW"."EMPLID" = "PS_DM_EMPL_HIST_VW_1"."EMPLID")

Works like a charm, and very speedy. Thanks for your help, though...always good to bounce things off someone!