Error In Compiling Sql Expression
bug affects only some reports Saturday 26 January 2013 @ 12:14 am So here is an odd bug I found. It happens every time in Crystal versions 9, 10 and 11(XI) but also happens in some reports in newer versions. The bug only affects SQL Expressions that contain a correlated sub-query. For example this expression works in both CRv8.5 and CRv12 (2008) but fails in all of the versions in between: (SELECT Min (CAL.ONDATE)
FROM GM6.dbo.CAL CAL
WHERE CAL.ACCOUNTNO = Contact1.AccountNo) The expression is designed to show the first appointment in the Cal table for each contact in the report. Using a SQL Expression avoids duplication I would normally get by adding the Cal table to the report directly. Run this expression in CR versions 9, 10 or 11 and you get this error: Error in compiling SQL Expression. The multi-part identifier Contact1.AccountNo could not be bound. At first I thought this error was coming from SQL. But as a test I took a v12 report that had this expression and opened it in v10. The report ran fine. But if I then try to edit the SQL expression or even check it, I get the error above. At that point the report will no longer run in v10 no matter what I do. My only option is to close it without saving changes and reopen it - then it works fine again. This tells me that the SQL is fine because it isn't the DB that rejects it. The error only occurs when CR tries to validate the expression. The validation only happens when the expression is being modified and saved, which is why the report will run when first opened. But once the validation has failed the report can't be refreshed, so the SQL expression must be marked as bad until it can pass the validation. A few more experiments showed that this error is somehow related to using aliases. If I remove all alias references the problem goes away. This expression works in all CR versions: ( SELECT Min ( ONDATE )
FROM GM6.dbo.CAL
WHERE ACCOUNTNO = Contact1.AccountNo ) It is curious that the alias of the CAL table causes the field from the Contact1 table to error out. But here i
Analytics Conference: Oct 18, Mastering SAP BI, Melbourne: Oct 23. Error in Compiling SQL Expression Search this topic...|Search Crystal Reports...|Search Box Select a search Explain These Choices... --------------------Recent Topics (All Forums) Unanswered Posts Register or Login to Post Forum Index -> Building Reports -> Crystal Reports Author Message KimjoForum MemberJoined: 01 Jun 2007Posts: 5 Posted: Wed Jun 27, 2007 1:06 pmPost subject: Error in Compiling SQL Expression I am trying to write my first SQL Expression. Because it was not working I created a very simple http://kenhamady.com/cru/archives/2430 query just to see if I could get an expression to work and I keep getting this error: Database Connector Error: 'HY000:[Oracle][ODBC][ORA]ORA-00936:missing expression [Database Vendor Code:936] Here is the expression: select verfn_data.`dtl_seq_nbr` from `verfn_data` I can run my report with formulas without any problem I can build this query in PLSQL and it works fine Back to top ghostmanSenior MemberJoined: 12 Jan 2006Posts: 83Location: http://www.forumtopics.com/busobj/viewtopic.php?t=87581&sid=84ceeec61aa87d002dd39f51ce2519a4 Switzerland Posted: Tue May 06, 2008 2:02 amPost subject: Re: Error in Compiling SQL Expression It has something to do with the connection type. Are you using ODBC or OLE DB? I have this problem and I am trying to solve it. Did you ever resolve this? Back to top KimjoForum MemberJoined: 01 Jun 2007Posts: 5 Posted: Tue May 06, 2008 8:25 amPost subject: Re: Error in Compiling SQL Expression I was using a ODBC connection. During this time, we had upgraded to oracle 10. The way we solved the problem was to change the connection to go directly to oracle instead of using an ODBC connection. Not sure that is the correct answer, but it was the only thing that we could make work. Good luck. Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 YearOldest FirstNewest First Register or Login to Post Forum Index -> Building Reports -> Crystal Reports Page 1 of 1 All times are GMT - 5 Hours Select a search Explain These Choices... --------------------Recent Topics (All Forums) Unanswered Posts Jump to:Select a forumBusinessObjects Software------------------------------------General
Newbie Joined: 22 Mar 2007 Location: United States Online Status: Offline Posts: 30 Topic: Error in SQL ConnectionPosted: 30 Aug 2007 at 3:49pm Hi! guys, Need help.. I connected ODBC so that i could write sql commands, but i get this error... any suggestion on how i could make this http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=1257 go away? Error in compiling SQL Expression:Database Connector Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax http://www.cogniza.com/wordpress/2005/11/07/crystal-reports-using-sql-expression-fields/ near the keyword 'select'.[Database Vendor Code:156]'. IP Logged BrianBischof Admin Group Joined: 09 Nov 2006 Online Status: Offline Posts: 2460 Posted: 30 Aug 2007 at 4:53pm This is a pretty generic error message. Sounds like a typical SQL syntax error. I would copy the SQL into the Query Analyzer to get a better error message. Edited by BrianBischof - 30 Aug 2007 at 4:54pm error in Please support the forum! Tell others by linking to it on your blog or website:Crystal Reports Forum IP Logged jjcyeshua Newbie Joined: 22 Mar 2007 Location: United States Online Status: Offline Posts: 30 Posted: 30 Aug 2007 at 4:59pm Not sure what you mean run it using query analyzer. but the sql command that i wrote is working under sql server. It was just a simply select * , to make sure at its working. not entirely sure if error in compiling this helps or even answer your comment... SOrry IP Logged BrianBischof Admin Group Joined: 09 Nov 2006 Online Status: Offline Posts: 2460 Posted: 30 Aug 2007 at 5:58pm Well, that is what I meant. The error message is too generic to find out what is wrong. I thought maybe you had a SQL syntax error. Are you sure that CR has permission to access all the necessary tables? Please support the forum! Tell others by linking to it on your blog or website:Crystal Reports Forum IP Logged jjcyeshua Newbie Joined: 22 Mar 2007 Location: United States Online Status: Offline Posts: 30 Posted: 30 Aug 2007 at 6:02pm Yes, it can access all the tables. Whenever i drag and drop any tables into the report itself, it works fine. The tables shows as well as the data. Not sure if i'm typing it correctly or if CR uses different syntax. Any ideas why? IP Logged BrianBischof Admin Group Joined: 09 Nov 2006 Online Status: Offline Posts: 2460 Posted: 30 Aug 2007 at 6:13pm Crystal will use a different syntax for its own queries (use the View SQL Query option to see), but the SQL you enter is passed to the server untouched. Also, I would create a new report with a very simple query in the Command object. Just "SELECT * FROM table" and see if it takes it. If so, add on incrementally from there to see what
The contents of a SQL Expression field is any valid SQL statement that returns a scalar value. The resulting SQL is added to the report's SELECT clause. For example:
SELECT PAT_NAME,HOSP_ADMSN_TIME,HOSP_DISCH_TIME,
--SQL Expression
(
SELECT MAX(HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP2
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP.PAT_ENC_CSN_ID
)
--/SQL Expression
FROM PAT_ENC_HSP
Limitations SQL Expression fields may only return a scalar value (one column and one row). Parameter fields may not be used in SQL Expressions. Features A database [Oracle|SQL Server|Sybase] scalar function may be used in a SQL Expression field. Case statements may be used in SQL Expression fields. For example (Oracle syntax):
(
CASE
WHEN PAT_ENC_HSP.HOSP_DISCH_TIME IS NULL THEN
'F'
WHEN PAT_ENC_HSP.HOSP_DISCH_TIME >= PAT_ENC_HSP.HOSP_ADMSN_TIME-7 THEN
'T'
ELSE
'F'
END
)
Syntax The SQL statement needs to be enclosed in parenthesis:
(
SELECT MAX(HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP2
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP.PAT_ENC_CSN_ID
)
Double-quotation marks aren't required if tables in the main query aren't aliased:
(
--will compile
SELECT MAX(HOSP_DISCH_TIME)
FROM PAT_ENC_HSP PAT_ENC_HSP2
WHERE PAT_ENC_HSP2.PAT_ID=PAT_ENC_HSP.PAT_ID
AND PAT_ENC_HSP2.PAT_ENC_CSN_ID < PAT_ENC_HSP.PAT_ENC_CSN_ID
) ( -will compile SELECT MAX("HOSP_DISCH_TIME") FROM "PAT_ENC_HSP" "PAT_ENC_HSP2″ WHERE "PAT_ENC_HSP2″."PAT_ID"="PAT_ENC_HSP"."PAT_ID" AND "PAT_ENC_HSP2″."PAT_ENC_CSN_ID" < "PAT_ENC_HSP"."PAT_ENC_CSN_ID" ) Double-quotation marks are required if tables in the main query are aliased:
(
--will compile
SE