• RSS
  • Facebook
  • Twitter
  • Linkedin
Home > Error In > Error In Compiling Sql Expression

Error In Compiling Sql Expression

My only option is to close it without saving changes and reopen it - then it works fine again. Comments (0) Posted in Bugs and Errors,Method Subscribe RSS 2.0 feed. In my version I can't edit at least in the Show SQL Query window. May 15 '07 #1 Post Reply Share this Question 11 Replies Expert 2.5K+ P: 3,235 Motoma What do you mean, exactly, when you say "extract?" You can use CR to query More about the author

Unfortunately, that is far beyond the scope of both Crystal Reports, and pretty much my entire department. HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | JAVASCRIPT FORUM © 2004-2016 Brian Bischof. If I remove all alias references the problem goes away. Eg: Code: SPEC_NO MACH_NO MACH_UPTO_QTY 1123 235 999999 1123 421 999999 1123 901 999999 1123 650 999999 2657 235 999999 2657 552 999999 2657 126 999999 2657 650 999999 3728 235

I wrote the query SELECT name,address,age FROM t1 where age>20 ,but got the error Error in compiling SQL Expression Database Connector Error:'42000:[Microsoft][ODBC SQL Server Driver][SQL Server][Incorrect sybtax near the keyword 'select'.[Database Thank you. 0 LVL 34 Overall: Level 34 Crystal Reports 33 Query Syntax 6 Message Active today Expert Comment by:James06282008-11-24 You're welcome. I want to do this in CR. I know how to use Crystal report for reporting purpose but not for extraction.Since my company will not have any sql programmer in future they are planning to do this way.

What happens when you capture the item in SQL profiler? For more robust usage of SQL in CR 9 and later, use a command object. ==================== NOTES: Although SQL Expressions with SELECT statements are not supported, they can work if they Register now while it's still free! I'm hoping some solution can be reached within Crystal Reports XI itself.

I keep getting an error on the WHERE clause of the subquery, saying that the column prefix ‘MainQueryTable' does not match with a table name or alias name in the query. 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. All rights reserved. Get 1:1 Help Now Advertise Here Enjoyed your answer?

How do I get the id in the subquery to equal the reptext.req_id that is in the main query at the very bottom of the statement? I have one more question. Win 2k3 already has MDAC 2.8 I believe, though Microsoft makes a component checker that will tell you for sure, so I do not believe that is the issue. I am asking this question because there are no programmers here.

  1. Not sure if i'm typing it correctly or if CR uses different syntax.
  2. Well, at least it makes more sense now....
  3. but the sql command that i wrote is working under sql server.
  4. PandeySrivastav September 15, 2011 at 05:56 Re: Use SQLExpression field in Crystal Reports Hi Author, this is great article !!!!!!!!!
  5. Now i want a report that conatins only Name,Address and Age.
  6. Tell others by linking to it on your blog or website:Crystal Reports Forum IP Logged BrianBischof Admin Group Joined: 09 Nov 2006 Online Status: Offline Posts: 2460 Posted: 30
  7. Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'sqllims'. 0 LVL 100 Overall: Level 100 Crystal Reports 94 Query Syntax 8 Message Active today Expert Comment by:mlmcc2008-11-18
  8. For ex, I have to pull all the details of customers whose age is greater than 20.How can i do this in CR.
  9. Faq Reply With Quote September 9th, 2005,10:42 AM #3 No Profile Picture NetCog View Profile View Forum Posts  Contributing User Devshed Newbie (0 - 499 posts)  Join Date
  10. At that point the report will no longer run in v10 no matter what I do.

Contrary to what mlmcc said, in the SQL expression you Go to Solution 26 Comments LVL 29 Overall: Level 29 Query Syntax 4 Message Expert Comment by:Badotz2008-11-18 where "x"."req_id" = Now i want a report that conatins only Name,Address and Age. Leave a Reply Name Email (will not be published) Website Crystal Training at Your Location! So once i leave is it possible to do all extarctions using CR.

If you'd like some more details on the subreport idea, just holler. :-) James 0 Message Author Closing Comment by:cheyanne0402772008-11-23 Thanks for verifying what I thought, I will use a NOTE: If Crystal Reports recognizes the function name it will turn blue in the 'SQL Expression Editor' dialog box. Are you aComputer / IT professional?Join Tek-Tips Forums! Close Reply To This Thread Posting in the Tek-Tips forums is a member-only feature.

The validation only happens when the expression is being modified and saved, which is why the report will run when first opened. System Administrator February 7, 2006 at 10:41 You may want to try a simple SQL Expression (e.g. Featured Post How to improve team productivity Promoted by Quip, Inc Quip adds documents, spreadsheets, and tasklists to your Slack experience - Elevate ideas to Quip docs - Share Quip docs I see.

All Rights Reserved. At first I thought this error was coming from SQL. SPEC_ADD_ON SPEC_ADD_ON TESTAMT is my test database Faq Reply With Quote September 9th, 2005,02:05 PM #4 No Profile Picture NetCog View Profile View Forum Posts  Contributing User Devshed Newbie

The way we solved the problem was to change the connection to go directly to oracle instead of using an ODBC connection.

This expression works in all CR versions: ( SELECT Min ( ONDATE )
WHERE ACCOUNTNO = Contact1.AccountNo ) It is curious that the alias of the CAL Thnks a Lot Craig Buchanan February 11, 2007 at 21:08 No, you can't use a parameter in SQL Expression fields. Are you using ODBC or OLE DB? Privacy Policy Site Map Support Terms of Use Not endorsed by or affiliated with SAP Register| Login Want to sponsor BOB? (Opens a new window) Index|About Sponsors| Contact Us|

Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework. This type of request has been coming up frequently from users requesting reports and it would be great to be able to do so without creating a view every time. Usually, an item only has one path)) Each SPEC_NO is listed at least 4 times, sometimes more. Parameter fields may not be used in SQL Expressions.

While I still don't know how to get the SQL statement to work in the "SQL formula" section, I did find a little button that automatically sets it to show only Not the way you specified it. This is using Crystal Reports version 11, so maybe they have added "features" to prevent the use of any references to the main query. This field is the "final destination" of this segment of my overall record selection.

If you need to do more with the counts, the subreport can save the count in a shared variable and you can then use that variable in the main report. The problem is that CR appears to be checking the SQL expression by itself, without putting it into the context of the main query on the report.