Home » SQL & PL/SQL » SQL & PL/SQL » UNION ALL
UNION ALL [message #212348] Thu, 04 January 2007 20:02 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi All,

I have the following two sql which uses union all, but the result is have duplicate rows also I have to have only one row at a time. Please help me to ignore the duplicate rows.

SELECT distinct A.CONT_NO,b.MODEL,'' "INV NO",'' "INV DATE"
FROM CSS_CONT_HDR A,CSS_CONT_MODELS b
WHERE A.STATUS != 'X' AND A.REF_NO=b.CTHD_REF_NO
AND b.CODE IN ('NHR85EU3ES','NPR85LU4Y','NPR85LU5Y','NPR75L')
GROUP BY A.CONT_NO,b.model
union all
SELECT distinct A.CONT_NO,b.MODEL,c.BIHD_DOC_NO "INV NO",TO_CHAR(c.BIHD_DOC_DATE,'DD-MON-YY') "INV DATE"
FROM CSS_CONT_HDR A,CSS_CONT_MODELS b,css_car_records c
WHERE A.STATUS != 'X' AND A.REF_NO=b.CTHD_REF_NO
AND b.CODE IN ('NHR85EU3ES','NPR85LU4Y','NPR85LU5Y','NPR75L')
and a.CONT_NO=c.CTHD_CONT_NO
GROUP BY A.CONT_NO,B.CODE,C.bihd_DOC_NO,C.bihd_DOC_DATE
order by 1;

my output is like this

CONT NO MODEL INV NO INV DATE
TCTU602146 NPR85LU5Y
TCTU602146 NPR85LU5Y TITU700021 03-Jan-07
TCTU602210 NPR85LU5Y
TCTU602219 NPR85LU4Y
TCTU602219 NPR85LU4Y TITU602282 14-Dec-06
TCTU602226 NHR85EU3ES
TCTU602227 NHR85EU3ES
TCTU602229 NPR85LU4Y
TCTU602229 NPR85LU4Y TITU602345 26-Dec-06
TCTU602234 NHR85EU3ES
TCTU602236 NPR85LU4Y
TCTU602236 NPR85LU4Y TITU700003 03-Jan-07
TCTU602237 NPR85LU4Y
TCTU602237 NPR85LU4Y TITU602330 21-Dec-06
TCTU602238 NPR85LU4Y
TCTU602238 NPR85LU4Y TITU700020 03-Jan-07
TCTU602241 NPR85LU5Y
TCTU602241 NPR85LU5Y TITU602346 26-Dec-06
TCTU602243 NHR85EU3ES
TCTU602251 NPR85LU4Y
TCTU602251 NPR85LU4Y TITU602303 18-Dec-06
TCTU602252 NPR85LU4Y
TCTU602253 NPR85LU5Y
TCTU602255 NHR85EU3ES
TCTU602259 NHR85EU3ES
TCTU602260 NHR85EU3ES
TCTU602282 NPR85LU5Y
TCTU602285 NPR75L
TCTU602289 NPR85LU5Y
TCTU602289 NPR85LU5Y TITU602337 22-Dec-06
TCTU602296 NPR85LU4Y
TCTU602296 NPR85LU4Y TITU700008 03-Jan-07

Re: UNION ALL [message #212355 is a reply to message #212348] Thu, 04 January 2007 20:45 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
UNION ALL will provide duplicates, you should simply use UNION.
Previous Topic: Can I define Date Format in Create Table Syntax (merged)
Next Topic: Member Functions !?! A Definition Please !!
Goto Forum:
  


Current Time: Mon Dec 05 05:09:36 CST 2016

Total time taken to generate the page: 0.15118 seconds