Decode with alias name in cursor

Question

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

I have code such as:

 declare
     cl number;
     cursor c is 
        select c1.rowid,c1.col1,
               DECODE(c1.col2, 'XYZ', c1.col3, 10) cl 
          from table1 d,table2 c1 where c1.process_id=13525 and d.col3(+)=cl;
begin
  for rec in c
  loop
    dbms_output.put_line(NVL(rec.cl,'-1'));
  end loop;
end;

In this, when i will fire query by removing condition 'and d.col3(+)=cl' it will retrieve me data with the value of 'cl' . But when i assign this condtion it will not retrive data and not go in for loop of cursor.I have a matching data in d.col3.

Suppose if i will get cl as 5 then it is also present in d.col3 then it should give me data i did this because i need to remove duplicate records.Because with that single condition i will get duplicate records.Here col3 in d table is as primary key.

So i am not getting why it will not go in for loop as it gets value from query.

No there is not any error message.It does not go in loop because it does not retrive any data. — YLG, Nov 27 '14 at 08:12
possible duplicate of [Using an Alias in a WHERE clause](http://stackoverflow.com/questions/356675/using-an-alias-in-a-where-clause) — Sylvain Leroux, Nov 27 '14 at 08:15

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

You can't use alias in WHERE clause: Using an Alias in a WHERE clause

In such cases, a sub-query or a CTE might help. Something like that (untested!):

with V as (
  select c1.rowid rid, ,c1.col1, c1.process_id,
         DECODE(c1.col2, 'XYZ', c1.col3, 10) cl 
    from table2 c1)

select V.rid, V.col1, V.cl from table1 d,V 
  where V.process_id=13525 and d.col3(+)=V.cl;

edited May 23 '17 at 11:50

Community

1
1

answered Nov 27 '14 at 08:14

Sylvain Leroux

50,096
7
103
125

YLG · Answer 2 · 2014-11-30T09:08:36.297

After getting suggestion that using WITH clause, My approach to retrieve data through DECODE() using alternative table name is:

  declare
     cl number;
     cursor c is 
        with V as (
           select c1.process_id,
                  DECODE(c1.col2, 'BANDM', c1.col3, 10) cl 
             from table2 c1)
           select c1.rowid rid,c1.col1, V.cl from table1 d,V,table2 c1
            where V.process_id=1 
              and d.col3(+)=V.cl 
              and c1.col3=V.cl;

  begin
    for rec in c
    loop
     dbms_output.put_line(NVL(rec.rid,'-1'));
     dbms_output.put_line(NVL(rec.cl,'-1'));
    end loop;
  end;

Another solution without WITH clause is :

  declare
     c2 number;
     cursor c is 
          select c1.process_id
                 c1.rowid,
                 c1.col1,
                 DECODE(c1.col2, 'BANDM', c1.col3, 10) as c2
            from table1 d,
                 table2 c1
           where c1.process_id=1 
             and d.col3(+) = DECODE(c1.col2, 'BANDM', c1.col3, 10);

  begin
    for rec in c
    loop
     dbms_output.put_line(NVL(rec.rid,'-1'));
     dbms_output.put_line(NVL(rec.c2,'-1'));
    end loop;
  end;

Decode with alias name in cursor

2 Answers2