Professional Web Applications Themes

Problems with CASE statement - IBM DB2

Helpful folks, I seem to be misunderstanding the use of the CASE statement as used within a WHERE clause. I had thought that one could do the following to prioritize possible results, but the example below returns two rows, where I would expect it to return one: CREATE TABLE DB2ADMIN.SPCTEMP ( COL1 CHAR(6) ) IN USERSPACE1 NOT LOGGED INITIALLY; COMMIT; INSERT INTO SPCTEMP VALUES ('ABC'); INSERT INTO SPCTEMP VALUES ('XYZ'); SELECT * FROM SPCTEMP WHERE 1 = CASE WHEN COL1 = 'ABC' THEN 1 WHEN COL1 = 'XYZ' THEN 1 ELSE 0 END ; The intent is, if there are ...

  1. #1

    Default Problems with CASE statement

    Helpful folks,

    I seem to be misunderstanding the use of the CASE statement as used
    within a WHERE clause. I had thought that one could do the following
    to prioritize possible results, but the example below returns two
    rows, where I would expect it to return one:

    CREATE TABLE DB2ADMIN.SPCTEMP (
    COL1 CHAR(6)
    )
    IN USERSPACE1 NOT LOGGED INITIALLY;
    COMMIT;
    INSERT INTO SPCTEMP VALUES ('ABC');
    INSERT INTO SPCTEMP VALUES ('XYZ');


    SELECT * FROM SPCTEMP
    WHERE
    1 =
    CASE
    WHEN COL1 = 'ABC' THEN 1
    WHEN COL1 = 'XYZ' THEN 1
    ELSE 0
    END
    ;

    The intent is, if there are rows which contain both 'abc' and 'xyz',
    then I only want to return the rows with 'abc'. if there are no 'abc'
    rows, then I'll take the 'xyz' rows. My understanding of the CASE
    statement is that it ends when it encounters the first TRUE condition,
    but this example seems to 'fall through' to the following WHEN even if
    the first WHEN evaluates to true. What am I missing?
    Any help would be appreciated.
    Sean C. Guest

  2. #2

    Default Re: Problems with CASE statement

    "Sean C." wrote:
    > Helpful folks,
    >
    > I seem to be misunderstanding the use of the CASE statement as used
    > within a WHERE clause. I had thought that one could do the following
    > to prioritize possible results, but the example below returns two
    > rows, where I would expect it to return one:
    >
    > CREATE TABLE DB2ADMIN.SPCTEMP (
    > COL1 CHAR(6)
    > )
    > IN USERSPACE1 NOT LOGGED INITIALLY;
    > COMMIT;
    > INSERT INTO SPCTEMP VALUES ('ABC');
    > INSERT INTO SPCTEMP VALUES ('XYZ');
    >
    > SELECT * FROM SPCTEMP
    > WHERE
    > 1 =
    > CASE
    > WHEN COL1 = 'ABC' THEN 1
    > WHEN COL1 = 'XYZ' THEN 1
    > ELSE 0
    > END
    > ;
    >
    > The intent is, if there are rows which contain both 'abc' and 'xyz',
    > then I only want to return the rows with 'abc'. if there are no 'abc'
    > rows, then I'll take the 'xyz' rows. My understanding of the CASE
    > statement is that it ends when it encounters the first TRUE condition,
    > but this example seems to 'fall through' to the following WHEN even if
    > the first WHEN evaluates to true. What am I missing?
    > Any help would be appreciated.
    Seems to me the CASE statement is the last thing you should be using. Try:

    SELECT * FROM SPCTEMP
    WHERE col1 IN ('ABC','XYZ');

    --
    Daniel Morgan
    [url]http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp[/url]
    [url]http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp[/url]
    [email]damorganx.washington.edu[/email]
    (replace 'x' with a 'u' to reply)


    Daniel Morgan Guest

  3. #3

    Default Re: Problems with CASE statement

    [email]dba_fla[/email] (Sean C.) wrote in message news:<2f9c2d92.0308281123.5ce4c28cposting.google. com>...
    > Helpful folks,
    >
    > I seem to be misunderstanding the use of the CASE statement as used
    > within a WHERE clause. I had thought that one could do the following
    > to prioritize possible results, but the example below returns two
    > rows, where I would expect it to return one:
    >
    > CREATE TABLE DB2ADMIN.SPCTEMP (
    > COL1 CHAR(6)
    > )
    > IN USERSPACE1 NOT LOGGED INITIALLY;
    > COMMIT;
    > INSERT INTO SPCTEMP VALUES ('ABC');
    > INSERT INTO SPCTEMP VALUES ('XYZ');
    >
    >
    > SELECT * FROM SPCTEMP
    > WHERE
    > 1 =
    > CASE
    > WHEN COL1 = 'ABC' THEN 1
    > WHEN COL1 = 'XYZ' THEN 1
    > ELSE 0
    > END
    > ;
    >
    > The intent is, if there are rows which contain both 'abc' and 'xyz',
    > then I only want to return the rows with 'abc'. if there are no 'abc'
    > rows, then I'll take the 'xyz' rows. My understanding of the CASE
    > statement is that it ends when it encounters the first TRUE condition,
    > but this example seems to 'fall through' to the following WHEN even if
    > the first WHEN evaluates to true. What am I missing?
    > Any help would be appreciated.
    Predicates in a WHERE clause are evaluated for each rows.
    In your example of CASE expression:
    For 1st row(VALUES ('ABC')), it will end after evaluation of first condition.
    For 2nd row(VALUES ('XYZ')), it will end after evaluation of second condition.
    Hence, both rows will be selected.

    For your requirement, how about this way?
    SELECT * FROM SPCTEMP
    WHERE COL1 = 'ABC'
    OR
    COL1 = 'XYZ'
    AND
    NOT EXISTS (SELECT * FROM SPCTEMP
    WHERE COL1 = 'ABC'
    )
    ;
    Tokunaga T. Guest

  4. #4

    Default Re: Problems with CASE statement

    Thanks for the feedback guys.
    Daniel, the example I posted was as simplistic as I could devise,
    just to show the behavior I was observing. My requirements are far
    more complex, and the query I was trying to construct is given below.
    In a nutshell, the situation is that we have an 'Override' table
    (DATAFIX) that is used to convert or override the values on incoming
    order data. The criteria used to determine if an override should occur
    is to match on 4 distinct column values coming in on the order. For
    simplicity, I've labelled them DATA1 through DATA4. The design of the
    DATAFIX 'override' table is that rows can exist in this table that can
    match on all four column values, or on only one, or any combination.
    The application must find the most 'granular' match and use that row
    to supply override values. That is, if an order has values in all four
    DATA columns, and there is a row on the DATAFIX table that has values
    that match the first two DATA columns and another row that matches on
    all four DATA columns, I want to select the latter row.
    I thought I could implement this 'most granular to least granular'
    prioritizing by using the 'exit on first true condition' nature of the
    CASE statement. So, the WHEN clauses in the CASE statement below are
    ordered in most granular to least granular order.

    Evidently, as Tokunaga T mentioned, I cannot use that method in a
    WHERE clause. If anyone can provide a more elegant solution I would be
    most appreciative.

    Thanks,
    Sean


    select D.DATA1, D.DATA2, D.DATA3, D.DATA4, O.DATA1, O.DATA2, O.DATA3,
    O.DATA4
    from DATAFIX D, ORDERS O

    where O.orderid = 99999999
    and D.SEARCHCODE = O.SEARCHCODE

    and (D.DATA1 is not null or D.DATA2 is not null or D.DATA3 is not null
    or D.DATA4 is not null)
    and (D.DATA1 = O.DATA1)

    and(
    CASE

    WHEN (O.DATA2 IS NOT NULL AND D.DATA3 IS NOT NULL AND O.DATA4 IS
    NOT NULL
    AND D.DATA2 = O.DATA2 AND D.DATA3 = O.DATA3 AND D.DATA4 =
    O.DATA4) THEN 1

    WHEN (O.DATA2 IS NOT NULL AND D.DATA3 IS NOT NULL AND O.DATA4 IS
    NULL
    AND D.DATA2 = O.DATA2 AND D.DATA3 = O.DATA3) THEN 1

    WHEN (O.DATA2 IS NULL AND O.DATA3 IS NOT NULL AND O.DATA4 IS NOT
    NULL
    AND D.DATA3 = O.DATA3 AND D.DATA4 = O.DATA4) THEN 1

    WHEN (O.DATA2 IS NOT NULL AND O.DATA3 IS NULL AND O.DATA4 IS NOT
    NULL
    AND D.DATA2 = O.DATA2 AND D.DATA4 = O.DATA4) THEN 1

    WHEN (O.DATA2 IS NOT NULL AND D.DATA2 = O.DATA2) THEN 1

    WHEN (O.DATA3 IS NOT NULL AND D.DATA3 = O.DATA3) THEN 1

    WHEN (O.DATA4 IS NOT NULL AND D.DATA4 = '*') THEN 1

    WHEN (O.DATA4 IS NOT NULL AND D.DATA4 = O.DATA4) THEN 1

    ELSE 0
    END) = 1
    Sean C. Guest

  5. #5

    Default Re: Problems with CASE statement

    CREATE TABLE DB2ADMIN.SPCTEMP (
    COL1 CHAR(6)
    )
    INSERT INTO SPCTEMP VALUES ('ABC');
    INSERT INTO SPCTEMP VALUES ('XYZ');


    I think result values will be 'ABC ', 'XYZ '. Correct use with func
    like RTRIM, LEFT etc (but You lost index use) or add space to variables or
    migrate to varchar.


    Andy
    andreyp#Antispam@mapsitnA#e-vision-group.com Guest

  6. #6

    Default Re: Problems with CASE statement

    [email]dba_fla[/email] (Sean C.) wrote in message news:<2f9c2d92.0308290550.12eb580bposting.google. com>...

    In a bit of a hurry, but I'll give some suggestions below

    [...]
    >
    > select D.DATA1, D.DATA2, D.DATA3, D.DATA4, O.DATA1, O.DATA2, O.DATA3,
    > O.DATA4
    > from DATAFIX D, ORDERS O
    >
    > where O.orderid = 99999999
    > and D.SEARCHCODE = O.SEARCHCODE
    >
    > and (D.DATA1 is not null or D.DATA2 is not null or D.DATA3 is not null
    > or D.DATA4 is not null)
    Not sure, but perhaps this can be expressed as

    coalesce(D.DATA1,D.DATA2,D.DATA3,D.DATA4) is not null and

    > and (D.DATA1 = O.DATA1)
    >
    > and(
    > CASE
    >
    > WHEN (O.DATA2 IS NOT NULL AND D.DATA3 IS NOT NULL AND O.DATA4 IS
    > NOT NULL
    > AND D.DATA2 = O.DATA2 AND D.DATA3 = O.DATA3 AND D.DATA4 =
    > O.DATA4) THEN 1
    >
    I beleive the IS NOT NULL is redundant since D.DATAx = O.DATAx can
    never evaluate to true as long as any of them IS NULL (NULL = NULL ->
    false). Thus this becomes:

    CASE WHEN (
    D.DATA2 = O.DATA2 AND
    D.DATA3 = O.DATA3 AND
    D.DATA4 = O.DATA4
    ) THEN 1

    In a similar fashion you can handle the rest of the cases.

    My next step would be to try to reduce the number of cases. Check if
    some of your cases are redundant of another one. You could try
    something like:

    with data (x) as (values 'x', 'y', cast(null as char(1))) select d1.x,
    d2.x, d3.x, d4.x, o1.x, o2.x, o3.x, o4.x, case ...., case .... from
    data d1, data d2, data d3, data d4, data o1, data o2, data o3, data o4

    and play around with them to see if any one subsumes another one

    HTH
    /Lennart

    [...]
    Lennart Jonsson Guest

  7. #7

    Default Re: Problems with CASE statement

    [email]lennartkommunicera.umea.se[/email] (Lennart Jonsson) wrote in message news:<6dae7e65.0308300401.2808466bposting.google. com>...
    > [email]dba_fla[/email] (Sean C.) wrote in message news:<2f9c2d92.0308290550.12eb580bposting.google. com>...
    >
    [...]

    Perhaps I have oversimplified, but does this do the trick?

    where
    O.orderid = 99999999 and
    D.SEARCHCODE = O.SEARCHCODE and
    D.DATA1 = O.DATA1 AND
    (
    D.DATA2 = O.DATA2 OR
    D.DATA3 = O.DATA3 OR
    D.DATA4 = O.DATA4 OR
    D.DATA4 = '*'
    )

    /Lennart
    Lennart Jonsson Guest

  8. #8

    Default Re: Problems with CASE statement

    [email]dba_fla[/email] (Sean C.) wrote in message news:<2f9c2d92.0308290550.12eb580bposting.google. com>...
    > Thanks for the feedback guys.
    > Daniel, the example I posted was as simplistic as I could devise,
    > just to show the behavior I was observing. My requirements are far
    > more complex, and the query I was trying to construct is given below.
    > In a nutshell, the situation is that we have an 'Override' table
    > (DATAFIX) that is used to convert or override the values on incoming
    > order data. The criteria used to determine if an override should occur
    > is to match on 4 distinct column values coming in on the order. For
    > simplicity, I've labelled them DATA1 through DATA4. The design of the
    > DATAFIX 'override' table is that rows can exist in this table that can
    > match on all four column values, or on only one, or any combination.
    > The application must find the most 'granular' match and use that row
    > to supply override values. That is, if an order has values in all four
    > DATA columns, and there is a row on the DATAFIX table that has values
    > that match the first two DATA columns and another row that matches on
    > all four DATA columns, I want to select the latter row.
    > I thought I could implement this 'most granular to least granular'
    > prioritizing by using the 'exit on first true condition' nature of the
    > CASE statement. So, the WHEN clauses in the CASE statement below are
    > ordered in most granular to least granular order.
    >
    > Evidently, as Tokunaga T mentioned, I cannot use that method in a
    > WHERE clause. If anyone can provide a more elegant solution I would be
    > most appreciative.
    >
    > Thanks,
    > Sean
    >
    Though not so elegant, but how about this?

    SELECT D.DATA1, D.DATA2, D.DATA3, D.DATA4, O.DATA1, O.DATA2, O.DATA3, O.DATA4
    FROM ORDERS O
    , TABLE (SELECT D.DATA1, D.DATA2, D.DATA3, D.DATA4,
    CASE
    WHEN ( D.DATA2 = O.DATA2
    AND D.DATA3 = O.DATA3
    AND D.DATA4 = O.DATA4 ) THEN 1
    WHEN ( O.DATA4 IS NULL
    AND D.DATA2 = O.DATA2
    AND D.DATA3 = O.DATA3 ) THEN 2
    WHEN ( O.DATA2 IS NULL
    AND D.DATA3 = O.DATA3
    AND D.DATA4 = O.DATA4 ) THEN 3
    WHEN ( O.DATA3 IS NULL
    AND D.DATA2 = O.DATA2
    AND D.DATA4 = O.DATA4 ) THEN 4
    WHEN ( O.DATA3 IS NULL
    AND O.DATA4 IS NULL
    AND D.DATA2 = O.DATA2 ) THEN 5
    WHEN ( O.DATA2 IS NULL
    AND O.DATA4 IS NULL
    AND D.DATA3 = O.DATA3 ) THEN 6
    WHEN ( O.DATA2 IS NULL
    AND O.DATA3 IS NULL
    AND D.DATA4 = '*' ) THEN 7
    WHEN ( O.DATA2 IS NULL
    AND O.DATA3 IS NULL
    AND D.DATA4 = O.DATA4 ) THEN 8
    ELSE 9999
    END AS Priority
    FROM DATAFIX D
    WHERE D.SEARCHCODE = O.SEARCHCODE
    AND D.DATA1 = O.DATA1
    AND (
    ( D.DATA2 = O.DATA2
    AND D.DATA3 = O.DATA3
    AND D.DATA4 = O.DATA4 )
    OR ( O.DATA4 IS NULL
    AND D.DATA2 = O.DATA2
    AND D.DATA3 = O.DATA3 )
    OR ( O.DATA2 IS NULL
    AND D.DATA3 = O.DATA3
    AND D.DATA4 = O.DATA4 )
    OR ( O.DATA3 IS NULL
    AND D.DATA2 = O.DATA2
    AND D.DATA4 = O.DATA4 )
    OR ( O.DATA3 IS NULL
    AND O.DATA4 IS NULL
    AND D.DATA2 = O.DATA2 )
    OR ( O.DATA2 IS NULL
    AND O.DATA4 IS NULL
    AND D.DATA3 = O.DATA3 )
    OR ( O.DATA2 IS NULL
    AND O.DATA3 IS NULL
    AND D.DATA4 = '*' )
    OR ( O.DATA2 IS NULL
    AND O.DATA3 IS NULL
    AND D.DATA4 = O.DATA4 )
    )
    ORDER BY Priority ASC
    FETCH FRIST 1 ROW ONLY
    ) AS D
    WHERE O.orderid = 99999999
    ;

    (I think "DATAx IS NULL"s in a CASE expression in a subquery can be removed.
    But I'm not sure.)
    Tokunaga T. Guest

  9. #9

    Default Re: Problems with CASE statement

    Sean C. wrote:
     
    How can a row with col1 have values 'abc' and 'xyz' at the same time? 
    In row 1 of your table, the CASE condition evaluates to true because
    COL1= 'ABC' which matches the WHEN COL1 = 'ABC' THEN 1.

    In row 2 of your table, the CASE condition also evaluates to true. The
    first WHEN COL1 = 'ABC' fails, so it checks the next WHEN clause, WHEN
    COL1 = 'XYZ' which matches.


    Norm

    Norm Guest

  10. #10

    Default Re: Problems with CASE statement

    Norm Wong <com> wrote:
     [/ref]
     
     [/ref]

    I think you should read a bit about how a WHERE condition is evaluated in
    SQL. It looks at each row at a time and then tries to figure out if the
    condition is met. If it is, the row is added to the resulting table that
    is returned by the SELECT.

    If I understood your description correctly, then a query like this should do
    for you:

    SELECT *
    FROM spctemp
    WHERE col1 = 'ABC' OR
    ( col1 = 'XYZ' AND
    NOT EXISTS ( SELECT col1
    FROM spctemp
    WHERE col1 = 'ABC' ) )


    Now, each row in your table is yzed. If the value of col1 is 'ABC', the
    row is returned. If the value is not 'ABC' but rather 'XYZ' _and_ if there
    are no rows at all where the value of col1 is 'ABC', then the row is
    returned.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

Similar Threads

  1. sql select case statement
    By Anderson11983 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 9th, 01:50 PM
  2. List in case statement
    By lingo smith in forum Macromedia Director Basics
    Replies: 2
    Last Post: April 17th, 02:05 AM
  3. Case Statement
    By tsetliff webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 9
    Last Post: February 5th, 11:26 PM
  4. problems with case insensitive tr/// regexp
    By Dan Anderson in forum PERL Beginners
    Replies: 5
    Last Post: November 29th, 06:08 AM
  5. CASE statement and CONTAINS
    By steve in forum Macromedia Director Lingo
    Replies: 2
    Last Post: July 31st, 12:12 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139