Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    6

    Unanswered: WHERE EXISTS within CASE Statement

    In SQL you can have a WHERE EXISTS within a CASE statement, but my DB2 isn't allowing it. I am able to use a WHERE EXISTS after a WHERE clause. I'm connected via the IBM ODBC driver. I'm not sure about the driver version, but my iSeries Navigator version is 5.4.

    What I'm trying to do can be illustrated as:

    Code:
    SELECT colA, colB,
           CASE 
              WHEN EXISTS (SELECT * FROM tab2 WHERE tab2.colA = tab1.colA) 
                 THEN '1'
              WHEN EXISTS (SELECT * FROM tab3 WHERE tab3.colA = tab3.colA) 
                 THEN '2'
              ELSE '0'
           END AS colC
      FROM tab1;
    The error I get back is:

    ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDBJSQL0104 - Token EXISTS was not valid. Valid tokens: <IDENTIFIER> <INTEGER> <CHARSTRING> <GRAPHSTRING>.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    An alternative would be:
    Code:
    SELECT
       colA, colB
    ,  CASE 
          WHEN tab2.colA IS NOT NULL THEN '1'
          WHEN tab3.colA IS NOT NULL THEN '2'
          ELSE '0'
       END AS colC
       FROM tab1
       LEFT OUTER JOIN tab2
          ON (tab2.colA = tab1.colA)
       LEFT OUTER JOIN tab3
          ON (tab3.colA = tab1.colA);
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Posts
    6

    Amended Information

    I'm sorry I posted a very generic example that didn't reflect what I'm actually trying to do. Below is a more focused and customized presentation. Please note that while the item field in the TBL_INVENTORY table is unique, the item field in the TBL_HISTORY table can repeat - and that seems to be the cause of the difficulty getting this sort of query to work in DB2. Also, though this example only shows two columns in the output, potentially there could be a dozen summing various combinations of HIST_DESCR codes.
    Code:
    Select INV_TERRITORY
    ,(SUM(1)) As ItemCount
    ,Sum(Case When EXISTS (select *
                  from TBL_HISTORY
                  where HIST_DESCR IN ('DELETED', 'OBSOLETE', 'SUPERSEDED')
                  AND HIST_ITEM = INV_ITEM)) Then 1 Else 0 End) As InactiveCount
    From TBL_INVENTORY
    GROUP BY INV_TERRITORY
    ORDER BY INV_TERRITORY
    TBL_INVENTORY:

    INV_TERRITORY|INV_ITEM
    MAINE--------|11111
    CALIFORNIA---|22222
    KANSAS------|33333
    CALIFORNIA--|44444
    KANSAS------|55555
    CALIFORNIA--|66666
    MAINE-------|77777
    WYOMING----|88888

    --------------------------------------------
    TBL_HISTORY:

    HIST_ITEM|HIST_DESCR
    33333----|ACTIVE
    33333----|DELETED
    33333----|ACTIVE
    55555----|SUPERSEDED
    55555----|DELETED
    22222----|ACTIVE
    33333----|NEW
    44444----|OBSOLETE
    88888----|ACTIVE
    --------------------------------------------------
    DESIRED RESULT:

    INV_TERRITORY|ItemCount|InactiveCount
    CALIFORNIA---|000000003|0000000000001
    KANSAS-------|000000002|0000000000002
    MAINE---------|000000002|0000000000000
    WYOMING-----|000000001|0000000000000

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The idea scales well.
    Code:
    SELECT
       INV_TERRITORY
    ,  Sum(1) As ItemCount
    ,  Sum(
          CASE
             WHEN HIST_DESCR IN ('DELETED', 'OBSOLETE', 'SUPERSEDED') THEN 1
             ELSE 0
          END
       ) As InactiveCount
       FROM TBL_INVENTORY
       LEFT JOIN TBL_HISTORY
          ON (TBL_HISTORY.HIST_ITEM = TBL_INVENTORY.INV_ITEM)
       GROUP BY INV_TERRITORY
       ORDER BY INV_TERRITORY;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2013
    Posts
    6

    A Lot Better

    Thanks for your help. I can't create tables on the DB2 but I created the two on SQL 2005 using the test data and ran my script and found out I was unfairly blaming DB2 - it doesn't run in SQL either, but the error message makes a little more sense: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery".

    I tried your script suggestion and this is what it yields for the sample data:

    YOUR SCRIPT RESULT:

    INV_TERRITORY|ItemCount|InactiveCount
    CALIFORNIA---|000000003|0000000000001
    KANSAS-------|000000006|0000000000003
    MAINE--------|000000002|0000000000000
    WYOMING------|000000001|0000000000000

    DESIRED RESULT:

    INV_TERRITORY|ItemCount|InactiveCount
    CALIFORNIA---|000000003|0000000000001
    KANSAS-------|000000002|0000000000002
    MAINE--------|000000002|0000000000000
    WYOMING------|000000001|0000000000000

    If I delete the duplicate HIST_ITEM rows from the TBL_HISTORY table for ACTIVE and SUPERSEDED, then your script yields the correct counts for KANSAS. However, the central point of this issue is the problem that the duplicate HIST_ITEM rows in TBL_HISTORY are causing in coming up with accurate counts.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try COUNT(DISTINCT expression), like
    Code:
    SELECT inv_territory
         , COUNT(DISTINCT inv_item) AS ItemCount
         , COUNT(DISTINCT
                 CASE
                 WHEN hist_descr IN ('DELETED' , 'OBSOLETE' , 'SUPERSEDED') THEN
                      hist_item
                 END
                ) AS InactiveCount
     FROM  tbl_inventory AS i
     LEFT  JOIN
           tbl_history   AS h
      ON   h.hist_item = i.inv_item
     GROUP BY
           inv_territory
     ORDER BY
           inv_territory
    ;
    Last edited by tonkuma; 03-05-13 at 08:32.

Posting Permissions

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