-
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.
-
First :
take this expression in the attribute list
Second :
use
order by
order by 1 -- if it's the first in attribute list.
Orca
-
I dont think it is a good practice to have columns selected more than once as well as to use position number for ordering the result. If there is any change in the program unit, the developer has to modify the order by position number at all locations of order by clause. Moderators please advice on this.
-
Originally posted by dba_akram
I dont think it is a good practice to have columns selected more than once as well as to use position number for ordering the result. If there is any change in the program unit, the developer has to modify the order by position number at all locations of order by clause. Moderators please advice on this.
ok!
Second variant:
put the SQL in a SUBSQL ordering the named coloumn outside
SELECT column1, column2, ... from ( SELECT ....
)
ORDER BY columnx
-
Originally posted by dba_akram
I dont think it is a good practice to have columns selected more than once ....
Here it is not a matter of good practice, it is the only way for you to use order by in your union-ed example. If you want to use ORDER BY with UNION, the ordered expression must be listed in all unioned subqueries. In your case you must include STATUS_CHANGE_DATE in both your queries and then order by that column. BTW, you don't need to do
Code:
ORDER BY to_date(to_char(tsc.status_change_date, 'Mon DD, YYYY'), 'Mon DD, YYYY')
when you can simply use
Code:
ORDER BY status_change_date
...... as well as to use position number for ordering the result.
I agree, but sometimes this is the only way to be able to use ORDER BY with UNION. In earlier releases that was the only option. Starting with 8i (I think) you can also use ORDER BY column_alias, where column_alias must be the same in all union-ed queries.
So depending on your release, your solution would be:
Code:
SELECT
tsc.status_change_date AS status_change_date,
.....
FROM
.....
UNION
SELECT
tsc.status_change_date AS status_change_date,
.....
FROM
.....
ORDER BY status_change_date;
or
Code:
SELECT
tsc.status_change_date AS status_change_date,
.....
FROM
.....
UNION
SELECT
tsc.status_change_date AS status_change_date,
.....
FROM
.....
ORDER BY 1;
P.S. Note that there is no need to use DISTINCT in queries with UNION - the UNION operator by itself will return you only distinct rows.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Use INLINE view to solve your problem in which rows are sorted on the date.
Example:
Table TEST
columns ID NUmber , Name Varchar2(30), LOGDATE Date.
Similar to your query:
Select a.id, a.name, a.tdate
from ( select id, name, to_char(logdate, 'MON DD YYYY') tdate
from test order by logdate) a
union
Select b.id, b.name, b.tdate
from ( select id, name, to_char(logdate, 'MON DD YYYY') tdate
from test order by logdate) b
;
Use NVL on LOGDATE if it contains NULL values.
-
BTW, what does it get you to use "distinct", since "UNION" eliminates ALL duplicates from the result set anyway?
Oracle DBA and Developer
-
Originally posted by tamilselvan
Select a.id, a.name, a.tdate
from ( select id, name, to_char(logdate, 'MON DD YYYY') tdate
from test order by logdate) a
union
Select b.id, b.name, b.tdate
from ( select id, name, to_char(logdate, 'MON DD YYYY') tdate
from test order by logdate) b
;
The result from the above query will not be sorted by logdate. It will be sorted by ID first, then by NAME and lastly by TDATE.
If you want to sort by LOGDATE, but do not LOGDATE to be includet into the resultset, then you should use:
Code:
select id, name, tdate
from
(select id, name, to_char(logdate, 'MON DD YYYY') tdate, logdate
from table1
union
select id, name, to_char(logdate, 'MON DD YYYY') tdate, logdate
from table2
)
order by logdate;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally Posted by jmodic
The result from the above query will not be sorted by logdate. It will be sorted by ID first, then by NAME and lastly by TDATE.
If you want to sort by LOGDATE, but do not LOGDATE to be includet into the resultset, then you should use:
Code:
select id, name, tdate
from
(select id, name, to_char(logdate, 'MON DD YYYY') tdate, logdate
from table1
union
select id, name, to_char(logdate, 'MON DD YYYY') tdate, logdate
from table2
)
order by logdate;
Welcome back to the forum..
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Thanks to all. I should kick my ass to have asked this question. Because distinct was my main catch in it and as you guys made me realise the silly thing that UNION itself eliminates duplicates. thanks all.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|