Home » Developer & Programmer » Reports & Discoverer » order by with Union All
icon10.gif  order by with Union All [message #254793] Sat, 28 July 2007 05:56 Go to next message
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
Littlefoot
Messages: 21823
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

Previous Topic: How to put the double line
Next Topic: Hi-Adding a parameter As of Date
Goto Forum:
  


Current Time: Fri Dec 06 01:58:59 CST 2024