if condition in sql where statement

[jasemilly]

Hi guys

I have this condition

 

where IF($P{Usr_Rng}<>'All',Range = $P{Usr_Rng}, Range <> $P{Usr_Rng})

 

and it says incorrect condition near IF and I can't see whats wrong!!!!!! the parameter Usr_Rng is a string.

 

The user will be promtped with a list of Range codes and the word All as the first choice. 

 

If they choose All then this statement should be false and everything not like Range should be selected,

 

if they choose a range then the condition is true and it filters on the desired code.  But I can't see whats wrong with the syntax

Any ideas would be much appreciated

 

[pjamack]

 If you're righting a SQL Query, try this instead...

 

 WHERE    $P{Usr_Rng}<>'All' 

 

Not sure about Range= this or that since if you're writing a query in a JasperReport, the Range needs to have meaning in Jasper as well, otherwise it'll throw an error.

[jasemilly]

I got the If statement from a search and it works fine for them.  The user has a long list of range codes to filter on or I would like them to choose All, which would effectively mean no filter. 

 

I know you can do if statements in sql just the format a little unsure of.

 

thanks jasemilly

[jasemilly]

yes it's an sql statment and range is a field name

[jasemilly]

I have it  working, a slight change in thought and it's much less complex.

 

the where statement now just looks like this

 

where ($P{myrange} = 'ALL') or ([id] = $P{myrange})

 

it works perfectly the user gets everything if they select all or an indiviual range if thats what they select.  I have expanded it to include a few other user options to increase flexibity with out lots of complex SQL statemtents or other form of control logic.