Outer Join HELP [message #435897] |
Fri, 18 December 2009 12:47  |
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   |
 |
BlackSwan
Messages: 26766 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  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Thu, 17 December 2009 17:29Please 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
|
|
|