Pleas help with variables and DECODEs

[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 these commands. It would save me thousands of lines of code if I can use a variable for the column name, and change the variable before I call the curosr, hence reusing it.)


I asked this in the SQL forum, and they suggested I try here, as DBMS_SQL was probably my best option. But how do I use that?!

Cheers, ben

 

[karluk]

Hi Ben, I was the one who suggested you try the Oracle 8 forum. Actually, though, dbms_sql should only be your choice if you aren't on Oracle 8i. The newer, so-called Native Dynamic SQL is a much better choice if it's available on your system. A good thread to read as background is thread186-97963. Please consult the references pointed to by that thread.

To get you started, suppose you want to create an "execute immediate" statement to run your decode. I think your code would look something like the following

DECLARE
sql_stmt VARCHAR2(1000);
v_use VARCHAR2(30) := 'saq2a';
BEGIN
sql_stmt := 'SELECT SUM(DECODE(' || v_use || ',1,1,0)) from your_table';
EXECUTE IMMEDIATE sql_stmt;
END;

 

[benbenben]

Thanks Karluk...

You have no idea how much that helped :-D !!

 

[benbenben]

I guess it's going to have to be DBMS_SQL, as I'm using version 8.0.5.1.0 ... Damn!! And I thought I was onto a winner with that one...

Thanks anyway ;-)