SQL (boolean) error 

SQL (boolean) error 

  

Hi

I don't know what's wrong with the query (tested it with value true). If the param is true select all, otherwise select with a where clause. 

Regards, Harry


Got it:

IF @is20 = 'TRUE'
    SELECT * FROM {Leervak} 
ELSE 
    SELECT * FROM {Leervak} WHERE {Leervak}.[LeraarBoven20] = 'FALSE'

Harry

Solution

Hi Harry,

Of course I don't know your application, but it seems like you could best define the field 'LeraarBoven20' as a boolean field (true/false), instead of a text field. In that case you need to use 1 or 0 when using an advanced query. In this case the query would be (with a little optimization to remove the if-statement):

SELECT {Leervak}.* FROM {Leervak} WHERE @is20 = 1 or {Leervak}.[LeraarBoven20] = 0

Best practive would be to use an aggregate whenever possible. Here you can also define the Filter as:

Is20 = False or Leervak.LeraarBoven20 = false


Goodluck!

Solution

Hi Lennart,

Great solution. Changed it to Is20 = True or Leervak.LeraarBoven20 = false

but works like a charm (will mark it as a solution). Thanks

Regards Harry