union+order by

[edli]

hi,

I had this code :

create or replace procedure
proc_name( v_idfolder tblfolder.idfolder%type,
v_target tblhtmlfile.idcustomergroup%type,
v_recordset out types.cursor_type)
is
begin
open v_recordset for
select tf.idfolder,tf.name as Item,tf.orderlist,1 as isfolder,
0 as isroot,thf.idhtmlfile
from tblfolder tf,tblhtmlfile thf
where tf.idfolder=thf.idfolder
and tf.idparent=v_idfolder
and tf.deleted=0
and tf.published=1
and thf.isroot=1
and thf.idcustomergroup=v_target
and thf.deleted=0
UNION ALL
select thf.idfolder,thf.htmltitle as Item,thf.orderlist,
0 as isfolder,thf.isroot,thf.idhtmlfile
from tblhtmlfile thf
where thf.isroot=0
and thf.idfolder=v_idfolder
and thf.published=1
and thf.deleted=0
and thf.isoverthemenu=1
and thf.idcustomergroup=v_target
order by t1.orderlist;
end;

and It gives the following error:
ERROR at line 27:
ORA-06550: line 27, column 14:
PLS-00201: identifier 'THF.IDFOLDER' must be declared
ORA-06550: line 7, column 2:
PL/SQL: SQL Statement ignored


the alternative solution I found was
select * from
( ) t1
order by t1.orderlist;

But Can anyone explain why it doesn't work directly with the order by???
Thanks in advance

[TomazZ]

Originally posted by edli
PLS-00201: identifier 'THF.IDFOLDER' must be declared

desc tblhtmlfile

[edli]

well I really have written it wrong but its not that the problem because the field belongs to the table.

create or replace procedure
proc_name( v_idfolder tblfolder.idfolder%type,
v_target tblhtmlfile.idcustomergroup%type,
v_recordset out types.cursor_type)
is
begin
open v_recordset for
select tf.idfolder,tf.name as Item,tf.orderlist,1 as isfolder,
0 as isroot,thf.idhtmlfile
from tblfolder tf,tblhtmlfile thf
where tf.idfolder=thf.idfolder
and tf.idparent=v_idfolder
and tf.deleted=0
and tf.published=1
and thf.isroot=1
and thf.idcustomergroup=v_target
and thf.deleted=0
UNION ALL
select thf.idfolder,thf.htmltitle as Item,thf.orderlist,
0 as isfolder,thf.isroot,thf.idhtmlfile
from tblhtmlfile thf
where thf.isroot=0
and thf.idfolder=v_idfolder
and thf.published=1
and thf.deleted=0
and thf.isoverthemenu=1
and thf.idcustomergroup=v_target
order by tf.orderlist;
end;

and It gives the following error:
ERROR at line 27:
ORA-06550: line 27, column 14:
PLS-00201: identifier 'TF.IDFOLDER' must be declared
ORA-06550: line 7, column 2:
PL/SQL: SQL Statement ignored

[edli]

TF.ORDERLIST must be declared

[AOrehek]

Try this

create or replace procedure
proc_name( v_idfolder tblfolder.idfolder%type,
v_target tblhtmlfile.idcustomergroup%type,
v_recordset out types.cursor_type)
is
begin
open v_recordset for
select tf.idfolder,tf.name as Item,tf.orderlist,1 as isfolder,
0 as isroot,thf.idhtmlfile
from tblfolder tf,tblhtmlfile thf
where tf.idfolder=thf.idfolder
and tf.idparent=v_idfolder
and tf.deleted=0
and tf.published=1
and thf.isroot=1
and thf.idcustomergroup=v_target
and thf.deleted=0
UNION ALL
select thf.idfolder,thf.htmltitle as Item,thf.orderlist,
0 as isfolder,thf.isroot,thf.idhtmlfile
from tblhtmlfile thf
where thf.isroot=0
and thf.idfolder=v_idfolder
and thf.published=1
and thf.deleted=0
and thf.isoverthemenu=1
and thf.idcustomergroup=v_target
order by orderlist;
end;

[edli]

Aready tried that, it wants it to specifie to which table it belongs.