issue with 'ALL' in sql command 1

[ub1234567]

Hi,

i am using sql command.
i have problem with how to handle 'ALL' in sql command parameter.
if in drop down list user select 'ALL' then there is no filtering on that parameter.
if user select perticular one, then there is filtering of that database filed value in command parameter.

i want to handle this thing only in sql command not in record selection.

pls provide me suggestion.
any help is appriciated.
thanks.

 

[jmd0252]

so what does you sql command look like,, you could always add the "where", portion of the command to do you record selection.

 

[Madawc]

You could do an IF in the SQL, select unconditionally if the parameter is ALL and conditinally by the parameter if not. Doing it as two commands would work, though maybe someone knows a better way.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[bdreed35]

I usually use something like this:

WHERE ('{?Param}' <> 'ALL' and table.field = '{?Param}')

~Brian

 

[lbass]

I agree with Brian except that you need to add a clause so that it becomes:

WHERE (
'{?Param}' <> 'ALL' and
table.field = '{?Param}'
) or
'{Param}' = 'ALL'

-LB

 

[bdreed35]

LB:

I have no issues not adding the additional clause in the command. Have you experienced any side effects from not using it?

~Brian

 

[scottostanek]

My usual method for an "All" option looks like this:

({?Salesman} = "ALL") or (({CUST_PRODUCT.Sales-Agent-ID} = {?Salesman}))

The parenthesis is around the outermost ref to the parameter

You can easily set this up as a startswith or contains kind of parameter as well.

Scotto the Unwise

 

[lbass]

Brian,

When I tested this with the Xtreme database, no records were returned before adding:

or
'{Param}' = 'ALL'

-LB