ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Question:I have a problem
because in a PL/SQL procedure I need to control that the user press the link,
the PL/SQL perform a different order. Then I was trying the following, but
I get the following error code: in compile...
PL/SQL: ORA-01785: ORDER BY item must be the number of a SELECT-list expression:

cursor M_datos_x_NumeroExp is
select a.numero_cliente, a.nombre_expediente, a.codigo_jur, a.ibc,
a.numero_expediente,
a.status, 'SOC' tipo
from clientes b, sociedades a
where a.numero_cliente = b.numero_cliente
and b.numero_cliente != '9036'
and a.numero_expediente = nombre_expediente_ww
and (gestion_cobro like g_cobro_w or a.numero_cliente='8000')
and a.codigo_jur like upper('%'||locacion_w||'%')
and nvl(b.codigo_origen,'%') like origen
and ((b.orac_man = v_orac_man or b.orac_man = 3) or v_orac_man=3)
and (a.orac_man = v_orac_man or v_orac_man=3)
union
select a.numero_cliente, a.nombre_expediente, a.codigo_jur, null,
a.numero_expediente,
a.status_fideicomiso,'FID'
from clientes b, fideicomisos a
where a.numero_cliente = b.numero_cliente
and b.numero_cliente != '9036'
and a.numero_expediente = nombre_expediente_ww
and (gestion_cobro like g_cobro_w or a.numero_cliente='8000' or gestion_cobro is
null)
and a.codigo_jur like upper('%'||locacion_w||'%')
and nvl(b.codigo_origen,'%') like origen
and ((b.orac_man = v_orac_man or b.orac_man = 3) or v_orac_man=3)
and (a.orac_man = v_orac_man or v_orac_man=3)
order by
decode(ordenador,1,a.numero_cliente,2,a.nombre_expediente,3,a.codigo_jur,5,a.numero_expediente);

Answer: You can re move the "PL/SQL:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression" error
by re-writing the SQL as follows:

cursor M_datos_x_NumeroExp is
select * from (
select a.numero_cliente, a.nombre_expediente, a.codigo_jur, a.ibc,
a.numero_expediente,
a.status, 'SOC' tipo
from clientes b, sociedades a
where a.numero_cliente = b.numero_cliente
and b.numero_cliente != '9036'
and a.numero_expediente = nombre_expediente_ww
and (gestion_cobro like g_cobro_w or a.numero_cliente='8000')
and a.codigo_jur like upper('%'||locacion_w||'%')
and nvl(b.codigo_origen,'%') like origen
and ((b.orac_man = v_orac_man or b.orac_man = 3) or v_orac_man=3)
and (a.orac_man = v_orac_man or v_orac_man=3)
union
select a.numero_cliente, a.nombre_expediente, a.codigo_jur, null,
a.numero_expediente,
a.status_fideicomiso,'FID'
from clientes b, fideicomisos a
where a.numero_cliente = b.numero_cliente
and b.numero_cliente != '9036'
and a.numero_expediente = nombre_expediente_ww
and (gestion_cobro like g_cobro_w or a.numero_cliente='8000' or
gestion_cobro is null)
and a.codigo_jur like upper('%'||locacion_w||'%')
and nvl(b.codigo_origen,'%') like origen
and ((b.orac_man = v_orac_man or b.orac_man = 3) or v_orac_man=3)
and (a.orac_man = v_orac_man or v_orac_man=3)
)
order by
decode (ordenador,1,a.numero_cliente,2,a.nombre_expediente,
3,a.codigo_jur,5,a.numero_expediente);

��

Burleson is the American Team

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail:
and include the URL for the page.