Home » SQL & PL/SQL » SQL & PL/SQL » Multiple rows been pulled from select...Incorrect Join??
Multiple rows been pulled from select...Incorrect Join?? [message #238103] Wed, 16 May 2007 13:24 Go to next message
concorde800
Messages: 52
Registered: May 2007
Member
This query results in multiple row as a result set. My opinion is the join is incorrect?

Can you advise on how to change to avoid multiple rows result??

Thank you.

THE QUERY IS..............

SELECT SUM ( AL1.BAS_CNFRMD_QTY ),
SUM (AL1.BAS_ORDR_ENTRY_QTY ), AL1.CRD,
SUM ( AL1.BAS_RESERVED_QTY ), AL1.CHNL_CLASS, AL1.USAGE, AL1.DS_QLFR,
DECODE(to_char(AL1.ETA_DT,'YYYYMMDD'),'19000101',NULL,To_Char(AL1.ETA_DT,'YYYYMMDD') ) ,
AL3.DS_ETA_DATE, case when AL1.ITEM_CAT in ('ZJTS','ZJTP', 'ZJTD') then AL3.DS_ETA_DATE else
to_date((DECODE(to_char(AL1.ETA_DT,'YYYYMMDD'),'19000101',NULL,To_Char(AL1.ETA_DT,'YYYYMMDD') ) ) ,'yyyymmdd')
end,
SUM ( AL1.BAS_RESERVED_IN_TRANSIT_QTY ), SUM ( AL1.BAS_RESERVED_PO_QTY ), AL1.FIRM_COMMIT_FLAG

FROM OPS_RPT.RPT_SLS_ORDR_FACT AL1, OPS_RPT.RPT_DS_ETA_DATE AL3

WHERE [B]AL1.SO_HDR_NO = AL3.KEY_DRS_SO_NO (+)
AND AL1.SO_LN_ITM_NO = AL3.KEY_DRS_SO_LN (+)
AND AL1.SO_HDR_NO='0810410391'
AND AL1.SO_LN_ITM_NO='000010'

GROUP BY AL1.CRD, AL1.CHNL_CLASS, AL1.USAGE, AL1.DS_QLFR,
DECODE(to_char(AL1.ETA_DT,'YYYYMMDD'),'19000101',NULL,To_Char(AL1.ETA_DT,'YYYYMMDD') ) , AL3.DS_ETA_DATE,
case when AL1.ITEM_CAT in ('ZJTS','ZJTP', 'ZJTD')
then AL3.DS_ETA_DATE
else to_date((DECODE(to_char(AL1.ETA_DT,'YYYYMMDD'),'19000101',NULL,To_Char(AL1.ETA_DT,'YYYYMMDD') ) ) ,'yyyymmdd') end,
AL1.FIRM_COMMIT_FLAG

Re: Multiple rows been pulled from select...Incorrect Join?? [message #238111 is a reply to message #238103] Wed, 16 May 2007 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read the stickies of the forum (post your Oracle version, format your post, post all relevant data about your tables, for instance description...).

Regards
Michel
Re: Multiple rows been pulled from select...Incorrect Join?? [message #238112 is a reply to message #238103] Wed, 16 May 2007 13:58 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hard to tell, since we don't know the PK's and FK's for the related tables... If you post the table definitions, maybe we can help.

(edit: Michel types faster...)

[Updated on: Wed, 16 May 2007 13:58]

Report message to a moderator

Re: Multiple rows been pulled from select...Incorrect Join?? [message #238143 is a reply to message #238103] Wed, 16 May 2007 18:32 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
The VIEW OPS_RPT.RPT_DS_ETA_DATE AL3 is populated from the table
OPS_PO_LINE_MV. I am using ORACLE 9i, the table structure for the OPS_PO_LINE_MV table is attached to this email. Please let me know if you need more information than this.

The DDL used to populate OPS_RPT.RPT_DS_ETA_DATE is

CREATE OR REPLACE VIEW OPS_RPT.RPT_DS_ETA_DATE (
KEY_PO_HDR, KEY_PO_LINE, KEY_DRS_SO_NO, KEY_DRS_SO_LN, IB_DELIVERY_DT, PLAN_DLVRY_DT, DS_ETA_DATE)
AS
SELECT
key_po_hdr,
key_po_line,
key_drs_so_no,
key_drs_so_ln,
MIN(ib_delivery_dt) ib_delivery_dt,
MIN(plan_dlvry_dt) plan_dlvry_dt,
DECODE(MIN(PO.IB_DELIVERY_DT),NULL,MIN(po.plan_dlvry_dt),MIN(po.ib_delivery_dt)) ds_eta_date
FROM ops_stg.ops_po_line_mv po
WHERE key_drs_so_no > ' '
AND key_drs_so_ln > ' '
GROUP BY key_po_hdr,
key_po_line,
key_drs_so_no,
key_drs_so_ln.





Re: Multiple rows been pulled from select...Incorrect Join?? [message #238145 is a reply to message #238103] Wed, 16 May 2007 18:35 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Unless & until you respond to all the details requested by Michel Cadot & SKOOMAN, you might find meaningful responses in short supply.

[Updated on: Wed, 16 May 2007 18:35] by Moderator

Report message to a moderator

Previous Topic: Top 1 from Subtable
Next Topic: Some interview questions.
Goto Forum:
  


Current Time: Mon Dec 05 21:31:34 CST 2016

Total time taken to generate the page: 0.41437 seconds