Using NULL as a valid parameter

[scb1776]

Hi,

I am using crystal reports version 11 connected to an oracle database.

I have several parameters that are used to build my sql.

The problem is when the user chooses NULL for the CC_DESCRIPTION parameter then the sql expert build the statement statement
"CMPL_CONTROL"."CC_DESCRIPTION"=''

For Oracle to return the correct rows it needs to be
"CMPL_CONTROL"."CC_DESCRIPTION" is null.

However if the user chooses a value other than NULL then the original query works fine.

Can anyone suggest how I can allow NULL to be a valid choice for this parameter?

I set the Convert Database NULL values to default to be true, however this did not fix the problem; I think that the problem exists before crystal reports gets the data.

The entire query (if you need it) is:
SELECT "CMPL_CONTROL"."CC_EFFECTIVE_DATE", ..., "CMPL_CONTROL"."CC_COMPANY_ID"
FROM ((("OPS$PASPROD"."CMPL_COMPO_REPORT_DET" "CMPL_COMPO_REPORT_DET" INNER JOIN "CMPL_REPORT_INSTANCE" "CMPL_REPORT_INSTANCE" ON "CMPL_COMPO_REPORT_DET"."CCRD_REPORT_INST_ID"="CMPL_REPORT_INSTANCE"."CRI_ID") INNER JOIN "OPS$PASPROD"."PARAMETER_SEQUENCE" "PARAMETER_SEQUENCE" ON "CMPL_REPORT_INSTANCE"."CRI_PS_ID"="PARAMETER_SEQUENCE"."PS_ID") INNER JOIN "OPS$PASPROD"."CMPL_CONTROL" "CMPL_CONTROL" ON "CMPL_REPORT_INSTANCE"."CRI_CONTROL_ID"="CMPL_CONTROL"."CC_ID") INNER JOIN "OPS$PASPROD"."CMPL_REPORTS" "CMPL_REPORTS" ON "PARAMETER_SEQUENCE"."PS_CRID"="CMPL_REPORTS"."CR_ID"
WHERE "CMPL_CONTROL"."CC_DESCRIPTION"='' AND ("CMPL_REPORTS"."CR_REPORT_NAME"='RATINGSMAX' OR "CMPL_REPORTS"."CR_REPORT_NAME"='RATINGSMIN')


Thanks for any suggestions

 

[synapsevampire]

If by "I have several parameters that are used to build my sql." you are trying to say that you use parmaeters in your record selection criteria, then post your record selection criteria.

To allow for a null or a space, something like this should work:

(
(
isnull({?MyParmaeter})
or
{?MyParmaeter} = ""
)
or
{table.field} = {?MyParmaeter}
)
Parameters are rarely null, so you generally code for them being blank, the above handles either.

-k


-k

 

[lbass]

What does the record selection formula look like? I'm wondering how you used the parameter there.

-LB

 

[scb1776]

The formula from the selection expert is

{CMPL_CONTROL.CC_DESCRIPTION} = {?Control Description} and
{CMPL_REPORTS.CR_REPORT_NAME} in ["RATINGSMAX", "RATINGSMIN"] and
CDate({CMPL_CONTROL.CC_EFFECTIVE_DATE}) = {?As Of Date} and
{COUNTERPARTIES.C_NAME} = {?Account } and
{VEHICLE_NAME.VEHICLENAME} = {?Vehicle Name}

The database allows the user to save a null value for CC_DESCRIPTION, so the use must be allowed to choose a blank in the parameter window.

The problem is that the query gets written using {CMPL_CONTROL.CC_DESCRIPTION} = '', however oracle won't accept this as a match to {CMPL_CONTROL.CC_DESCRIPTION} is null.


I don't know if there is any way to change the query to be written using the is null format if the user chooses a blank for the description.

(note that the database field is a varchar so oracle treats a blank string as a null when inserting)

 

[synapsevampire]

The database allows the user to save a null value for CC_DESCRIPTION, so the use must be allowed to choose a blank in the parameter window."

Null means that it isn't a blank, I'm not sure what the above is supposed to mean.

A shame you didn't just use what I supplied, you'd be on to the next issue by now.

-k

 

[scb1776]

I am not sure how or where to add the code that you suggested.

Do you mean to make that part of the formula in the select expert?

 

[lbass]

Go to report->selection formula->record and add it there, adjusting the existing formula as necessary.

-LB

 

[scb1776]

This solution seems to always give me the record with the value of NULL.

I only want the NULL record to be returned if that is what the user chose.

The value to be used for this particular parameter is selected from a choice box by the user.

If they choose the blank record then I need the query to use the form

{table.field} is null

else I need it to be

{table.field} = {?My Parameter}

{table.field} = '' does not work for reutning the null value in Oracle. Somewhere, when creating the select I need to be able to apply an if/else command to create the proper string.

 

[GJParker]

If what you're saying is that you want to allow the user to filter the report and return only records where the descriptioni snull then try something like

(
If {?Control Description} = '' Then
IsNull({CMPL_CONTROL.CC_DESCRIPTION})
Else
{CMPL_CONTROL.CC_DESCRIPTION} = {?Control Description}
)

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[scb1776]

Thanks for you help. I couldn't figure out how to use the if statement in the select expert functionality.

 

[GJParker]

You will need to type this directly in to the selection formula Report menu -> selection formulas -> Record

HTH

Gary Parker
MIS Data Analyst
Manchester, England