How do I work with null values when I'm trying to query a collection subset

[Ben]

I'm using a multivalue parameter and its currently giving me a little grief for a single case. Null

my query is:

select * from table where column1 in (?)

which ends up looking like
select * from table where column1 in ('a','b','c')

this is a problem with null included because it evaluates to unknown and can't continue.

with the following query this works but I'm not sure of how to set up the parameters to make this work.


select * from table where column1 in ('a','b','c') or tag is null

Can you help me understand how I can set up or format my multivalue parameter to deal with this?

[Stef]

Hello Ben,

Please check the following forum discussion and the used solutions for cases when you need to check for Null: Multivalue parameter with AllowNull = true is not working.


I hope the above information is helpful.

Regards,
Stef
Telerik

 

 

[Ben]

the option for coalesce doesn't seem to be quite what I'm looking for. with coalesce it takes the first non null parameter so if I only want to look at the null situation thats fine but if I want to look at the situation where its null and 2 or 3 other values this doesn't work.

Any other thoughts?

[Ben]

I was able to solve this by wrapping the null condition up in a sub query

select * from (
select isnull(column,'some_value') from table
) where column in (@columnValues)

this gives the column a value even when null. then I just have the same isnull check on my query that allows me to pick my columns from the multivalue report parameter