Help with the WHERE clause

[jmskarp]

Hello,

Crystal Reports v9
Oracle db ver 9i

I am creating a command object within a Crystal report and I need some help.

Here is my current query:

Select *
from
qm_document_sp qmsp, dm_document_sp dmsp
where
qmsp.R_DOCID = dmsp.R_OBJECT_ID
and
dmsp.R_OBJECT_TYPE in {?Doctypes}

I would like to change the where clause to select all
{?Doctypes} if the user enters ('all') into the parameter prompt or limit the {?Doctypes} to the list provided if they enter ('cgen', 'ccal'). Can someone let me know if it is possible to do an IF, THEN, ELSE within the WHERE clause, and if it is possible, an example of how I could do it.

Thanks in advance.

 

[dbomrrsm]

It may pay you to post this query in the crystal reports forum - I do the exact same thing with business objects by creating a dimension based on a select from the field union 'ALL' then build a query filter based on where x in ALL or ALL in ALL.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[jmskarp]

dbomrrsm,

Thanks for the feedback. I will look into submitting this to the Business Objects forum. I submitted here because I was asking more about the possibilities of the where clause within the statement.

Have a GREAT weekend!

 

[dbomrrsm]

when you construct a filter as I sadi above the where clause of the sql comes out looking like :

WHERE my_table.column1 IN ('XYZ')
OR ('ALL' IN ('XYZ'))

when a user picks from a list or:

WHERE my_table.column1 IN ('ALL')
OR ('ALL' IN ('ALL'))

if they select the ALL that appears in the list of values due to the union statement.

You have a good weekend too.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]