Outer Join on Table [message #662618] |
Mon, 08 May 2017 03:10 |
|
syedferhat
Messages: 17 Registered: February 2013 Location: Karachi, Pakistan
|
Junior Member |
|
|
Dear Friends
I am trying to execute the outer join query but on the third outer join failed the result (showing no record), if removed the third outer join it shows the correct result, please let me know why this happen
SELECT INVOICE_DETAIL.ITEM_CODE,ITEM_DESC,
INVOICE_DETAIL.BATCH_ID,SUM(INVOICE_DETAIL.ITEM_QTY) AS QTY,
SUM(INVOICE_DETAIL.FREE_PC) AS FREE_PC,
SUM(INVOICE_DETAIL.ITEM_RATE) AS ITEM_RATE,SUM(INVOICE_DETAIL.TAX_AMT) AS TAX_AMT,
SUM(INVOICE_DETAIL.ITEM_DISC) AS ITEM_DISC
FROM INVOICE_DETAIL, INVOICE_MASTER, ITEM,SL_RT_DETAIL,SL_RT_MAST
WHERE
INVOICE_DETAIL.INV_NO=INVOICE_MASTER.INV_NO
AND INVOICE_DETAIL.ITEM_CODE=ITEM.ITEM_CODE
--AND INVOICE_MASTER.STAT='2'
AND INVOICE_MASTER.INV_NO='CI-0000000001'
---OUT JOIN 1
AND SL_RT_DETAIL.SL_RT_NO(+)=SL_RT_MAST.SL_RT_NO
---OUT JOIN 2
AND INVOICE_MASTER.INV_NO(+)=SL_RT_MAST.INV_NO
---OUT JOIN 3 ----THIS OUT JOIN CREATING RESULT
AND INVOICE_DETAIL.ITEM_CODE(+)=SL_RT_DETAIL.ITEM_CODE
GROUP BY
INVOICE_DETAIL.ITEM_CODE,ITEM.ITEM_DESC,INVOICE_DETAIL.BATCH_ID
|
|
|
|
|
Re: Outer Join on Table [message #662623 is a reply to message #662622] |
Mon, 08 May 2017 03:48 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Have you considered using ANSI standard join syntax? I find it much easier to understand and less prone to error. Example:orclz> select ename,dname from emp right outer join dept using(deptno);
ENAME DNAME
---------- --------------
KING ACCOUNTING
MILLER ACCOUNTING
CLARK ACCOUNTING
SMITH RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SCOTT RESEARCH
JONES RESEARCH
JAMES SALES
ALLEN SALES
BLAKE SALES
MARTIN SALES
TURNER SALES
WARD SALES
OPERATIONS
15 rows selected.
|
|
|
|
Re: Outer Join on Table [message #662625 is a reply to message #662624] |
Mon, 08 May 2017 04:00 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
So? Just add it to the join clause. This is basic SQL that anyone should be able to write. However, if you are going reply in UPPER CASE which is very annoying I shall sign off from this topic.
|
|
|
Re: Outer Join on Table [message #662626 is a reply to message #662622] |
Mon, 08 May 2017 04:41 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
syedferhat wrote on Mon, 08 May 2017 09:42thanks for your response, no invoice master and invoice_detail are not optional , If I removed outer join from SL_RT_MAST AND SL_RT_DETAIL then query also not showing any result
You haven't got an outer-join against sl_rt_mast to remove.
Maybe you want this:
AND SL_RT_DETAIL.SL_RT_NO(+)=SL_RT_MAST.SL_RT_NO
---OUT JOIN 2
AND INVOICE_MASTER.INV_NO=SL_RT_MAST.INV_NO(+)
---OUT JOIN 3 ----THIS OUT JOIN CREATING RESULT
AND INVOICE_DETAIL.ITEM_CODE=SL_RT_DETAIL.ITEM_CODE(+)
But that'll throw ora-01417 "a table may be outer joined to at most one other table"
So you're going to have to use the ANSI syntax:
FROM INVOICE_MASTER JOIN INVOICE_DETAIL ON INVOICE_DETAIL.INV_NO=INVOICE_MASTER.INV_NO
JOIN ITEM ON INVOICE_DETAIL.ITEM_CODE = ITEM.ITEM_CODE
LEFT JOIN SL_RT_MAST ON INVOICE_MASTER.INV_NO = SL_RT_MAST.INV_NO
LEFT JOIN SL_RT_DETAIL ON SL_RT_DETAIL.SL_RT_NO = SL_RT_MAST.SL_RT_NO
AND INVOICE_DETAIL.ITEM_CODE = SL_RT_DETAIL.ITEM_CODE
WHERE INVOICE_MASTER.INV_NO = 'CI-0000000001'
|
|
|
Re: Outer Join on Table [message #662642 is a reply to message #662626] |
Mon, 08 May 2017 12:51 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
cookiemonster wrote on Mon, 08 May 2017 05:41syedferhat wrote on Mon, 08 May 2017 09:42thanks for your response, no invoice master and invoice_detail are not optional , If I removed outer join from SL_RT_MAST AND SL_RT_DETAIL then query also not showing any result
So you're going to have to use the ANSI syntax:
OP referenced 6i in this post. Unfortunately ANSI syntax was not available back then.
|
|
|
Re: Outer Join on Table [message #662659 is a reply to message #662642] |
Tue, 09 May 2017 03:27 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That's the forms version. Assuming the DB version is higher (and it really needs to be) that query can be put in a stored proc or view.
Otherwise they're going to have to come up with a different approach, cause the outer join they want wont work with the old syntax.
|
|
|