I have the below user defined sql in Oracle Enterprise stage(which reads data from a table)
Select
Col1,
Col2,
col3,
decode(Col4,null,'A','B')
from Table1
But I am getting the below warning when i try to view the data.
I am getting the warning Input dataset could not find column Col4.
In the columns tab of Oracle Enterprise stage I have loaded the metadata from the table instead of typing,the name of column against col4 is Col4 in the columns tab.
If i remove the decode function then I am able to view the data.
The sql statement with the decode function works perfectly at the oracle sql prompt.
Any idea what is causing this error.
Thanks
Oracle enterprise stage
Moderators: chulett, rschirm, roy
Try adding an alias to the decode(Col4,null,'A','B').
Select
Col1,
Col2,
col3,
decode(Col4,null,'A','B') Col4
from Table1;
Using sqlplus to verify the name of the last column. Without the alias it'll be something like DECODE(...
1* select sysdate, decode( 1, 'A', 1, 'B', 2, 'C', 3, 4) from dual
SQL> /
SYSDATE DECODE(1,'A',1,'B',2,'C',3,4)
----------- -----------------------------
09-OCT-2008 4
Select
Col1,
Col2,
col3,
decode(Col4,null,'A','B') Col4
from Table1;
Using sqlplus to verify the name of the last column. Without the alias it'll be something like DECODE(...
1* select sysdate, decode( 1, 'A', 1, 'B', 2, 'C', 3, 4) from dual
SQL> /
SYSDATE DECODE(1,'A',1,'B',2,'C',3,4)
----------- -----------------------------
09-OCT-2008 4