OraFAQ Forum: SQL & PL/SQL » Conditional Join?

Home » SQL & PL/SQL » SQL & PL/SQL » Conditional Join?

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

Conditional Join? [message #617963]

Sat, 05 July 2014 12:46

sparsh2010
Messages: 1
Registered: July 2014
Location: India

Junior Member

Hi

I am not sure how to write query in order to do join, If POLINE.Item_Type = I then search in ITEMLOC and POLINE.Item_Type <> I then search in MMDIST table

Table1 POLINE
Table2 ITEMLOC
POLINE.Company = ITEMLOC.Company
POLINE.Item = ITEMLOC.Item
POLINE.Location = ITEMLOC.Location

Table1 POLINE
Table2 MMDIST
POLINE.Company = MMDIST.Company
POLINE.Item = MMDIST.Item
POLINE.Line_Nbr = MMDIST.Line_Nbr

Thanks in Advance
Pramod Chandra

Report message to a moderator

Re: Conditional Join? [message #617964 is a reply to message #617963]

Sat, 05 July 2014 12:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Report message to a moderator

Re: Conditional Join? [message #617965 is a reply to message #617963]

Sat, 05 July 2014 12:52

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Well you can have conditional filter predicates, however, not a good choice. But I am not clear with your requirement. Could you please post a test case.

Report message to a moderator

Re: Conditional Join? [message #617969 is a reply to message #617965]

Sat, 05 July 2014 13:56

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

One option might be use of the UNION set operator, such as

select ...
From poline, itemloc
Where POLINE.Company = ITEMLOC.Company
  And POLINE.Item = ITEMLOC.Item
  And POLINE.Location = ITEMLOC.Location 
  And poline.item_type = 'I'
Union all
Select ...
From poline, mmdist
Where POLINE.Company = MMDIST.Company
  And POLINE.Item = MMDIST.Item
  And POLINE.Line_Nbr = MMDIST.Line_Nbr 
  And poline.item_type <> 'I'

Report message to a moderator

Previous Topic:	How to insert data in another table
Next Topic:	getting error in a simple collection method

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 23:49:49 CDT 2024