Home » SQL & PL/SQL » SQL & PL/SQL » Date field causing duplicate rows in output
Date field causing duplicate rows in output [message #243006] Tue, 05 June 2007 13:06 Go to next message
concorde800
Messages: 52
Registered: May 2007
Member
select /*+ parallel (a,6) */
a.key_sls_ordr_fact_id, a.bas_ordr_entry_qty, a.eta_dt, b.ds_eta_date, MAX(B.ds_eta_date), sum(a.bas_ordr_entry_qty)
from
OPS_RPT.RPT_SLS_ORDR_FACT a, ops_rpt.rpt_ds_eta_date b
where a.so_hdr_no = b.key_drs_so_no (+)
  and   a.so_ln_itm_no = b.key_drs_so_ln (+)
  and  a.so_hdr_no = '0810410391'
  and  a.so_ln_itm_no = '000010'
group by a.key_sls_ordr_fact_id, a.bas_ordr_entry_qty, a.eta_dt, b.ds_eta_date


The above query returns the output below

 KEY_SLS_ORDR_FACT_ID     BAS_ORDR_ENTRY_QTY    ETA_DT     DS_ETA_DATE  MAX(B.DS_ETA_DATE)     SUM(A.BAS_ORDR_ENTRY_QTY)      

 
----------------------     --------------- ------------  --------------   ---------- 		-------------------- 
 8104103910000100001         680              4/18/2007     4/18/2007      4/18/2007                  680 
 8104103910000100001         680              4/18/2007     4/23/2007      4/23/2007                 1360 
 8104103910000100002        1055              4/18/2007     4/18/2007      4/18/2007                 1055 
 8104103910000100002        1055              4/18/2007     4/23/2007      4/23/2007                 2110 
 8104103910000100003        1297              4/18/2007     4/18/2007      4/18/2007                 1297
 8104103910000100003        1297              4/18/2007     4/23/2007      4/18/2007                 1297  

6 row(s) retrieved


The output is showing duplicate row for each KEY_SLS_ORDR_FACT_ID and the problem is in the JOIN condition.

I need to put an extra filter on the where clause to restrict it further by the b.ds_eta_date and this would take care of the duplicates being put out. The max (b.ds_eta_date) will not work as group functions are not allowed in the where clause.

How do I do it?
Re: Date field causing duplicate rows in output [message #243012 is a reply to message #243006] Tue, 05 June 2007 13:33 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I'm not sure what you are trying to do, could you please post the required output? (calculate it yourself I mean, show us what you want the result to be)
Re: Date field causing duplicate rows in output [message #243017 is a reply to message #243012] Tue, 05 June 2007 13:43 Go to previous messageGo to next message
vggraj
Messages: 3
Registered: May 2007
Junior Member
If u think only date field causing the issue use like this TRUNC(a.eta_dt)


Rgds
Govind
Re: Date field causing duplicate rows in output [message #243018 is a reply to message #243012] Tue, 05 June 2007 13:43 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
SUM(A.BAS_ORDR_ENTRY_QTY)

8104103910000100001         680              4/18/2007     4/18/2007      4/18/2007                  680 
 8104103910000100001         680              4/18/2007     4/23/2007      4/23/2007                 1360 
 8104103910000100002        1055              4/18/2007     4/18/2007      4/18/2007                 1055 
 8104103910000100002        1055              4/18/2007     4/23/2007      4/23/2007                 2110 

Why this column doesnt produce the same result then.

[Updated on: Tue, 05 June 2007 13:46]

Report message to a moderator

Re: Date field causing duplicate rows in output [message #243027 is a reply to message #243018] Tue, 05 June 2007 14:03 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
My desired output is shown below. I need to exclude the doubling of r KEY_SLS_ORDR_FACT_ID and doubling of BAS_ORDR_ENTRY_QTY (680 is doubled to 1360).

By adding an extra where clause
and  b.ds_eta_date = '18-APR-2007'
I get the below output.

The trunc suggestion does not work and it excludes some records from the desired result below.

 KEY_SLS_ORDR_FACT_ID     BAS_ORDR_ENTRY_QTY    ETA_DT     DS_ETA_DATE  MAX(B.DS_ETA_DATE)     SUM(A.BAS_ORDR_ENTRY_QTY)      

 
----------------------     --------------- ------------  --------------   ---------- 		-------------------- 
 8104103910000100001         680              4/18/2007     4/18/2007      4/18/2007                 680
 8104103910000100002        1055              4/18/2007     4/18/2007      4/18/2007                 1055  
 8104103910000100003        1297              4/18/2007     4/18/2007      4/18/2007                 1297
Re: Date field causing duplicate rows in output [message #243081 is a reply to message #243027] Wed, 06 June 2007 00:28 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
then I guess you should also join date field in your query
i.e.

where .... and a.eta_dt = b.ds_eta_date (+)


Previous Topic: merge does not use index
Next Topic: Help!
Goto Forum:
  


Current Time: Thu Dec 08 18:40:08 CST 2016

Total time taken to generate the page: 0.05808 seconds