Error in SQL Expression

[maas]

Hello All,

I am having a sql expression which is already been created in an old report and it is working fine. When I have copied it to a new report and has the same tables and exact relations, it is giving an error: ORA-00942 table or view does not exist.

Here is the SQl Expression:

(select MAX(intrates.RATE_DT)
FROM INTRATES,SECTYPE,DEALS,SECISSUE
WHERE sectype.thekey = "posnrpt"."SECTYPE"
AND SECISSUE.THEKEY = SECTYPE.SECISSUE_ID
AND LTRIM(RTRIM(UPPER(intrates.ISSUER_DETAIL_ID))) =LTRIM(RTRIM(UPPER('ID'||secissue.ISIN)))
AND INTRATES.RATE_DT <= "posnrpt"."POSN_DT"
)

Do I have to do further modification to it? Also, I can't perform any simple query in the SQL expression becuase it is giving the same error.

 

[lbass]

Try removing the table name within the summary:

(
select MAX(RATE_DT)
from...

In versions 9 to XI, SQL expressions fail when a table is referenced within the summary. This was fixed in CR 2008, I believe.

-LB

 

[maas]

I tried, but it did not work for me.

The wondering thing is that this SQL expression is working in a previous report, but now it is not working?

 

[lbass]

I see you have referenced a table in the expression that is not in the From clause--"posnrpt". Make sure this table is in your main report.

Also, please identify your Crystal Reports version. The particular driver can also affect the use of SQL expressions.

-LB

 

[maas]

I am using Crystal Report XI and the driver which I am using is OLE DB ADO for Oracle.

In one of the reports which are currently avaialbe, this expression is working, But I have just coped the same to the new report, but it is not working for me.

Also, All of the tables are there.

 

[lbass]

Is there a field from each table used in the main report. You might also want to go to database->set location and make sure you have only one driver in play. I don't have any other suggestions.

-LB

 

[maas]

There is only one driver.

I will try changing the driver to oracle driver and check what will happen.

Thanks for the help