OraFAQ Forum: SQL & PL/SQL » Outer Join on Table

Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join on Table (Forms 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Outer Join on Table [message #662621 is a reply to message #662618]

Mon, 08 May 2017 03:28

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: Outer Join on Table [message #662622 is a reply to message #662621]

Mon, 08 May 2017 03:42

syedferhat
Messages: 17
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

Report message to a moderator

Re: Outer Join on Table [message #662623 is a reply to message #662622]

Mon, 08 May 2017 03:48

John Watson
Messages: 8929
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.

Report message to a moderator

Re: Outer Join on Table [message #662624 is a reply to message #662623]

Mon, 08 May 2017 03:56

syedferhat
Messages: 17
Registered: February 2013
Location: Karachi, Pakistan

Junior Member

DEAR HERE I HAVE MORE THEN 2 TABLE

Report message to a moderator

Re: Outer Join on Table [message #662625 is a reply to message #662624]

Mon, 08 May 2017 04:00

John Watson
Messages: 8929
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.

Report message to a moderator

Re: Outer Join on Table [message #662626 is a reply to message #662622]

Mon, 08 May 2017 04:41

cookiemonster
Messages: 13920
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'

Report message to a moderator

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: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.

Report message to a moderator

Re: Outer Join on Table [message #662659 is a reply to message #662642]

Tue, 09 May 2017 03:27

cookiemonster
Messages: 13920
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.

Report message to a moderator

Previous Topic:	Dynamic array as SP input
Next Topic:	Returning Old value during update

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 20:19:30 CDT 2024