Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join HELP (Oracle SQL)
Outer Join HELP [message #435897] Fri, 18 December 2009 12:47 Go to next message
wplaza
Messages: 9
Registered: December 2009
Junior Member
Hi. When I try to get the information of a record that no has data in the table NBRBJOB the system display a no data to fetch. Instead if I get a record that has data in both tables, the record display. I want to get the data with the database link no matter that the record has no data in NBRBJOB. Any suggestion?

SELECT DISTINCT
B.NBRBJOB_PIDM,
SPRIDEN_ID,
SPRIDEN_LAST_NAME,
SPRIDEN_FIRST_NAME,
VPOSN.POSN,
VPOSN.SUFF,
VPOSN.BEGIN_DATE,
VPOSN.END_DATE
FROM keygroupdata164, NBRBJOB@FINBACK B, SPRIDEN@FINBACK,
(select C.NBRBJOB_PIDM PIDM,
C.NBRBJOB_POSN POSN,
C.NBRBJOB_SUFF SUFF,
C.NBRBJOB_BEGIN_DATE BEGIN_DATE,
C.NBRBJOB_END_DATE END_DATE
WHERE C.NBRBJOB_POSN(+) = SUBSTR(keygroupdata164.KG342,1,6)
AND C.NBRBJOB_SUFF =
(SELECT MAX(A.NBRBJOB_SUFF)
FROM NBRBJOB@FINBACK A
WHERE A.NBRBJOB_PIDM = C.NBRBJOB_PIDM)) VPOSN
WHERE SPRIDEN_ID = 'XXXXXXXXXX'
AND SPRIDEN_CHANGE_IND IS NULL
AND B.NBRBJOB_PIDM(+) = SPRIDEN_PIDM
AND B.NBRBJOB_SUFF =
(SELECT MAX(A.NBRBJOB_SUFF)
FROM NBRBJOB@FINBACK A
WHERE A.NBRBJOB_PIDM = B.NBRBJOB_PIDM)
AND VPOSN.PIDM = SPRIDEN_PIDM
AND VPOSN.POSN_ONBASE(+) = B.NBRBJOB_POSN
AND SPRIDEN_ID = VPOSN.ID_ONBASE

Re: Outer Join HELP [message #435898 is a reply to message #435897] Fri, 18 December 2009 13:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.


It would be helpful if you posted SQL with valid syntax

[Updated on: Fri, 18 December 2009 13:06]

Report message to a moderator

Re: Outer Join HELP [message #435902 is a reply to message #435897] Fri, 18 December 2009 13:21 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Thu, 17 December 2009 17:29
Please read OraFAQ Forum Guide, among it (but not only) "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Previous Topic: Join ? left outer join ?
Next Topic: Using /* + Append */ insert hint
Goto Forum:
  


Current Time: Thu Dec 08 01:54:26 CST 2016

Total time taken to generate the page: 0.20582 seconds