Parameter Question

[Badlarry]

Here's the situation: I have a static list of values for a parameter. Included in this list is the word "NULL". When the user selects this option I want to return records with a null value in a certain field (WOPM12). Otherwise, simply return the records that match the value selected. I'm currently using the below statements in my record selection:

(if {?Pm-?Schedule Mode} = "NULL" then isnull({WORKORDER.WOPM2})
else {WORKORDER.WOPM2} = {?Pm-?Schedule Mode})

The problem is that this is running terribly slow. Can anyone think of a way to optimize this?

CR 8.5
Oracle

 

[rosemaryl]

Dear Badlarry,

Do a Database/Show SQL query. My assumption is that the else part is not passing.

I would rewrite your query:

(if {?Pm-?Schedule Mode} = "NULL" then isnull({WORKORDER.WOPM2})
else {WORKORDER.WOPM2} = {?Pm-?Schedule Mode})

as:

(
if {?Pm-?Schedule Mode} = "NULL" then isnull({WORKORDER.WOPM2})
else if {WORKORDER.WOPM2} <> "NULL" then
{WORKORDER.WOPM2} = {?Pm-?Schedule Mode}
)

The above should pass the SQL .. test by replacing your selection criteria and running the report passing NULL to the parameter or passing another value for the parameter.

Regards,
ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.