sql command parameter 2

[bmpsu]

Crystal Report XI
Oracle 9i

I am using a SQL command to create my report. Here is a short/partial example.

SELECT
a.license_id, a.license_no, a.license_status
FROM 
license a
WHERE 
a.license_status = {?Status}

I have my parameter in my command ({?Status}). I want to give the user the option to select a status of 1 or 2. 1 and 2 are values in that database column. I want to also allow the user to select both.

What I have tried;
I have a parameter ({?Status}) that has three different values (0,1,2). Then I use a selection formula:

(If {?Status} = 0 Then
({Command.LICENSE_STATUS} = 1 OR {Command.LICENSE_STATUS} = 2))

I know my selection criterion is where I am going wrong. How would I accomplish this or is it possible to have a parameter work this way? I tried my parameter with a value field and without. Not too sure.

 

[dk87]

Not running Crystal on this machine but if memory serves...

If {?Status} = 0 then
({Command.LICENSE_STATUS} = 1 OR {Command.LICENSE_STATUS} = 2) else {Command.LICENSE_STATUS} = {?Status}

Also, use the "View SQL Query" feature and post that along with your selection criteria.

 

[bmpsu]

I tried using the selection criteria you posted above. When I select 0, I still do not get any results.

I am not sure where to find "View SQL Query". I am able to find "Database / Show SQL Query". That just shows me the query that is posted in my first post above.

I can get the report to work if I use the query above without the 'WHERE' clause; and them just create a parameter in crystal and use record selection to only display the results based off of status.

Record Selection Formula:

{?Status} = 0 OR {Command.LICENSE_STATUS}={?Status}

The reason I wanted to put the parameter in my SQL is to shorten the time the query takes to execute. With the parameter in the SQL Command, the query will pull only needed records. With the parameter in Crystal, the query will pull all records then use the record selection formula above to display the records. I am not an expert by any means, but I believe that is correct.

Thoughts?

 

[Turkbear]

Hi,
The parameter in the SQL command is used in the Sql statement only..it cannot be applied in the Report as well...

Change the SQL statement to handle the If..Then..logic( using a DECODE or something similar( depends on the database you are accessing).







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

 

[lbass]

In this case (one or all), I think you could use:

SELECT
a.license_id, a.license_no, a.license_status
FROM
license a
WHERE {?Status} = 0 or
a.license_status = {?Status}

-LB

 

[bmpsu]

I just got back to this report.

LB -
I wasn't able to get your example to work. This table has status values other than 1 or 2. Using your example gave me all rows. I left out that information from before and should have been more clear.

Turkbear -
I am using Crystal XI; Oracle 9i.
I played around with your example. From my testing so far, I believe this to work. Though I will continue to test for that odd circumstance. What do you think of what I have so far.

SELECT 
a.license_id, 
a.license_status,
a.license_no,
decode(a.license_status, 1, 'Active', 2, 'Pending') Status,
decode(a.license_status, 1, 'Both', 2, 'Both') Both
FROM
license a
WHERE
(decode(a.license_status, 1, 'Active', 2, 'Pending') = {?Status} 
OR 
decode(a.license_status, 1, 'Both', 2, 'Both') = {?Status})

Before I was just using the numeric value for my examples. In this example I was thinking that I could have my parameter {?Status} values as Active, Pending, and Both. The user could only select one of course.

Does anyone see something I can't see that would cause this to fail.

Thanks

 

[synapsevampire]

Looks good to me.

The only you might want to do is create a Stored Procedure out of it to lend itself to easier maintenance and reusability by other processes.

-k

 

[lbass]

Then you could change the command to:

SELECT
a.license_id, a.license_no, a.license_status
FROM
license a
WHERE
a.license_status in (1,2) and
(
{?Status} = 0 or
a.license_status = {?Status}
)

-LB

 

[bmpsu]

k -
That is something I should look into. Thanks for the tip.

LB -
I like what you wrote. Simple and neat.

Thanks to all for the help.