Home » SQL & PL/SQL » SQL & PL/SQL » Left Outer Join Error (merged) (duplicate deleted by bb) (oracle)
Left Outer Join Error (merged) (duplicate deleted by bb) [message #689870] |
Wed, 05 June 2024 08:13 |
![](http://www.gravatar.com/avatar/bfbe81cb4f183effda1772de042d00ed?s=64&d=mm&r=g) |
suji6281
Messages: 144 Registered: September 2014
|
Senior Member |
|
|
Hi All,
I have written below code and added extra logic which shown in orange color. It was working fine earlier but whenever I added that extra code, its throwing below error. Please help me.
Error: DSG_QLIK_PO_VW - SQL Error. Error Position: 0 Return: 1799 - ORA-01799: a column may not be outer-joined to a subquery
SELECT A.BUSINESS_UNIT || A.PO_ID
, A.VENDOR_ID
, E.NAME1
, A.BUSINESS_UNIT
, A.PO_ID
, A.PO_REF
, B.CATEGORY_ID
, F.DESCR60
, B.LINE_NBR
, B.DESCR254_MIXED
, B11X.XLATSHORTNAME
, C.SCHED_NBR
, C.SHIPTO_ID
, D.DISTRIB_LINE_NUM
, D.ACCOUNT
, I.DESCR
, D.DEPTID
, D.OPERATING_UNIT
, D.PROJECT_ID
, D.ACTIVITY_ID
, C.PCT_UNIT_PRC_TOL
, C.EXT_PRC_TOL
, A22X.XLATLONGNAME
, A.PO_DT
, D.MERCHANDISE_AMT
, D.MONETARY_AMOUNT
, D.MONETARY_AMOUNT- D.MERCHANDISE_AMT
, A.BUYER_ID
, G.OPRDEFNDESC
, G.EMAILID
, A.OPRID_ENTERED_BY
, H.OPRDEFNDESC
, A.ENTERED_DT
, A.ACTIVITY_DATE
, A.PYMNT_TERMS_CD
, PTH.DESCRSHORT
,NVL(DISP.TO_EMAILIDS
, ' ')
FROM ((PS_PO_HDR A LEFT OUTER JOIN PSOPRDEFN G ON A.BUYER_ID = G.OPRID ) LEFT OUTER JOIN PSOPRDEFN H ON
A.OPRID_ENTERED_BY = H.OPRID ) [color=orangered]LEFT OUTER JOIN PS_PO_DISPATCHED DISP ON A.BUSINESS_UNIT= DISP.BUSINESS_UNIT
AND A.PO_ID = DISP.PO_ID
AND DISP.process_instance = (
SELECT MAX(POM.process_instance)
FROM ps_po_dispatched POM
WHERE POM.business_unit = DISP.business_unit
AND POM.po_id = DISP.PO_ID
GROUP BY POM.business_unit, POM.po_id)[/color] LEFT OUTER JOIN (
SELECT *
FROM PSXLATITEM A22X
WHERE A22X.FIELDNAME='PO_STATUS'
AND A22X.EFF_STATUS = 'A'
AND A22X.EFFDT = (
SELECT MAX(EFFDT)
FROM PSXLATITEM TB
WHERE TB.FIELDNAME=A22X.FIELDNAME
AND TB.FIELDVALUE=A22X.FIELDVALUE
AND TB.EFF_STATUS = 'A'
AND TB.EFFDT <= SYSDATE ) ) A22X ON A22X.FIELDVALUE=A.PO_STATUS LEFT OUTER JOIN (
SELECT *
FROM PS_PYMT_TRMS_HDR PTH
WHERE PTH.SETID = 'DICKS' ) PTH ON PTH.PYMNT_TERMS_CD = A.PYMNT_TERMS_CD , PS_PO_LINE B LEFT OUTER JOIN (
SELECT *
FROM PSXLATITEM B11X
WHERE B11X.FIELDNAME='CANCEL_STATUS'
AND B11X.EFF_STATUS = 'A'
AND B11X.EFFDT = (
SELECT MAX(EFFDT)
FROM PSXLATITEM TB
WHERE TB.FIELDNAME=B11X.FIELDNAME
AND TB.FIELDVALUE=B11X.FIELDVALUE
AND TB.EFF_STATUS = 'A'
AND TB.EFFDT <= SYSDATE ) ) B11X ON B11X.FIELDVALUE=B.CANCEL_STATUS, PS_PO_LINE_SHIP C,
(PS_PO_LINE_DISTRIB D LEFT OUTER JOIN PS_GL_ACCOUNT_TBL I ON I.ACCOUNT = D.ACCOUNT ), PS_VENDOR E,
PS_ITM_CAT_TBL F
WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.PO_ID = B.PO_ID
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.PO_ID = C.PO_ID
AND B.LINE_NBR = C.LINE_NBR
AND C.BUSINESS_UNIT = D.BUSINESS_UNIT
AND C.PO_ID = D.PO_ID
AND C.LINE_NBR = D.LINE_NBR
AND C.SCHED_NBR = D.SCHED_NBR
AND E.VENDOR_ID = A.VENDOR_ID
AND F.CATEGORY_ID = B.CATEGORY_ID
AND F.EFFDT = (
SELECT MAX(F_ED.EFFDT)
FROM PS_ITM_CAT_TBL F_ED
WHERE F.SETID = F_ED.SETID
AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE
AND F.CATEGORY_CD = F_ED.CATEGORY_CD
AND F.CATEGORY_ID = F_ED.CATEGORY_ID
AND F_ED.EFFDT <= SYSDATE)
AND A.PO_DT >= (
SELECT TO_DATE(S1.STRING_TEXT
, 'YYYY-MM-DD')
FROM PS_STRINGS_TBL S1
WHERE S1.PROGRAM_ID = 'DSG_QLIK'
AND S1.STRING_ID = 'DSG_QLIK_PO_STRT')
AND A.PO_DT <= (
SELECT TO_DATE(S1.STRING_TEXT
, 'YYYY-MM-DD')
FROM PS_STRINGS_TBL S1
WHERE S1.PROGRAM_ID = 'DSG_QLIK'
AND S1.STRING_ID = 'DSG_QLIK_PO_END')
AND I.EFFDT = (
SELECT MAX(I_ED.EFFDT)
FROM PS_GL_ACCOUNT_TBL I_ED
WHERE I.SETID = I_ED.SETID
AND I.ACCOUNT = I_ED.ACCOUNT
AND I_ED.EFFDT <= SYSDATE)
AND I.SETID = 'DICKS')
ORDER BY 4, 5, 9, 13
Thank you.
Regards
Suji
[Updated on: Wed, 05 June 2024 19:42] by Moderator Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:34:10 CDT 2024
|