Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Need assistance in CASE statements in DB2 SQL query

    Hi All,

    I have a query with CASE statement and this query for few accounts doesn't retrieve any rows from the database i.e., SQLCODE = +100. So, my requirement is to handle that SQLCODE within the single query using "WITH TEMP AS". But not able to get it, any assistance on this would be highly appreciated.

    Find below the query:
    WITH TEMPA AS (
    SELECT COUNT(*) AS CNT FROM TABLE1
    WHERE ACCOUNT = ACNT1),

    TEMPB AS (
    SELECT SALARY FROM TABLE1
    WHERE ACCOUNT = ACNT1)

    SELECT
    CASE WHEN TEMPA.CNT > 1
    THEN TEMPB.SALARY
    ELSE 0
    END
    FROM TEMPA, TEMPB

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well its probably best to ask the question in the DB2 thread then
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    Code:
    select coalesce((select salary from tab1 where account = acnt1),0)
    from sysibm.sysdummy1

  4. #4
    Join Date
    Apr 2012
    Posts
    5
    For better solution, can you please provide the data in tempa and tempb.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try this:

    Example 1:
    Code:
    SELECT CASE
           WHEN cnt > 1 THEN
                salary
           ELSE 0
           END AS salary_list
     FROM (SELECT salary
                , COUNT(*) OVER() AS cnt
            FROM (VALUES 'xxxxx' ) AS p(acnt1)
            LEFT  OUTER JOIN
                  TABLE1
             ON   account = acnt1
          ) t
    ;
    Note: Change "(VALUES 'xxxxx' )" according to the datatypes of account and acnt1.


    Here are some test results.
    Note: Same test data were used in all tests.

    Example 1-t1: Three rows for account = 'A0001'
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /****************************************
    ********** Start of test data. **********
    ****************************************/
      TABLE1(account , salary) AS (
    VALUES
      ( 'A0001' , 1500 )
    , ( 'A0001' ,  800 )
    , ( 'A0001' , 1100 )
    , ( 'B0001' , 1000 )
    , ( 'D0001' ,  900 )
    , ( 'D0001' , 1200 )
    )
    /****************************************
    **********   End of test data. **********
    ****************************************/
    SELECT CASE
           WHEN cnt > 1 THEN
                salary
           ELSE 0
           END AS salary_list
     FROM (SELECT salary
                , COUNT(*) OVER() AS cnt
            FROM (VALUES 'A0001' ) AS p(acnt1)
            LEFT  OUTER JOIN
                  TABLE1
             ON   account = acnt1
          ) t
    ;
    ------------------------------------------------------------------------------
    
    SALARY_LIST
    -----------
            800
           1100
           1500
    
      3 record(s) selected.
    Example 1-t2: Only one row for account = 'B0001'
    Code:
    SELECT CASE
           WHEN cnt > 1 THEN
                salary
           ELSE 0
           END AS salary_list
     FROM (SELECT salary
                , COUNT(*) OVER() AS cnt
            FROM (VALUES 'B0001' ) AS p(acnt1)
            LEFT  OUTER JOIN
                  TABLE1
             ON   account = acnt1
          ) t
    ;
    ------------------------------------------------------------------------------
    
    SALARY_LIST
    -----------
              0
    
      1 record(s) selected.

    Example 1-t3: No row for account = 'C0001'
    Code:
    SELECT CASE
           WHEN cnt > 1 THEN
                salary
           ELSE 0
           END AS salary_list
     FROM (SELECT salary
                , COUNT(*) OVER() AS cnt
            FROM (VALUES 'C0001' ) AS p(acnt1)
            LEFT  OUTER JOIN
                  TABLE1
             ON   account = acnt1
          ) t
    ;
    ------------------------------------------------------------------------------
    
    SALARY_LIST
    -----------
              0
    
      1 record(s) selected.
    Last edited by tonkuma; 04-23-12 at 22:57. Reason: Replace COUNT(salary) with COUNT(*). Add alias "t" for subquery.

Tags for this Thread

Posting Permissions

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