order by with Union All [message #254793] |
Sat, 28 July 2007 05:56 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
asgharmayo
Messages: 5 Registered: March 2006 Location: Faisalabad
|
Junior Member |
|
|
i have report that has two queries with union all
i want to order by doc_date. i use order by but no order by date.
MY QUERY IS
SELECT DOC_DATE,DOC_NO,CONTRACTNO,PLANNO,VCODE,COLOUR,BRAND,YARN_WT_OUT,FABRIC_WT_IN,ITEM
FROM(
select m.outw_date DOC_DATE,m.outw_no DOC_NO ,m.contract_no CONTRACTNO,m.plan_no PLANNO,m.vend_code VCODE
,d.color COLOUR,d.brand BRAND,d.weight YARN_WT_OUT,0 FABRIC_WT_IN
,item_desc ITEM
from outw_master m,outw_detail d,ITEM I
where m.OUTW_NO=d.outw_no and d.ITEM_DTL_CODE=i.item_code and m.rec_type='KNT'
UNION ALL
SELECT M.inw_date DOC_DATE,m.inw_no DOC_NO,M.contract_no CONTRACTNO,m.plan_no PLANNO,m.VEND_CODE VCODE,
d.color COLOUR,d.brand BRAND, 0 YARN_WT_OUT,d.knt_fabric_kg FABRIC_WT_IN ,f.fabric_type item
from inw_master m,inw_detail d ,tblyarnfabrictype f
where m.inw_no=d.inw_no and d.FABRIC_CODE=f.fabric_code and m.rec_type='KNT'
) T
WHERE PLANNO=:P_PLANNO
ORDER BY DOC_DATE
|
|
|
Re: order by with Union All [message #254801 is a reply to message #254793] |
Sat, 28 July 2007 07:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Please, copy-paste output of the SQL*Plus session. Run this statement:SELECT doc_date
FROM (SELECT outw_date doc_date
FROM outw_master
WHERE ROWNUM < 6
UNION
SELECT inw_date doc_date
FROM inw_master
WHERE ROWNUM < 6
)
ORDER BY doc_date;
Also, what are datatypes of 'outw_master.outw_date' and 'inw_master.inw_date' columns?
Oh, yes - next time, please, format code using [code] tags. This message is nothing but a mess.
[Updated on: Sat, 28 July 2007 07:25] Report message to a moderator
|
|
|