Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join on Table (Forms 6i)
Outer Join on Table [message #662618] Mon, 08 May 2017 03:10 Go to next message
syedferhat
Messages: 9
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 #662621 is a reply to message #662618] Mon, 08 May 2017 03:28 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

The (+) needs to go next to columns from the table that is optional. I doubt you mean invoice_master and invoice_detail to be optional. So outer joins 2 and 3 are both wrong.
Re: Outer Join on Table [message #662622 is a reply to message #662621] Mon, 08 May 2017 03:42 Go to previous messageGo to next message
syedferhat
Messages: 9
Registered: February 2013
Location: Karachi, Pakistan
Junior Member
thanks 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

What I actually want is not to display those item HAVING SAME QTY AGAINST SAME INVOICE NO which was already in Sales Return BY ADDING

"having sum(invoice_Detail.ITEM_QTY - nvl(sl_rt_detail.ITEM_QTY,0))>0" that is why I keep SL_RT_MAST and SL_RT_DETAIL in query
Re: Outer Join on Table [message #662623 is a reply to message #662622] Mon, 08 May 2017 03:48 Go to previous messageGo to next message
John Watson
Messages: 7255
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 #662624 is a reply to message #662623] Mon, 08 May 2017 03:56 Go to previous messageGo to next message
syedferhat
Messages: 9
Registered: February 2013
Location: Karachi, Pakistan
Junior Member
DEAR HERE I HAVE MORE THEN 2 TABLE
Re: Outer Join on Table [message #662625 is a reply to message #662624] Mon, 08 May 2017 04:00 Go to previous messageGo to next message
John Watson
Messages: 7255
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 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
syedferhat wrote on Mon, 08 May 2017 09:42
thanks 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 Go to previous messageGo to next message
joy_division
Messages: 4821
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Mon, 08 May 2017 05:41
syedferhat wrote on Mon, 08 May 2017 09:42
thanks 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 Go to previous message
cookiemonster
Messages: 13011
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.
Previous Topic: Dynamic array as SP input
Next Topic: Returning Old value during update
Goto Forum:
  


Current Time: Sun Feb 18 21:46:20 CST 2018

Total time taken to generate the page: 0.06528 seconds