Question Re: SQL Expressions generating errors on CRXI and Oracle DB in Technology Q&A

SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Wed, 15 May 2013 17:34:14 GMT

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!

Re: SQL Expressions generating errors on CRXI and Oracle DB

former_member11696 — Wed, 15 May 2013 18:09:07 GMT

Hi David,

OK so this is unclear, and I don't recommend using SELECT statements in SQLExpressions but others here say it should work... Anyways I would think it's the fully qualified names causing the problem since that is the error being generated by Oracle:

Try wrapping all of the references in double quotes, not just the field.

"INNERALIAS"."EFFDT"

It could be, assuming you are using ODBC, use Quoted Identifiers in the ODBC properties is not checked on, again assuming you are using a driver that supports it.

As a test copy the SQL and create a new report using a Command Object as your data source and see if that works...

Does that SQL work in SQLPlus?

Don

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Wed, 15 May 2013 18:47:18 GMT

Thanks for the quick reply! I've tried pretty much every combination I can think of - double quotes, single quotes, no quotes, quotes on field and not table, quotes on table and not field - and no joy. Also, the references that aren't in quotes in the example above are alias references. These work fine as they are in the second example, where I've substituted the table references that are causing the issue with discreet values.

The code above wouldn't work in a Command, because a command is not part of the larger report. Correct me if I'm wrong, but I should be able to reference the tables that are in the DB Expert already - right? If I add references in the FROM command to reference these tables, it works fine as a Command. It looks like the SQL expression isn't aware of the report it's a part of when the expression is saved, so references to the tables in the report aren't recognized. This DOES work in CR2011 against a SQL Server DB.

Here's another example:

