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!

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

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... :)

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! :)