Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: PRD 3.5 - creation of SQL query for conditional null or non-null value

  1. #1
    Join Date
    Dec 2009
    Posts
    12

    Default PRD 3.5 - creation of SQL query for conditional null or non-null value

    Hi,
    I am using Reports Designer 3.5 and we are stuck up with generating SQL query where one of paramters depending upon some user action needs to be passed as 'null' ( IS NULL in db lanaguage). How could we do that?

    Normal case:
    select x1, x2.. from table A where A.y1 = 'somevale';

    Conditional case:
    select x1,x2.. from table A where A.y1 is null; //i can't pass A.y1 = null

    The alternative solution would be creating of 2 reports handling each case but that wouldn't be not-so-elegant solution....

    Any help?

    Thanks,
    Manish

  2. #2
    Join Date
    Oct 2007
    Posts
    235

    Default

    You could try some thing like the following:

    select * from A where coalesce({value}, 'some weird value that cant happen and can represent null') = coalesce(A.y1, 'some weird value that cant happen and can represent null')

    Don't know how well that would work on your db, but it seems to work ok on my postgres db

    good luck

    Wil
    SQL: as much of a standard as the English language

  3. #3
    Join Date
    Dec 2009
    Posts
    12

    Default

    select * from A where coalesce({value}, 'some weird value that cant happen and can represent null') = coalesce(A.y1, 'some weird value that cant happen and can represent null')

    This wouldn't work as when {value} is null, the where clause will be evaluated as
    "select * from A where null = A.y1" which is wrong...rather I want it to be evaluated as "select * from A where A.y1 is null".

    OR, I am missing something here ????

  4. #4
    Join Date
    Oct 2007
    Posts
    235

    Default

    Your missing some thing.

    The coalesce functions pick the first non-null parameter as the return value, so in the first part if {value} is null it sets it to 'some weird value that cant happen and can represent null' The same thing happens on the A.y1 so that any nulls end up as the 'some weird value that cant happen and can represent null' as well and thus the = works because when its null you get

    "where 'some weird value that cant happen and can represent null' = A.y1" (and if a.y1 happens to be null as well then it is converted to the 'some weird value that cant happen and can represent null' value)

    Pick your own value to be 'some weird value that cant happen and can represent null' depending on your data types and possible values.

    Good luck

    Wil
    SQL: as much of a standard as the English language

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.