Print Page | Close Window

SQL Expression field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20412
Printed Date: 28 Apr 2024 at 8:51pm


Topic: SQL Expression field
Posted By: lolly54
Subject: SQL Expression field
Date Posted: 31 Jan 2014 at 5:12am
Hi All,

I cannot create a stored procedure on the DB and any DB changes would take up to three months.

I am trying to write a SQL in my report to return a field. I have never used SQL expression before and whatever I tried, it returned with error.

Not as simple as this work:

(
SELECT Case.Case_Owner
FROM Case
WHERE Case.Case_NUMBER = "12345"
)

I have done some searches for SQL expression sample but it didn't seem to help. Would you have a good document or sample I can learn from?

Many thanks and Happy CNY!



Replies:
Posted By: DBlank
Date Posted: 31 Jan 2014 at 5:22am
to my knowledge, SQL Expressions are basically an optimized formula as they try to push the execution of the formula onto the server rather than in crystal after the data has been pulled already.
Your example is really more akin to creating a data set for the report. This can be done using a Command object which is more like a stored procedure. The simplicity of the one you have entered would be just as easily handled by using the CASE able as yor data source and adding the record select formula of Case.Case_NUMBER = "12345"
 

Posted By: hilfy
Date Posted: 31 Jan 2014 at 5:38am
To add to what DBlank has said, a Command is just a SQL Select statement. However, when you use one you want to create it so that it pulls ALL of the data for your report. DO NOT link a command to tables or other commands! When you link like that, Crystal will pull all of the data into memory and do its linking/filtering there instead of pushing that processing to the database where it is handled much more efficiently. This will cause significant impact to the speed of the report and can cause problems if you're working with large data sets.

Also, when using a command do not use the Select Expert to filter your data - for the same reasons that you don't want to link the command to other tables/commands. Instead, put all of the filtering in the Where clause or Joins of the command. If you need to use parameters, they will need to be created in the Command Editor - there are extra "behind the scenes" required properties that are created when you do this. Because parameters created in the main report don't have these, they can't be used in commands. You'll then use Crystal syntax to use the parameters in the Where clause of the command. It will look something like this:

Where myTable.NumberField = {?NumberParam}
and myTable.DateField = {?DateParam}
and myTable.StringField = '{?StringParam}'
and myTable.MyField in {?MultiSelectParam}

Note the quotes around the string parameter - it won't work without them.
-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: kostya1122
Date Posted: 31 Jan 2014 at 9:37am
also use ' instead of "
(
SELECT Case.Case_Owner
FROM Case
WHERE Case.Case_NUMBER = '12345'
)

Posted By: lolly54
Date Posted: 02 Feb 2014 at 11:22pm
Thank you all! I have seen "add command" but never understand the use of it. It's good to learn something new about Crystal Reports. :)

Further question about not linking the command to any tables, does it mean just treat it as a standalone in the linking tables page?

Can I use more than one command in the same report?

Sorry for the many Qs, I am new to Command and am getting a bit excite about this "new" functionality... :)

Posted By: hilfy
Date Posted: 03 Feb 2014 at 4:07am
When you use a command, you should create it in such a way that you get ALL of the data you need for your report in a single command. Although it is possible to do so, don't use multiple commands or a command and tables unless you absolutely can't avoid it. If you have a command and tables/another command, your report WILL be slow because Crystal will load ALL of the data from both into memory and then join and filter the data there. Crystal does a lot of swapping to disk when this happens and if there are any problems, it doesn't clean up after itself very well.

Personally, the only time I'll use multiple commands is when I have one or more that I'm using to provide values for dynamic prompts only. When I do this, the extra commands are not linked to the main command and data from them is ONLY used in the dynamic prompts.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: lolly54
Date Posted: 04 Feb 2014 at 5:24am
Thanks for your explanation, hilfy!
Incident at work and I spent the whole day working on it. I will try the single command out tomorrow!
Many thanks! :)


Print Page | Close Window