Using ORDER BY in UNION clause in cursor
Hi Everybody,
I have one situation and i need your help guys. I have to use ORDER BY in UNION of two queries and return value in cursor.
OPEN cursor FOR
SELECT ID, DESC,SID, ITID, SID_DESC
FROM (SELECT A.ID,
A.DESC,
B.SID,
NULL AS ITID,
B.SID_DESC
FROM TABLEA A, TABLEB B
WHERE A.ID = B.ID
order by A.SORTORDER asc,B.SORTORDER)
UNION
SELECT ID, DESC, ,SID,ITID, ITID_DESCRIPTION,
FROM (SELECT A.ID,
A.DESC,
NULL AS SID,
C.ITID,
C.ITID_DESC,
FROM TABLEA A,
TABLEC C
WHERE A.ID = C.ID
order by A.SORTORDER asc,C.SORTORDER)
I SORT ORDER is column in all three tables. TABLEA has unique number for each record as sort order. TABLEB has sortorder as 1 for each id. If id is two times then its 1 and 2 for each id. TABLEC has sort order as TABLEB, 1 for each id and if id is two times then it is 1 and 2 and id id is three times, it is 1, 2, 3.
I am not getting correct sorting as i cant use order by like this in UNION. Please let me know how i can handle this.
Thank you in advance to everybody.
I will really appreciate your comments and responses.