Date field causing duplicate rows in output [message #243006] |
Tue, 05 June 2007 13:06  |
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 #243018 is a reply to message #243012] |
Tue, 05 June 2007 13:43   |
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   |
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
|
|
|
|