Results 1 to 12 of 12

Thread: Case statement

  1. #1
    Join Date
    Jul 2013
    Posts
    7

    Unanswered: Case statement

    I have to write a case statement where the scenario is:
    For option 'A' only column values with 'A' option sud come but for selection for Option 'B' all the vlues sud come from the table ie. 'A' and 'B' since the column has only 'A' and 'B' values.
    So i have writtien the case as below:

    CASE WHEN Table.col a In ('A') THEN 'Ant'
    when Table.col a In('A','B') then 'Ball' .

    Now this is properly executing for 'Ant' and 'A' vlaues are coming.
    But for 'Ball' option only 'B' values are coming ,no 'A' values are coming.
    I have written the second statement as for that condition all column values sud come which in this scenario consist of 'A' and 'B'.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think you have a basic misunderstanding of how if-then-else logic works.
    You might want to use a UNION ALL statement to solve your problem.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2013
    Posts
    7
    Better i will tell what i need.
    The option is like that when the table has column values as 'A' then it sud show 'Ant' and when the table has both 'A' and 'B' values then 'Ball' option sud be shown.

    The scenario wants when the selection of 'Ant'is made,'A' data sud come and for selection of 'Ball',all the data from table sud come.
    Since the table has only 'A' and 'B' data tht's why for second condition i am giving 'A' and 'B' option for retreival of all the data.Tht's why this statement.
    so i have to work the condition for
    if 'A' then 'Ant'
    else if 'A' and 'B' then 'Ball'

    If u have any other option,then pls suggest.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by biswa View Post
    Better i will tell what i need.
    The option is like that when the table has column values as 'A' then it sud show 'Ant' and when the table has both 'A' and 'B' values then 'Ball' option sud be shown.

    The scenario wants when the selection of 'Ant'is made,'A' data sud come and for selection of 'Ball',all the data from table sud come.
    Since the table has only 'A' and 'B' data tht's why for second condition i am giving 'A' and 'B' option for retreival of all the data.Tht's why this statement.
    so i have to work the condition for
    if 'A' then 'Ant'
    else if 'A' and 'B' then 'Ball'

    If u have any other option,then pls suggest.
    UNION ALL. Check the SQL reference manual if you don't understand.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Oct 2013
    Posts
    1

    data base d-1

    hello,

    what is the better form to create a database D-1 on DB2?
    I need this to create reports and not impact the environment of production.

    thank you!
    Last edited by andersonhpolo; 10-08-13 at 11:25.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by biswa View Post
    I have to write a case statement where the scenario is:
    For option 'A' only column values with 'A' option sud come but for selection for Option 'B' all the vlues sud come from the table ie. 'A' and 'B' since the column has only 'A' and 'B' values.
    So i have writtien the case as below:

    CASE WHEN Table.col a In ('A') THEN 'Ant'
    when Table.col a In('A','B') then 'Ball' .

    Now this is properly executing for 'Ant' and 'A' vlaues are coming.
    But for 'Ball' option only 'B' values are coming ,no 'A' values are coming.
    I have written the second statement as for that condition all column values sud come which in this scenario consist of 'A' and 'B'.
    Your requirements were not clear for me.
    (My be because I'm not native of English)

    Please post DDL(s) and INSERT statement(s) and expected result from the data.

    For example,
    wha result do you want from the following test table and data?

    If the table and/or data were not appropriate to describe your issue,
    please post DDL(s) and INSERT statement(s) to describe your issue and expected result from the data.

    Code:
    CREATE TABLE test.case_biswa
    ( pk1   SMALLINT NOT NULL
    , pk2   SMALLINT NOT NULL
    , col_a CHAR(1)  NOT NULL
    , description VARCHAR(20)
    , PRIMARY KEY (pk1 , pk2)
    );
    
    INSERT INTO test.case_biswa
    VALUES
      ( 1 , 1 , 'A' , 'A only' )
    , ( 2 , 3 , 'B' , '      B only' )
    , ( 3 , 1 , 'A' , 'A and B' )
    , ( 3 , 4 , 'B' , 'A and B' )
    , ( 4 , 3 , 'A' , 'A and B and C' )
    , ( 4 , 4 , 'B' , 'A and B and C' )
    , ( 4 , 5 , 'C' , 'A and B and C' )
    , ( 5 , 2 , 'A' , 'A       and C' )
    , ( 5 , 4 , 'C' , 'A       and C' )
    , ( 6 , 1 , 'B' , '      B and D' )
    , ( 6 , 2 , 'D' , '      B and D' )
    , ( 7 , 1 , 'E' , 'no A and no B' )
    ;

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test.case_biswa;
    ------------------------------------------------------------------------------
    
    PK1    PK2    COL_A DESCRIPTION         
    ------ ------ ----- --------------------
         1      1 A     A only              
         2      3 B           B only        
         3      1 A     A and B             
         3      4 B     A and B             
         4      3 A     A and B and C       
         4      4 B     A and B and C       
         4      5 C     A and B and C       
         5      2 A     A       and C       
         5      4 C     A       and C       
         6      1 B           B and D       
         6      2 D           B and D       
         7      1 E     no A and no B       
    
      12 record(s) selected.

  7. #7
    Join Date
    Jul 2013
    Posts
    7
    The Condition is for the front end Report.
    Now in the report,I have the to show 'Ant' and 'Ball' option to the user.
    When user selects 'Ant' then all data from the table for COL_A(below ex) containg 'A' column values sud come.
    When user selects 'Ball' then all data containing 'A' and 'B' values ie. all the values from the table sud come.

    The table has only value 'A' and 'B' for tht particulat column.

    For examle the table looks like

    Col 1 Col 2 COL_A
    ------ ------ ----- --------------------
    1 x A
    2 y B
    3 z A
    4 l A
    5 m B
    6 o A
    7 o B
    8 p A
    9 q A
    10 r B
    11 s B
    12 t A

    So i have written the statement.The same case statement condition worked earlier when application was intefaced with
    oracle and i used In operator but maybe in DB2 case statement we cannot use In operator.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Biswa,
    Marcus has answered you twice already, look up. You have a misconception of how CASE and IN work. A case statement ends once the first matching condition is found. An IN states find any in my list. You are looking for must have both. Completely different scenario.

    Dave

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try this example.

    Here, <selected_option> is a declared variable or a parameter which includes the option selected by user.
    Code:
    SELECT t.*
     FROM  sample_data AS t
     WHERE <selected_option> = 'Ant'
       AND col_a = 'A'
       OR  <selected_option> = 'Ball'
       AND col_a IN ('A' , 'B')
    ;

    By the way,
    what is "sud"? (I'm not well at English.)
    For example, you wrote...
    For option 'A' only column values with 'A' option sud come
    but for selection for Option 'B' all the vlues sud come from the table ...

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tonkuma View Post
    By the way, what is "sud"? (I'm not well at English.)
    That is not English.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by biswa View Post
    ...

    When user selects 'Ball' then all data containing 'A' and 'B' values ie. all the values from the table sud come.

    The table has only value 'A' and 'B' for tht particulat column.

    ...
    If it was guaranteed, the query might be simplified more, like...

    Code:
    SELECT t.*
     FROM  sample_case_biswa AS t
        ,  parameter
     WHERE <selected_option> = 'Ant'
       AND col_a = 'A'
       OR  <selected_option> = 'Ball'
    ;

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The table has only value 'A' and 'B' for tht particulat column.
    If the condition was guaranteed,
    the query might be simplified more, like...
    Code:
    SELECT t.*
     FROM  sample_data AS t
     WHERE col_a = 'A'
       OR  selected_option = 'Ball'

Posting Permissions

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