Optional Ignore Parameter in a COMMAND 2

[MJRBIM]

CR-XI (R2)
ORACLE 10g Database
======================

The report is written by someone else using a COMMAND to provide so total counts from an ORACLE table.

Now we want to add OPTIONAL parameters for REGION, STATE and CITY.

I can get the STRING parameters to work if I provide an explicit value at all three (3) levels in the COMMAND -

Example -

SELECT nvl(avg(STORE.CASH),0) tot
FROM STORE
WHERE
LOCATION ='{?REGION}'AND
STATE = '{?SUB-REGION}'AND
CITY = '{?CITY}'

...but I want to be able to IGNORE the parameter within the COMMAND if an * value (or something similar) is passed.

I know I could do this by rebuilding all of the COMMAND logic within a Stored Procedure or directly within the RPT-file, but this report has 30+ COMMANDs and I am trying to avoid that.

Anyone know if there is a way to do this within the Oracle COMMAND in CR-XI (R2)...?

Thanks in advance!

Senior Business Intelligence Consultant
Toronto, Canada

SeagateInfo 7 -> Crystal Enterprise -> BOE-XI (R2) / (R3.1) | Crystal ( 7 -> 2008) design | WebI design | Best practices for Java / .Net / Web Services

 

[Turkbear]

Hi,
If you pass Oracle's wild card symbol '%' ( instead of '*') and change the = to a LIKE operator, it will work regardless of whether a value is passed thru the parameter.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[MJRBIM]

Thanks! That worked!

Senior Business Intelligence Consultant
Toronto, Canada

SeagateInfo 7 -> Crystal Enterprise -> BOE-XI (R2) / (R3.1) | Crystal ( 7 -> 2008) design | WebI design | Best practices for Java / .Net / Web Services

 

[Turkbear]

Hi,
Glad to help....





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[bdreed35]

Becareful when using like as I think it has to do a table scan.
An alternate option is to code your where clause like this:

SELECT nvl(avg(STORE.CASH),0) tot
FROM STORE
WHERE
('{?REGION}' = '*' OR LOCATION = '{?REGION}') AND
('{?SUB-REGION}' = '*' OR STATE = '{?SUB-REGION}') AND
('{?CITY}' = '*' OR CITY = '{?CITY}')

~Brian

 

[MJRBIM]

Brian - Thanks for the tip.

Performance seems OK for now - but we will keep this in mind.

Senior Business Intelligence Consultant
Toronto, Canada

SeagateInfo 7 -> Crystal Enterprise -> BOE-XI (R2) / (R3.1) | Crystal ( 7 -> 2008) design | WebI design | Best practices for Java / .Net / Web Services

 

[Turkbear]

Hi,
Easy enough to check - have the dba or you check the execution plan of both queries in Oracle - But the '*' character cannot be sent to Oracle, it is not the wildcard char.





To Paraphrase:"The Help you get is proportional to the Help you give.."