Crystal Report SQL Statement Generation for MS SQL

Question

If you create a Crystal Report using the built in wizard, tables are linked, etc. What determines whether a parameter makes it into the actual SQL statement? Also, why does it enclose everything in double quotes?

For example:

Here would be my base SQL Statement as generated by CR:

SELECT "poitem"."fpono"
,      "pomast"."fcompany"
,      "pomast"."fvendno"
,      "poitem"."fpartno"
,      "poitem"."fitemno"
,      "poitem"."frelsno"
,      "poitem"."fordqty"
,      "poitem"."frcpqty"
,      "poitem"."fucostonly"
,      "poitem"."flstpdate"
FROM "M2MDATA01"."dbo"."pomast" "pomast"
     INNER JOIN "M2MDATA01"."dbo"."poitem" "poitem"
     ON "pomast"."fpono" = "poitem"."fpono"

Now, watch what happens when I add the following into the select expert editor:

{poitem.fcategory} = "INV" and
not ({poitem.fmultirls} = "Y" and
{poitem.frelsno} = "  0") and
{poitem.fordqty} > {poitem.frcpqty} and
cdate({poitem.flstpdate}) = {?LastPromDate} and
{poitem.forgpdate} = DateTime (2010, 10, 05, 00, 00, 00) and 
{pomast.fstatus} = "OPEN"

I added the forgpdate for comparison since I'm asking for dates two different ways.

Here is the SQL I get:

 SELECT "poitem"."fpono"
,      "pomast"."fcompany"
,      "pomast"."fvendno"
,      "poitem"."fpartno"
,      "poitem"."fitemno"
,      "poitem"."frelsno"
,      "poitem"."fordqty"
,      "poitem"."frcpqty"
,      "poitem"."fucostonly"
,      "poitem"."flstpdate"
,      "poitem"."fcategory"
,      "poitem"."fmultirls"
,      "pomast"."fstatus"
,      "poitem"."forgpdate"
FROM "M2MDATA01"."dbo"."pomast" "pomast"
     INNER JOIN "M2MDATA01"."dbo"."poitem" "poitem"
     ON "pomast"."fpono" = "poitem"."fpono"
WHERE     "poitem"."fcategory" = 'INV'
      AND("poitem"."fmultirls" <> 'Y'
          OR "poitem"."frelsno" <> '  0')
      AND "poitem"."fordqty" > "poitem"."frcpqty"
      AND("poitem"."forgpdate" >= {TS '2010-10-05 00:00:00'}
          AND "poitem"."forgpdate" < {TS '2010-10-05 00:00:01'})
      AND "pomast"."fstatus" = 'OPEN'

Most of it is transferred directly, however it converted my "Not" statement.

Also, notice how my cdate line was not sent at all, but is resolved within Crystal itself.

Does anyone know exactly how Crystal decides what to do? I work with some large databases and need my criteria in the SQL statement otherwise Crystal returns huge datasets which waste time, memory, and processing.

TIA.

Answer 1

why does it enclose everything in double quotes?

Double quotes are the ansi standard way to enclose object names. It's the safe, cross-platform way to make sure your company's table and column names won't conflict with a reserved keyword somewhere.

The main part of question seems to be why it does your variable comparision client side. There I'm afraid I can't help you. My best guess is that it's something to do with what indexes are available, except it'd be odd for Crystal to know too much about that.

Answer 2

The format of the SQL it generates is partly dependent on the version of Crystal and the data provider. If you use ODBC, it will always put quotes around everything as this syntax is the ansi standard. The same will generally apply when reporting against SQL Server and other data sources. However, if for example you create a report in Crystal 8.5 and use the SQL Server datasource provider, it will generate SQL without the quotation marks (ie just a plain SELECT MyTable.MyField FROM MyTable). I believe this behaviour was changed in version 9 for any SQL Server datasource, but it is not an across the board change. In Crystal 11, using the native provider rather than ODBC for an Informix datasource will not generate the quoted SQL as above.

As for how it evaluates what to do in the SQL, that is dependent on how much it can rewrite in the data source's SQL. Any formula using a Crystal function (such as the CDate one you've used) can usually only ever be evaluated within Crystal itself. To maximize the work the server does, don't use any Crystal functions, summary fields or Switch statements. The exact amount it can convert again depends on the provider. Sometimes it's simply not possible to convert it all. There's also an option to Use Indexes On Server For Speed which can make things quicker, but doesn't really help much with what gets executed where.

Answer 3

You will have more control over the SQL statement if you use a Command object, rather than individual tables. Database | Database Expert..., expand the desired database server's root nood, then click 'Add Command'. Enter desired SQL statement. Add parameters as required.

The parameters that are created in the Command object are much less flexible than the ones that you define in the report itself. You can choose to have parameters defined in the Command object (which will be automatically added to the report) and additional parameters defined in the report, which will be applied as the report reads record from the database (obviously less efficient).