Results 1 to 10 of 10

Thread: HELP with CASE!

  1. #1
    Join Date
    Apr 2009
    Posts
    12

    Unanswered: HELP with CASE!

    I have a DB2 Databse. I am trying to use a query builder called AQT, or , Advanced query tool. The tech support staff at AQT claims that CASE works with their software and DB2. As I am NOT a developer, and normally work in MS access for ease of use, I am stuck.

    If I take examples from books, google, etc, I am using this format.

    SELECT *
    FROM PD12.MLTUWEC SET 'NEWCOLUMN' = CASE WHEN

    {UWE_UW_01_CODE} = 'PRP91' THEN 'PRP91_01'
    {UWE_UW_02_CODE} = 'PRP91' THEN 'PRP91_02'
    {UWE_UW_03_CODE} = 'PRP91' THEN 'PRP91_03'
    {UWE_UW_04_CODE} = 'PRP91' THEN 'PRP91_04'

    ELSE NULL
    END


    If I click FORMAT in the AQT program, it converts it to:

    SELECT *
    FROM PD12.MLTUWEC SET 'NEWCOLUMN' = CASE WHEN {UWE_UW_01_CODE} = 'PRP91' THEN 'PRP91_01' {UWE_UW_02_CODE} = 'PRP91' THEN 'PRP91_02' {UWE_UW_03_CODE} = 'PRP91' THEN 'PRP91_03' {UWE_UW_04_CODE} = 'PRP91' THEN 'PRP91_04' ELSE NULL END

    Any ideas would be mnost apraiciated!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    And your question is?..

    By looking at the query text I can think of at least three problems:
    - SET 'NEWCOLUMN': column names should not be enclosed in single quotes; it makes them string literals, not column names;
    - {UWE_UW_01_CODE}: ditto for curly braces; these are not valid delimiters in DB2 as far as I know;
    - ELSE NULL END: you may need to cast NULL to the target column data type explicitly, e.g. CAST (NULL AS VARCHAR(20))
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2009
    Posts
    12
    Nick - Sorry- The question is why is this code notworking...

    I took your suggestions, below, and code still fails...

    SELECT *
    FROM PD12.MLTUWEC SET NEWCOLUMN = CASE WHEN UWE_UW_01_CODE = 'PRP91' THEN 'PRP91_01' UWE_UW_02_CODE = 'PRP91' THEN 'PRP91_02' UWE_UW_03_CODE = 'PRP91' THEN 'PRP91_03' UWE_UW_04_CODE = 'PRP91' THEN 'PRP91_04' ELSE 'blank' END

    Thanks for all the suggestions!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT *
    FROM PD12.MLTUWEC SET NEWCOLUMN = CASE ... END
    Is this DB2 SQL query?
    There is no such syntax "SELECT ... FROM ... SET ..." in DB2 SQL.

    And, what error message(s) did you got?

  5. #5
    Join Date
    Apr 2009
    Posts
    12

    SQL expert with ODBC to DB2 table needed

    Well,

    I noticed I had only 1 WHEN statement, but it is still not working.

    I tried this...still no dice

    Select *


    NEW_column = CASE
    WHEN UWE_UW_01_CODE = 'PRP91' THEN 'PRP91_01'
    WHEN UWE_UW_02_CODE = 'PRP91' THEN 'PRP91_02'
    WHEN UWE_UW_03_CODE = 'PRP91' THEN 'PRP91_03'
    WHEN UWE_UW_04_CODE = 'PRP91' THEN 'PRP91_04'


    ELSE 'blank'
    END,



    from PD12.MLTUWEC

  6. #6
    Join Date
    Apr 2009
    Posts
    12

    Help With Case!

    SELECT *
    FROM PD12.MLTUWEC

    where UWE_UW_01_CODE = 'PRP91'

    Is valid, and produces expected results

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    , if you are tying to set all rows in the table for column to to specific values based on another set of columns using the CASE express, this could work:
    Code:
    UPDATE PD12.MLTUWEC 
    SET NEWCOLUMN = CASE 
                      WHEN UWE_UW_01_CODE = 'PRP91' THEN 'PRP91_01' 
                      WHEN UWE_UW_02_CODE = 'PRP91' THEN 'PRP91_02'
                      WHEN UWE_UW_03_CODE = 'PRP91' THEN 'PRP91_03'
                      WHEN UWE_UW_04_CODE = 'PRP91' THEN 'PRP91_04' 
                                                    ELSE ' ' 
                    END
    If you just want to do a SELECT to return the values, then this will work:
    Code:
    SELECT COL1
         , COL2
         ,CASE 
            WHEN UWE_UW_01_CODE = 'PRP91' THEN 'PRP91_01' 
            WHEN UWE_UW_02_CODE = 'PRP91' THEN 'PRP91_02'
            WHEN UWE_UW_03_CODE = 'PRP91' THEN 'PRP91_03'
            WHEN UWE_UW_04_CODE = 'PRP91' THEN 'PRP91_04' 
                                          ELSE ' ' 
          END
    FROM PD12.MLTUWEC
    NOTE: I used a space in the ELSE. If you want the text 'blank' instead, you can leave that the way it was.

  8. #8
    Join Date
    Apr 2009
    Posts
    12
    Stealth DBA - ALMOST there. I actually was able to get some results, closest I have seen yet!

    I used your function:

    SELECT COL1
    , COL2
    ,CASE
    WHEN UWE_UW_01_CODE = 'PRP91' THEN 'PRP91_01'
    WHEN UWE_UW_02_CODE = 'PRP91' THEN 'PRP91_02'
    WHEN UWE_UW_03_CODE = 'PRP91' THEN 'PRP91_03'
    WHEN UWE_UW_04_CODE = 'PRP91' THEN 'PRP91_04'
    ELSE ' '
    END
    FROM PD12.MLTUWEC

    and returned the 2 cloumns i wanted, but instead if it returning all rows where a columnd had PRP91, it returned other values as well.

    Is there a way that it can search all 55 columns of the table for PRP91 and return ONLY records where 1 of the 55 columns have a PRP91 in it?

    I will buy you a beer, six pack (if you drink) actually I am happy to throw you something via PAYPAL if we can get it working. At least there is some progress, and thanks for your help!
    Tnx - Brian

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Brian, since I am not sure exactly what you want for output (all columns or just the column that contains the value you are looking for), here are a few variations:
    This is a simple Select all columns where value in at least one of the 55 columns. FETCH FIRST 1 ROW ONLY will only return 1 row.
    Code:
    SELECT *
    FROM PD12.MLTUWEC
    WHERE 'PRP91' IN(col1, col2, col3, etc., col55)
    FETCH FIRST 1 ROW ONLY
    This uses the CASE expression in the SELECT to both return just the column that has the value and a CASE in the WHERE that will evaluate to TRUE if one of the columns contains the value (1=1) or FASLE if none of them do (1=0):
    Code:
    SELECT COL1
         , COL2
         ,CASE 
            WHEN UWE_UW_01_CODE = 'PRP91' THEN 'PRP91_01' 
            WHEN UWE_UW_02_CODE = 'PRP91' THEN 'PRP91_02'
            WHEN UWE_UW_03_CODE = 'PRP91' THEN 'PRP91_03'
            WHEN UWE_UW_04_CODE = 'PRP91' THEN 'PRP91_04' 
                                          ELSE ' ' 
          END
    FROM PD12.MLTUWEC
    WHERE 1 = CASE 
            WHEN UWE_UW_01_CODE = 'PRP91' THEN 1
            WHEN UWE_UW_02_CODE = 'PRP91' THEN 1
            WHEN UWE_UW_03_CODE = 'PRP91' THEN 1
            WHEN UWE_UW_04_CODE = 'PRP91' THEN 1 
                                          ELSE 0
              END
    FETCH FIRST 1 ROW ONLY
    This uses the Select CASE to return only the column with the value but uses the IN parameter in the WHERE clause (NOTE: IF the value is in multiple columns this may not work quite as expected. I am not sure about this without testing):
    Code:
    SELECT COL1
         , COL2
         ,CASE 
            WHEN UWE_UW_01_CODE = 'PRP91' THEN 'PRP91_01' 
            WHEN UWE_UW_02_CODE = 'PRP91' THEN 'PRP91_02'
            WHEN UWE_UW_03_CODE = 'PRP91' THEN 'PRP91_03'
            WHEN UWE_UW_04_CODE = 'PRP91' THEN 'PRP91_04' 
                                          ELSE ' ' 
          END
    FROM PD12.MLTUWEC
    WHERE 'PRP91' IN(col1, col2, col3, etc., col55)
    FETCH FIRST 1 ROW ONLY

  10. #10
    Join Date
    Jul 2009
    Posts
    150
    You can use this looking very simple SQL:

    select
    case
    WHEN UWE_UW_XX_CODE = 'PRP91' THEN 'PRP91_' || strip(digit(i), l, '0')
    else nullif(' ', ' ')
    end as clmn_found
    From
    (
    select 1 as i, UWE_UW_01_CODE as UWE_UW_XX_CODE
    FROM PD12.MLTUWEC
    where UWE_UW_01_CODE = 'PRP91'
    union all
    select 2 as i, UWE_UW_02_CODE as UWE_UW_XX_CODE
    FROM PD12.MLTUWEC
    where UWE_UW_02_CODE = 'PRP91'
    union all
    select 3 as i, UWE_UW_03_CODE as UWE_UW_XX_CODE
    FROM PD12.MLTUWEC
    where UWE_UW_03_CODE = 'PRP91'
    union all
    select 4 as i, UWE_UW_04_CODE as UWE_UW_XX_CODE
    FROM PD12.MLTUWEC
    where UWE_UW_04_CODE = 'PRP91'
    .........
    union all
    select 154 as i, UWE_UW_154_CODE as UWE_UW_XX_CODE
    FROM PD12.MLTUWEC
    where UWE_UW_154_CODE = 'PRP91'
    union all
    select 155 as i, UWE_UW_155_CODE as UWE_UW_XX_CODE
    FROM PD12.MLTUWEC
    where UWE_UW_155_CODE = 'PRP91'
    ) xx
    Kara S.
    Last edited by DB2Plus; 08-02-09 at 23:01.

Posting Permissions

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