HELP with CASE!

dz1rfj · #1 (**permalink**) 08-02-09, 11:23

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!

n_i · #2 (**permalink**) 08-02-09, 13:12

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))

dz1rfj · #3 (**permalink**) 08-02-09, 13:41

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!

tonkuma · #4 (**permalink**) 08-02-09, 13:50

Quote:

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?

dz1rfj · #5 (**permalink**) 08-02-09, 13:52

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

dz1rfj · #6 (**permalink**) 08-02-09, 13:58

SELECT *
FROM PD12.MLTUWEC

where UWE_UW_01_CODE = 'PRP91'

Is valid, and produces expected results

Stealth_DBA · #7 (**permalink**) 08-02-09, 14:03

, 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.

dz1rfj · #8 (**permalink**) 08-02-09, 15:18

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

Stealth_DBA · #9 (**permalink**) 08-02-09, 18:34

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

DB2Plus · #10 (**permalink**) 08-02-09, 21:52

You can use this looking very simple SQL:

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech