Select all Column Names where the Row value = '1'

General
Enthusiast

Select all Column Names where the Row value = '1'

Hi,

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. 

SELECT

databasename,

tablename,

columnname

FROM dbc.columns

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

3 REPLIES
Enthusiast

Re: Select all Column Names where the Row value = '1'

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;

UPDATE TABLE

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

Enthusiast

Re: Select all Column Names where the Row value = '1'

try this to generate one file. then run that file with bteq or way you like

SELECT'

SEL DISTINCT '''||TRIM(COLUMNNAME)||''', CASE WHEN '||TRIM(COLUMNNAME)||' >1 THEN ''YES'' ELSE ''NO'' END AS IND FROM '||TRIM(TABLENAME)||' WHERE IND=''YES'''

FROM dbc.COLUMNS

WHERE DATABASENAME ='DLWORK_DB01_UA'

AND TABLENAME='FACT_CLIF_RECORDS'

Teradata Employee

Re: Select all Column Names where the Row value = '1'

UPDATE TABLEX

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.