Null parameters and optimising SQL extraction

[theodorez]

Hi,

I'm using Crystal 8.5 and having all sorts of grief with null parameters.

I have a rather large database > 1 Million records and want to optimise the SQL extraction so that it only extracts records matching some filters.

I want crystal to create an SQL something like this

SELECT
SMDR."Date", SMDR."Country", field1, field2
FROM
"SMDR""
WHERE
SMDR."Date" >= {d '2005-11-04'} AND
SMDR."Date" <= {d '2005-11-05'} AND
SMDR."Country" = 'USA'

I have created parameter fields for DateFrom and DateTo and Country

This successfully and efficiently returns data for country USA,
however when I leave the country parameter to Blank or null the SQL comes back with

SELECT
SMDR."Date", SMDR."Country", field1, field2
FROM
"SMDR""
WHERE
SMDR."Date" >= {d '2005-11-04'} AND
SMDR."Date" <= {d '2005-11-05'} AND
SMDR."Country" = ''

and consequently no records.

So I modified the Record Selection Formula to look for nulls, blanks etc

{SMDR.Date} in {?DateFrom} to {?DateTo} and
if IsNull({?Country}) then
1=1
else
if {?Country} = " " then
1=1
else
if {?Country} = "" then
1=1
else
if {?Country} = "All" then
1=1
else
{SMDR.Country} = {?Country}

This generates the folowing SQL

SELECT
SMDR."Date", SMDR."Country", field1, field2
FROM
"SMDR""
WHERE
SMDR."Date" >= {d '2005-11-04'} AND
SMDR."Date" <= {d '2005-11-05'}

and displays the info I want, but as part of the selection process it reads ALL the records for the date range entered which slows the report down. Ideally, I would like it to ONLY select the records for the {?Country} entered. This is my problem.

I guess the problem is with the handling of null or " " parameters. Any ideas on optimising SQL extractions with null parameters?

Thanks and pardon the long message ....

Theodore

 

[nottoofemme]

First ensure you have the two settings of 'convert null values' checked in the report options area under the File menu item.

 

[nottoofemme]

Also, if you want the report to give you blank values regardless of your parameter, you would need to add that within your selection criteria like:

{SMDR.Country} = [{?Country},""]

 

[theodorez]

Thanks for the reply, however, my "convert null values" is greyed out because I have clicked "Perform grouping on the server". I am trying to make the extraction more efficient.

Ideally, if the user enters a Country parameter, the SQL should show

blah blah
WHERE
SMDR."Date" >= {d '2005-11-04'} AND
SMDR."Date" <= {d '2005-11-05'} AND
SMDR."Country" = {?Country parameter value entered eg "USA"}
blah blah

And extract records just for that country
OR if somebody doesn't enter a parameter, the SQL generated should be

blah blah
WHERE
SMDR."Date" >= {d '2005-11-04'} AND
SMDR."Date" <= {d '2005-11-05'}
blah blah

and extract records for all the countries.

This is what I am really after, any ideas? Anyone?
Is this possible?

Thanking in advance,

Theodore

 

[GJParker]

If you have an All option in your parameter then use

(If {?Country} <> "All" Then
{SMDR.Country} = {?Country}
Else
True)

If you simply want to leave the parameter empty then use

(If IsNull({?Country}) or {?Country} = "" Then
True
else
{SMDR.Country} = {?Country})

HTH


Gary Parker
MIS Data Analyst
Manchester, England

 

[theodorez]

Thank you, for the reply. I ended up using,

and
({SMDR.Country} = uppercase({?Country})
or trim({?Country}) = ""
)

That generated the result I was after.

Cheers,

T