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

4 posts, 0 answers
  1. Ben
    Ben avatar
    6 posts
    Member since:
    Sep 2014

    Posted 17 Sep 2014 Link to this post

    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?

  2. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 22 Sep 2014 Link to this post

    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
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  3. DevCraft banner
  4. Ben
    Ben avatar
    6 posts
    Member since:
    Sep 2014

    Posted 26 Sep 2014 in reply to Stef Link to this post

    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?
  5. Ben
    Ben avatar
    6 posts
    Member since:
    Sep 2014

    Posted 30 Sep 2014 Link to this post

    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
Back to Top