Hi All,
am executing the following query with the union clause in my sqlplus or even pl/sql block.
SELECT distinct t.tdd_id,
ts.abbreviation,
po.last_name||', '||po.first_name,
c.customer_name,
to_char(tsc.status_change_date, 'Mon DD, YYYY')
FROM tdd t,
tdd_status ts,
person p,
person po,
telecom_authority ta,
telecom_authority ta1,
customer c,
tdd_status_change tsc,
servicing_type st,
service s,
tdd_person tp,
product prod,
role r
WHERE t.tdd_id = tsc.tdd_id
AND tsc.tdd_status_id = ts.tdd_status_id
AND c.customer_id = t.customer_id
AND t.servicing_type_id = st.servicing_type_id
AND tsc.tdd_status_change_id = (select max(tdd_status_change_id)
from tdd_status_change where tdd_id = t.tdd_id)
AND po.person_id = t.tdd_owner
AND p.person_id = p_person_id
AND tp.person_id = p.person_id
AND t.tdd_id = tp.tdd_id
AND st.product_id = prod.product_id
AND rownum < 502
UNION
SELECT distinct t.tdd_id,
ts.abbreviation,
po.last_name||', '||po.first_name,
c.customer_name,
replace(t.tdd_description,'
',' '),
t.tdd_number,
to_char(tsc.status_change_date, 'Mon DD, YYYY')
FROM tdd t,
tdd_status ts,
person p,
person po,
telecom_authority ta,
telecom_authority ta1,
customer c,
tdd_status_change tsc,
servicing_type st,
service s,
tdd_person tp,
product prod,
role r
WHERE t.tdd_id = tsc.tdd_id
AND c.customer_id = t.customer_id
AND t.servicing_type_id = st.servicing_type_id
AND tsc.tdd_status_change_id = (select max(tdd_status_change_id)
from tdd_status_change where tdd_id = t.tdd_id)
AND po.person_id = t.tdd_owner
AND p.person_id = p_person_id
AND tp.person_id = p.person_id
AND t.tdd_id = tp.tdd_id
AND st.product_id = prod.product_id
AND rownum < 502
ORDER BY to_date(to_char(tsc.status_change_date, 'Mon DD, YYYY'), 'Mon DD, YYYY');
I get an error message like this
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
If I execute the query separately without the union clause just with the order by it works like the way I want.
Can anyone of you please let me know the way to overcome this situation. Iam expecting the moderators to show me the way to get out of it.
Thanks in advance.