Variables within DECODE

[benbenben]

Can anyone give any advice on using variable within DECODE?

If I use:

SUM(DECODE(saq2a, 1, 1, 0)) "1"

...with saq2a being the column name, it works. If I use:

v_use := 'saq2a';

SUM(DECODE(v_use, 1, 1, 0)) "1"

...it doesn't work because it gives a comparison error. If I then wrap the 1 in quotes ('1'), it works, but gives the wrong answer because it compares it to the word saq2a as opposed to the column name (and the value in the table).

This is really getting on my nerves. Does anyone know how I can use a variable for the column name.

(Just for background info, I have a cursor that uses this command. It would save me thousands of lines of code if I can use a variable for the column name, because I can reuse the cursor.)

 

[tlbroadbent]

Which RDMS are you using? Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[karluk]

"Decode" is an Oracle function, so that's probably the DBMS.

In general, if you have to dynamically assemble and execute an SQL statement, you should use the DBMS_SQL package, or the newer "execute immediate".

This question is specific to Oracle, so it would be better to post any additional questions to the Oracle 8 forum.