I need to locate the Columns in my table where the value is Greater than 1 or some other condition.
I've found I can find the Columns with the below code but I need someting like a 'ColumnValue' function to apply a condition.
WHERE databasename = 'DB'
AND tablename = 'TBL'
AND columnname = 'X'
AND COLUMNVALUE > '1' -- This is what I need to use or something similar
Thanks for any help
I don't think the above is a possibility so I have another question for the same purpose.
I'll need to write Update statements to populate a new field I have created & called 'Z'
There are 8 fields with various values next to Field 'Z'
They are 'A','B','C','D','E','F','G','H' plus the new field 'Z'
My Update statements need to update any combination of the 8 'A' - 'H' fields where their value is > 1.
To explain an example, I want the 'Z' field to update to 'A/B' if the Row values in Column 'A' & 'B' are > 1 & the others are '0'
An example statement;
SET Z = 'A/B'
WHERE A > 0
AND B > 0
AND C = 0
AND D = 0
AND E = 0
AND F = 0
AND G = 0
AND H = 0
Unfortunately, this means dozens & dozens of Update statements need to be written to cope with all combinations.
There must be a better way??
try this to generate one file. then run that file with bteq or way you like
SEL DISTINCT '''||TRIM(COLUMNNAME)||''', CASE WHEN '||TRIM(COLUMNNAME)||' >1 THEN ''YES'' ELSE ''NO'' END AS IND FROM '||TRIM(TABLENAME)||' WHERE IND=''YES'''
WHERE DATABASENAME ='DLWORK_DB01_UA'
SET Z = TRIM(TRAILING '/' FROM
(CASE WHEN A>0 THEN 'A/' ELSE '' END||
CASE WHEN B>0 THEN 'B/' ELSE '' END||
CASE WHEN C>0 THEN 'C/' ELSE '' END||
CASE WHEN D>0 THEN 'D/' ELSE '' END||
CASE WHEN E>0 THEN 'E/' ELSE '' END||
CASE WHEN F>0 THEN 'F/' ELSE '' END ||
CASE WHEN G>0 THEN 'G' ELSE '' END)
But why do UPDATE, just put the CASE in your SELECT.