(

select a.txgrs_ytd

from ps_tax_balance a

where a.emplid = "PS_DM_EMPL_HIST_VW"."EMPLID"

and a.company = "PS_DM_EMPL_HIST_VW"."COMPANY"

and a.tax_class = 'F'

and a.calendar_year = '2010'

and a.monthcd = (select max (monthcd)

from ps_tax_balance a2

where a2.emplid = a.emplid

and a2.tax_class = a.tax_class

and a2.calendar_year = a.calendar_year

and a2.company = a.company

)

This gives me the invalid identifier error on the reference to "PS_DM_EMPL_HIST_VW"."COMPANY". If I remove the two references to tables that are in the report and replace them with distinct values:

(

select a.txgrs_ytd

from ps_tax_balance a

where a.emplid = '00021'

and a.company = '01'

and a.tax_class = 'F'

and a.calendar_year = '2010'

and a.monthcd = (select max (monthcd)

from ps_tax_balance a2

where a2.emplid = a.emplid

and a2.tax_class = a.tax_class

and a2.calendar_year = a.calendar_year

and a2.company = a.company

)

...it works fine, and generates the value expected. The only thing that's causing an error is the reference to the table(s) already in the report. As I said above, it's as though Crystal isn't able to recognize the references to the tables that are in the DB Expert as it should.

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Wed, 15 May 2013 19:13:01 GMT

Another note - Here's another one I'm trying right now:

(

select max (EFFDT)

from PS_GENL_DEDUCTION

where PS_GENL_DEDUCTION.EMPLID = "PS_GENL_DEDUCTION_1".EMPLID

and PS_GENL_DEDUCTION."DEDCD" = "PS_GENL_DEDUCTION_1".DEDCD

and PS_GENL_DEDUCTION.EFFDT <= SYSDATE

)

I found this combination of quotes / no quotes on this page:

http://cogniza.com/wordpress/2005/11/07/crystal-reports-using-sql-expression-fields/

It says that quotes are only necessary around table names that are aliased in the main report. It fails the same way:

Error in compiling SQL Expression:

Failed to retrieve data from the database.

Details: ORA-00904:"PS_GENL_DEDUCTION_1"."DEDCD": invalid identifier

[Database Vendor Code: 904].

But I think it's interesting that the field name has quotes in the error message even though it doesn't in the code. This seems to make it even more likely that the issue lies somewhere in how Crystal is communicating the table/field references to the database.

Re: SQL Expressions generating errors on CRXI and Oracle DB

former_member11696 — Wed, 15 May 2013 20:07:50 GMT

Ah,,, so does it work in CR 2011 against Oracle 11.x? ( CR 2011 does not support Oracle 9, or it's never been tested )

R2 is end of life so we cannot escalate to the developers.

Also, as I indicated I don't recommended using SELECTS in SQLExpressions, it wasn't designed to do that. It's more to add a Function to the report. And even more so in older versions or CR and DB's.

Why not use it as a command for now and a subreport?

You could try turning on Oracle logging to see what the SQL is that CR is sending out. I could be we are generating something wrong server side, actaully from the error we are doing something. We just need to see what the Server is getting to compare syntax.

Odd part is why some work.... could possibly be a NULL value maybe?

MS SQL server is a lot more relaxed in syntax than Oracle is so it doesn't surprise me that it works in MS SQL.

If it doesn't work in CR 2011 and Oracle 11.x then we can escalate or even try to duplicate it. No one here even has R2 installed any more so no chance to debug it.

If it doesn't work in CR 2011 then you cna purchase a support case, you'll get a refund if it's shown to be a bug.

Don

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Wed, 15 May 2013 20:44:43 GMT

"Ah,,, so does it work in CR 2011 against Oracle 11.x?"

Don't know...the only version of Oracle I have available is 9.x. It'll be interesting in a couple months, though...we're going to start using CR2011 with Oracle 9.x.

We're currently working in an instance of CRXI that's hosted by our vendor, so I don't have the sort of access to drivers and ODBC that I'd like, but I'll ask them to check on the quoted identifiers property. I'll also see if they can check logs for us.

It wouldn't be a null issue, as I get the same error when I try to get the max of a date field by linking two instances of the same table.

As far as using subreports, I've done that, and it takes FOREVER to run. Literally hours to run a report that would otherwise take minutes. For reports that are being scheduled through BO that's ok, but not for ones that clients are running on their desktops. And we have done some of these reports using Commands, but doing so takes away many of the advantages of using Crystal, such as flexible parameters, using the GUI for report creation, etc. That unfortunately may turn out to be the only way to get it done, though.

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Fri, 21 Jun 2013 18:01:30 GMT

Just thought I'd check in again on this. I've seen many references to the issue online that involve newer versions of both Oracle and Crystal, so it would appear to be an ongoing issue.

I posted this on the Idea board today: https://ideas.sap.com/ct/ct_a_view_idea.bix?c=A5E8DEA8-D886-4250-BA2B-039F7D32FFC0&idea_id=%7B7731272C-1E72-456B-ACC8-300B1AED93C2%7D

I think I was able to distill this down to the actual issue here. When saving the SQL Expression, Crystal attempts to validate the expression. The problem is that when it does so, it's not taking the main report into account. So, a reference to a table in the main report looks like an invalid reference. Take this simple expression, for example:

(select max(hb.EFFDT)

from PS_HEALTH_BENEFIT hb

where hb.EMPLID = PS_DM_EMPL_CURR_VW.EMPLID)

This generates the error message:

ORA-00904: "PS_DM_EMPL_CURR_VW"."EMPLID": invalid identifier"

It should realize that the reference is to the table in the main report, but it doesn't. If I add a reference to that table to the FROM clause of the expression, the expression saves fine, but it's not going to do the job I need it to - it will be using a separate instance of the table from the one in the main report, and simply evaluates to the max date for the entire table.

Re: SQL Expressions generating errors on CRXI and Oracle DB

former_member11696 — Fri, 21 Jun 2013 20:53:05 GMT

Hi David,

Interesting find. So as I noted the version of CR you are using is end of life so we can't fix it. And because you are using SELECTs in a Expression, what happens if you don't and do some simple function?

I think you are correct, because Expressions are NOT Linked to main reports database CR can't handle this situation.

Work around is to sue a subreport rather than a SQLExpression. Not linking is not a problem now.

All I can suggest is to download CR 2011 Trial version and see if that works. If not we may be able to escalate it.

Don

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Fri, 21 Jun 2013 21:34:35 GMT

"...download CR 2011 Trial version..."

I wish I could. Unfortunately, I'm currently working in a Citrix environment on a vendor's server, so I'm at their mercy regarding what software they provide. I have seen others online complaining of the same issue in CR2011, though, so although I can't test it myself I believe it does exist.

As I said in earlier comments, I have tried the subreport alternative. Sometimes it works, but just takes exponentially longer to run; other times, the report fails for lack of resources. Unfortunately, that just isn't a reliable solution for me. Nor is grouping the report and selectively suppressing to show only the records with the latest date...this would require the download of literally millions of records when only a small fraction of those will actually be displayed in the report.

At this point, I'm reduced to writing manual SQL queries and running them as commands. As there's no real interface in Crystal for writing commands, though, this is also a far from ideal solution. The bottom line here is that the SQL Expressions are far and above the ideal method of achieving what I need...if there's any way to escalate this and have it looked at, that would be incredibly helpful!!

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Fri, 21 Jun 2013 21:37:46 GMT

Also...

"I think you are correct, because Expressions are NOT Linked to main reports database CR can't handle this situation."

The odd bit is that this DOES work when I'm using a SQL Server db.

Re: SQL Expressions generating errors on CRXI and Oracle DB

DellSC — Fri, 21 Jun 2013 22:07:46 GMT

Depending on how you're using the SQL Expression, you may not be able to use a subreport. In that case, you could write a Command to provide the data for your report. A Command is a SQL Select statement using the syntax of the database you're connected to. If you've created parameters in your main report to filter the data, you'll need to delete those parameters and re-create them in the Command Editor in order to filter the data there. A couple of best practices for using commands:

1. Do not link a command to any tables or other commands. Instead use a single query to return all of the data that's required in the report.

2. Do not filter the report data in the Select Expert. Instead, do the filtering in a Where clause in the Command.

Not following these rules will cause reports to be slow because Crystal will not be able to push the processing down to the server. Instead, it will pull all of the data into memory and link or filter the data there which can be very inefficient.

-Dell

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Fri, 21 Jun 2013 23:09:14 GMT

Yup...as I said above, the only option I've found is using Commands. Doing so is very tedious, though, as there's no interface for creating them in Crystal.

Re: SQL Expressions generating errors on CRXI and Oracle DB

JWiseman — Fri, 21 Jun 2013 23:43:45 GMT

hey David,

not sure if this helps at all, but what i do when i'm writing reports off of Commands, as i am by nature lazy, is create a report off of tables that at least brings in all of the tables that i need and then creating the joins in the linking expert. by adding a field from each table and also a filter the Database > Show SQL Query can get a good start on the command you have to write.

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Mon, 01 Jul 2013 18:53:16 GMT

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.

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Tue, 02 Jul 2013 04:47:05 GMT

Hi All

I have experienced similar issues before.

In your Select clause:

--SCHEMA.TABLE.COLUMN (Please do not use an alias)

SELECT SOME_OWNER.Suppliers.Supplier_Product_Name

In your from clause:

FROM Suppliers a -- Remove the Alias, Add Schema Reference

SOME_OWNER.Suppliers

In your JOINS:

JOIN Warehouse b

SOME_OWNER.Suppliers.Product_id = b.product_id

This worked for me.

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Thu, 04 Jul 2013 05:09:32 GMT

Hi Guys

With the Item in the Select clause--

It allows you to add in Schema.Table.Column Name (Tested today) -- Table Alias is still not allowed.

Another note --> If your subquery is returning more than one value, try using oracle function: wm_contact

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Thu, 04 Jul 2013 05:18:11 GMT

Sample I have used:

Schema: ORA_OWNER

TABLE: T1_PR_CTL

COLUMN QUERIED ON: VALUE

I forgot to mention in the above posts--Your sub-query in the SQL expression HAS to start with "(" and end with ")".

(select to_date(ORA_OWNER.T1_PR_CTL.VALUE,'dd/mm/yyyy')

FROM ORA_OWNER.T1_PR_CTL

WHERE ORA_OWNER.T1_PR_CTL.PR_NAME= 'DT_VALUE')

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Fri, 05 Jul 2013 16:59:12 GMT

Thanks for posting, but this doesn't address the actual problem I'm having. The problem is that I can't reference tables in the main report in the SQL expressions. Using an alias for the instances of tables in the expression actually does work. It's when I try to link to a table in the main report that it fails.

Re: SQL Expressions generating errors on CRXI and Oracle DB

Former Member — Fri, 05 Jul 2013 17:01:09 GMT

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.

Re: SQL Expressions generating errors on CRXI and Oracle DB

JWiseman — Sat, 06 Jul 2013 20:44:34 GMT

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?