Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: If logic in WHERE condition inside SQL statement

    Hi,

    I would like to change where condition in SQL statement according to input value entered in application.

    Sample data:
    The logic is:
    - if there is no 'region_variable' (variable value is '') the SQL should be without REGION condition - so retrieving all data of all regions:
    SELECT * FROM DB2ADMIN.TAB WHERE STATUS = 'Y'

    - if REGION is 000 or 100 then SQL should be:
    SELECT * FROM DB2ADMIN.TAB WHERE STATUS = 'Y' AND REGION IN (:region_variable)

    Code:
    ID          REGION STATUS
    ----------- ------ ------
              1 000    Y
              2 100    Y
              3 100    Y
    How to write such an SQL to have an if logic in WHERE condition? Can I use CASE in WHERE condition?


    Code to create sample:
    Code:
    CREATE TABLE DB2ADMIN.TAB (ID INT NOT NULL PRIMARY KEY,
     REGION CHAR(3), STATUS CHAR(1));
    INSERT INTO DB2ADMIN.TAB VALUES (1,'000','Y'),(2,'100','Y'),(3,'100','Y');
    Thanks,
    Grofaty
    Last edited by grofaty; 02-21-07 at 09:52.

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    This might work:

    SELECT *
    FROM DB2ADMIN.TAB
    WHERE STATUS = 'Y'
    AND REGION =
    CASE WHEN :region_variable = '' THEN REGION
    ELSE :region_variable
    END
    ;


    if region_variable is blanc then the column REGION is compared with itself which is always true unless it is <NULL>.
    if there is a value in region_variable then the column is compared to that value.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    umayer, thanks a lot this works fine if :region_variable has only one value.

    One more question: I also have to write such an SQL that more than one value can exist inside :region_variable.

    Sample:
    SELECT * FROM DB2ADMIN.TAB WHERE STATUS = 'Y' AND REGION IN (:region_variable)

    :region_variable is '100', '000' (into application region field user writes: '100', '000' ) so SQL should be:
    SELECT * FROM DB2ADMIN.TAB WHERE STATUS = 'Y' AND REGION IN ('100', '000')

    How to write such an SQL that :region_variable could have more than one value plus the functionality to have no region at all like in previous sample?

    Thanks,
    Grofaty

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    you cant use the IN :region_value in the intended way ( besides using dynamic sql ).

    TRANSLATE might be helpful.
    ...
    WHERE STATUS = 'Y'
    AND ( :region_variable = '' OR TRANSLATE(:region_variable,'',REGION) ^= :region_variable )


    if more than one region is specified, the values must be separated by any character like dash or komma or blanc.

  5. #5
    Join Date
    May 2006
    Posts
    82
    It can be done using programming logic as well.
    If region_variable = ' ' then call without_region and some more logic like that.
    In the corresponding Subroutines you can code the respective SQL statements.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by umayer
    you cant use the IN :region_value in the intended way ( besides using dynamic sql ).

    TRANSLATE might be helpful.
    ...
    WHERE STATUS = 'Y'
    AND ( :region_variable = '' OR TRANSLATE(:region_variable,'',REGION) ^= :region_variable )


    if more than one region is specified, the values must be separated by any character like dash or komma or blanc.
    Umayer, thank you very much for helping me out.

    I have tried your suggestion but I think it is not working well:
    If :region_variable is '100,000' then SQL looks like:
    Code:
    SELECT * FROM DB2ADMIN.TAB
    WHERE STATUS = 'Y'
    AND ( '100,000' = '' OR TRANSLATE('100,000','',REGION) ^= '100,000');
    the result of this SQL is correct.

    But any other value alway returns all records which is not correct result.
    For example if :region_variable is '100' the SQL looks like:
    Code:
    SELECT * FROM DB2ADMIN.TAB
    WHERE STATUS = 'Y'
    AND ( '100' = '' OR TRANSLATE('100','',REGION) ^= '100');
    but it does not display only 100 rows, instead all rows from table are returned, which is not what I want.

    The same problem is if only '000' is used as :region_variable. But it looks fine if empty variable is used (so '').

    So I need some kind of solution that :regional_variable can be:
    - empty string, tha is: ''
    - value: 100
    - value: 000
    - value 000, 100 or 100, 000
    - or any other more then two values combination.

    Any idea?

    Thanks,
    Grofaty
    Last edited by grofaty; 02-21-07 at 13:57.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by vini_srcna
    It can be done using programming logic as well.
    The problem is that the program is already written. Our company bought "SQL reporting application" witch only accepts SQL as input and host variables definitions as input fields in web application. So I can't change program, only SQL is allowed to be changed. So I am desperately looking for SQL solution. Thanks for help anyway.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    An alternative is to use COALESCE .. Noit much different though ..

    Code:
    with temp(seqno,ctry,regn) as 
    (
    values(1,'GB','EU'),
    (2,'US','NA'),
    (3,'CA','NA')
    )
    select * 
    from temp
    where 
     
    regn=coalesce(case when :invar='' then cast(null as char(10)) else :invar),regn)
    Here when the input variable invar is ''
    then the sql will be equivalent of

    Code:
    with temp(seqno,ctry,regn) as 
    (
    values(1,'GB','EU'),
    (2,'US','NA'),
    (3,'CA','NA')
     )
     
     where 
     
     regn=coalesce(cast(null as char(10)),regn)

    Here all records will be selected as regn=regn

    If you give a value, say NA

    then
    the SQL will be equivalent of

    Code:
     with temp(seqno,ctry,regn) as 
     (
     values(1,'GB','EU'),
     (2,'US','NA'),
     (3,'CA','NA')
      )
     
      where 
     
     regn=coalesce('NA',regn)
    Here only NA records will be selected.

    I guess this will solve your problem.


    If you want ctry also be part of the where clause (ie, you get values for two columns) then the where clause should be :

    Code:
    ctry=coalesce(case when :invar1='' then cast(null as char(10)) else :invar1),ctry) and
    regn=coalesce(case when :invar2='' then cast(null as char(10)) else :invar2),regn)
    It doesn't matter if the user enters only one or both values.

    If the input value comes from the same source, and this value can be either regn or country then use OR in the where clause.

    Code:
    ctry=coalesce(case when :invar='' then cast(null as char(10)) else :invar),ctry) OR 
    regn=coalesce(case when :invar ='' then cast(null as char(10)) else :invar ),regn)
    HTH
    Sathyaram
    Last edited by sathyaram_s; 02-21-07 at 14:33.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Sorry grofaty, the following may be an acceptable solution if multiple regions could come in ..

    For this solution, you will need a table UDF .. This UDF assumes a comma seperated input list :

    Code:
    CREATE FUNCTION CSV_TABLE(INLIST VARCHAR(1000))
    RETURNS TABLE(LISTVAL CHAR(10))
    BEGIN ATOMIC
    RETURN
    WITH TEMP1(STR1) AS
    (
    VALUES(REPLACE(INLIST||',''''','''',''))
    )
    ,
    TEMP2(STR3,STR4) AS
    (
    SELECT LEFT(STR1,POSSTR(STR1,',')-1),SUBSTR(STR1,POSSTR(STR1,',')+1) FROM TEMP1
    UNION ALL
    SELECT LEFT(STR4,POSSTR(STR4,',')-1),SUBSTR(STR4,POSSTR(STR4,',')+1) FROM TEMP2 WHERE LENGTH(STR4)>0
    )
    SELECT STR3 FROM TEMP2 ;
    END
    Code:
    with temp(seqno,ctry,regn) as 
    (
    values(1,'GB','EU'),
    (2,'US','NA'),
    (3,'CA','NA'),
    (4,'IN','AP')
    )
    select * 
    from temp
    where 
    regn in (values(case when :invar='' then regn end )) OR 
    regn in (select rgnname from table(csv_table(:invar)) as rgnlist(rgnname))
    Or, you can use a CTE instead of the Table UDF

    Code:
    WITH TEMP1(STR1) AS
    (
    VALUES(REPLACE(:invar||',''''','''',''))
    )
    ,
    TEMP2(rgnname,STR4) AS
    (
    SELECT LEFT(STR1,POSSTR(STR1,',')-1),SUBSTR(STR1,POSSTR(STR1,',')+1) FROM TEMP1
    UNION ALL
    SELECT LEFT(STR4,POSSTR(STR4,',')-1),SUBSTR(STR4,POSSTR(STR4,',')+1) FROM TEMP2 WHERE LENGTH(STR4)>0
    ),
    temp(seqno,ctry,regn) as 
    (
    values(1,'GB','EU'),
    (2,'US','NA'),
    (3,'CA','NA'),
    (4,'IN','AP')
    )
    select * 
    from temp
    where 
    regn in (values(case when :invar='' then regn end  )) OR 
    regn in (select rgnname from temp2 )
    HTH

    Sathyaram
    Last edited by sathyaram_s; 02-21-07 at 15:01.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by grofaty
    The problem is that the program is already written. Our company bought "SQL reporting application" witch only accepts SQL as input and host variables definitions as input fields in web application. So I can't change program, only SQL is allowed to be changed.
    For what it's worth, we used a stored procedure that returned a result set in a similar situation. You can implement any complex logic in a stored procedure or use dynamic SQL.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    n_i, Will that not mean a change of application code ?

    I mean, for example in Java, you use SQL Statement objects and execute them for SQL Statements whereas stored procs use callable stmts ...
    Also, I guess the host language stmts for handling SQL resultsets may be different from handling Stored Proc resultsets ..

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    sathyaram_s, I haven't tested your suggestions yet it looks so complex...

    I have one solution but it is not complete:

    Code:
    SELECT * FROM DB2ADMIN.TAB WHERE STATUS='Y' AND REGION IN (:replace_variable)
    UNION ALL
    SELECT * FROM DB2ADMIN.TAB WHERE NOT EXISTS
    (
    SELECT * FROM DB2ADMIN.TAB WHERE STATUS='Y' AND REGION IN (:replace_variable)
    )
    If :replace_variable is '000' the result is fine.
    If :replace_variable is '100' the result is fine.
    If :replace_variable is '000','100' the result is fine.
    If :replace_variable is '100','000' the result is fine.
    If :replace_variable is empty (so '') the result is fine.
    The problem is if there is one or all values that does not exist in table. E.g. value '300' or '300','400', etc. If so all the rows from the table are returned instead of no row.

    Any idea how to solve this problem? As I see there should be some kind of LENGTH function to solve the problem.

    Thanks,
    Grofaty
    Last edited by grofaty; 02-21-07 at 17:02.

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The complexity is my solution is primarily to do with the multi-value requirement ...

    I cannot think of a non-recursive solution because you do not know the number of values that come in ...

    Also remember, if hostvar is '000','001' then when db2 processes "in (:hostvar) " , it does not consider the value as two separate strings but as one string only ... So effectively, you will be looking for a region with a value '000','001' and NOT region '000' or '001'


    Back to the basics, please clarify - what is the replace_variable .. Is it a DB2 host variable or is it a Unix variable - where you just go and find/replace the string with your input values ?

    I assume it is a db2 host variable ...

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  14. #14
    Join Date
    Jan 2003
    Posts
    1,605
    sathyaram_s, I have been talking to programmer of this reporting application and application works like this:
    - application gets data from host variable and store this variable into memory
    - then application concats SQL and result of host variable into one SQL
    - SQL is executed

    I have tried this out one simple sample and inserting '000','001' into application input field and it works like I have described above. So '000','100' in input field as host_variable it is working well.
    Last edited by grofaty; 02-22-07 at 03:45.

  15. #15
    Join Date
    Dec 2005
    Posts
    273
    @ grofaty

    you are right, I muddled up REPLACE and TRANSLATE.

    please try:

    WHERE STATUS = 'Y'
    AND ( :region_variable = '' OR REPLACE(:region_variable,REGION,'') ^= :region_variable )


    sorry for my mistake.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •