plsql - Decode with alias name in cursor -


i want use decode function in cursor alias names avoid column ambiguity used below approach.

i have code such as:

 declare      cl number;      cursor c          select c1.rowid,c1.col1,                decode(c1.col2, 'xyz', c1.col3, 10) cl            table1 d,table2 c1 c1.process_id=13525 , d.col3(+)=cl; begin   rec in c   loop     dbms_output.put_line(nvl(rec.cl,'-1'));   end loop; end; 

in this, when fire query removing condition 'and d.col3(+)=cl' retrieve me data value of 'cl' . when assign condtion not retrive data , not go in loop of cursor.i have matching data in d.col3.

suppose if cl 5 present in d.col3 should give me data did because need remove duplicate records.because single condition duplicate records.here col3 in d table primary key.

so not getting why not go in loop gets value query.

you can't use alias in where clause: using alias in clause

in such cases, sub-query or cte might help. like (untested!):

with v (   select c1.rowid rid, ,c1.col1, c1.process_id,          decode(c1.col2, 'xyz', c1.col3, 10) cl      table2 c1)  select v.rid, v.col1, v.cl table1 d,v    v.process_id=13525 , d.col3(+)=v.cl; 

Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -