Results 1 to 6 of 6
  1. #1
    Join Date
    May 2015
    Posts
    2

    Answered: Stop Early Case Termination DB2

    Question:
    How would I stop early termination of the case statement?
    In particular, what would be the correct way to write the query below?

    Description:
    When I run the following query, "myCode" is never populated with 'VAL4', 'VAL5', or 'VAL6' because Table1 allways contains either an 'F' or and 'M' and thus the case statement terminates after the GENDER is found.

    SELECT
    CASE
    WHEN TABLE1.GENDER = 'F' THEN 'VAL1'
    WHEN TABLE1.GENDER = 'M' THEN 'VAL2'
    WHEN TABLE1.AGE BETWEEN '00' and '29' THEN 'VAL3'
    WHEN TABLE1.AGE BETWEEN '30' and '39' THEN 'VAL4'
    WHEN TABLE1.AGE BETWEEN '40' and '999' THEN 'VAL5'
    END "myCode"
    .....

    Thanks.

  2. Best Answer
    Posted by mark.b

    "
    Quote Originally Posted by jspek View Post
    I would want both values returned to "myCode".
    You shouldn't do this in the relational database world.
    This would violate the First normal form."


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The CASE syntax is an exception in SQL because it is guaranteed to be evaluated in sequence, as written. If the database engine behaves according to the standard, it will always "short circuit" when a condition evaluates to true.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by jspek View Post
    How would I stop early termination of the case statement?
    In particular, what would be the correct way to write the query below?
    This depends on the result you want to get.
    For example, what is the desired result when you have the following row values:
    TABLE1.GENDER = 'M' and TABLE1.AGE BETWEEN '00' and '29'
    'VAL3' or 'VAL2'?
    Regards,
    Mark.

  5. #4
    Join Date
    May 2015
    Posts
    2
    Quote Originally Posted by mark.b View Post
    This depends on the result you want to get.
    For example, what is the desired result when you have the following row values:
    TABLE1.GENDER = 'M' and TABLE1.AGE BETWEEN '00' and '29'
    'VAL3' or 'VAL2'?
    I would want both values returned to "myCode".

    I would like the code to look something like this:
    SELECT
    CASE
    WHEN TABLE1.GENDER = 'F' THEN 'VAL1'
    WHEN TABLE1.GENDER = 'M' THEN 'VAL2'
    END "myCode"
    CASE
    WHEN TABLE1.AGE BETWEEN '00' and '29' THEN 'VAL3'
    WHEN TABLE1.AGE BETWEEN '30' and '39' THEN 'VAL4'
    WHEN TABLE1.AGE BETWEEN '40' and '999' THEN 'VAL5'
    END "myCode"
    .....

    Where the case both cases would be returned to "myCode".
    However the second case statement isn't happy with the name "myCode".

    Thank you so much for your help.

  6. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a serious "shot in the dark", but how about something like:
    Code:
    SELECT
       CASE 
          WHEN TABLE1.GENDER = 'F' THEN 'VAL1'
          WHEN TABLE1.GENDER = 'M' THEN 'VAL2'
       END || ' '
    || CASE
          WHEN TABLE1.AGE BETWEEN '00' and '29' THEN 'VAL3'
          WHEN TABLE1.AGE BETWEEN '30' and '39' THEN 'VAL4'
          WHEN TABLE1.AGE BETWEEN '40' and '999' THEN 'VAL5'
       END "myCode"
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by jspek View Post
    I would want both values returned to "myCode".
    You shouldn't do this in the relational database world.
    This would violate the First normal form.
    Regards,
    Mark.

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
  •