Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: SQL Expression field Post Reply Post New Topic
Author Message
lolly54
Groupie
Groupie
Avatar

Joined: 25 Sep 2011
Online Status: Offline
Posts: 58
Quote lolly54 Replybullet Topic: SQL Expression field
    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!

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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"
 


Edited by DBlank - 31 Jan 2014 at 5:23am
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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

Edited by hilfy - 31 Jan 2014 at 5:39am
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 31 Jan 2014 at 9:37am
also use ' instead of "
(
SELECT Case.Case_Owner
FROM Case
WHERE Case.Case_NUMBER = '12345'
)
IP IP Logged
lolly54
Groupie
Groupie
Avatar

Joined: 25 Sep 2011
Online Status: Offline
Posts: 58
Quote lolly54 Replybullet 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... :)
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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
IP IP Logged
lolly54
Groupie
Groupie
Avatar

Joined: 25 Sep 2011
Online Status: Offline
Posts: 58
Quote lolly54 Replybullet 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! :)
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.030 seconds.