Of course, if you need to select multiple rows or multiple columns, you will need a subreport. " (This lecture is from Ken Hamady, www.kenhamady.com) Is it suiteable to your problem? Very simple! 2. So i connected to the database and inside SQL expressions Field i wrote the following query, SELECT name,address,age FROM t1 Is this correct. For example:
FROM IP_FLWSHT_MEAS IP_FLWSHT_MEAS_SP,
WHERE "IP_FLWSHT_MEAS_SP"."FLO_MEAS_ID" in ('11')
AND "IP_FLWSHT_MEAS_SP"."FSD_ID" = "IP_FLWSHT_REC_SP"."FSD_ID"
AND "IP_FLWSHT_REC_SP"."INPATIENT_DATA_ID" = "PAT_ENC_HSP"."INPATIENT_DATA_ID"
his comment is here
So once i leave is it possible to do all extarctions using CR. http://msdn.microsoft.com/en-us/library/ms226240%28v=vs.90%29.aspx http://mindstick.com/Articles/eb554c35-0236-49ca-a7ce-c2eb573e27e8/?SqlExpression%20Field%20in%20Crystal%20Report Jeff W July 24, 2013 at 14:53 8 years later and this article is still helping people. No. Clearly the comment doesn't make a difference to the database engine, but it must have some effect on the CR validation. https://archive.sap.com/discussions/thread/1822727
Join our community for more solutions or to ask questions. Login. Not just this SELECT COUNT, but a lot of SQL in my report.Where is the problem in this case?Can we give me some example code that works? Any ideas why?
I accept that is the solution. Features A database [Oracle|SQL Server|Sybase] scalar function may be used in a SQL Expression field. The bug only affects SQL Expressions that contain a correlated sub-query. I may be missing something, but I can't see a way to tie the SQL expression to a specific value in the main query.
What you see as available may not be what I see..So, while SAP can support the feature as a whole, they can't necessarily provide syntax support, nor guarantee effectiveness. Should someone know little about SQL to query the database in CR . Promoted by Recorded Future Enhance your security with threat intelligence from the web. http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=1257 If the comment includes the name of the field mentioned in the error message, then that somehow makes it work, even with aliases.
Post navigation ← Considerations For Reporting Against Remedy ARS Based Applications Crystal Reports: ShellSort() Function → 23 thoughts on “Crystal Reports: Using SQL Expression Fields” amelia December 29, 2005 at 11:37 Say for example the table t1 contains the following fileds. SQL Analyzer doesn't know about the other query or the fields in it. If I delete that word or misspell it, I get the original error.
from tablename where age>20;) was correct. http://kenhamady.com/cru/archives/2430 If I was going to rewrite the entire database, I would add a "TotalPayment" field to MainQueryTable1, and maintain it using triggers for whenever payments are added or removed from MainQueryTable2. like SELECT Count(*) FROM table_name), to ensure that functionality works as expected. The exact tables are there.
Please support the forum! this content In turn, this is needed because the report has to return only specific payments in each account meeting certain other criteria, but also exclude all payments from any account whose total This has better chance of supporting it. First Name Please enter a first name Last Name Please enter a last name Email We will never share this with anyone.
Join Now For immediate help use Live now! Are you sure that CR has permission to access all the necessary tables? I connected ODBC so that i could write sql commands, but i get this error... weblink The main limitation is that it can only return a single value.
You will be able to use parameters and the scalar query in the SELECT clause. You can see my SQL expression in the subquery in the middle. In the past I would have recommended doing this via subreport.
One alternative is to use a subreport to get the counts. 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 Another way to fix it is to put a SQL comment line at the beginning of the expression. Try: ( SELECT SUM(SALE_COST) FROM TEST_TBL WHERE (EA_SUBTYPE = ‘Hours') OR (EA_TYPE = ‘Labor' AND EA_SUBTYPE = ‘ ‘) ) Qry August 8, 2006 at 21:25 cool!
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 If you look at the SQL statement below, I can run this just fine in SQL Query Analyzer. 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 http://comscity.net/crystal-reports/crystal-reports-error-507.html Run this expression in CR versions 9, 10 or 11 and you get this error: Error in compiling SQL Expression.
Ken December 30, 2005 at 14:06 Could not have figured this out by myself. Covered by US Patent.