0

I am joining APPS.PER_JOBS on PER_ALL_ASSIGNMENTS_F, looking to get current row - but I get multiple rows unless I add REQUEST_ID to the join. However, when I do this - though it supresses the mulitple rows - it also causes the substring I use to pull Title to return a Null value for all records. How can I join these tables to pull only the current row without causing my Title substring to null out?

Here's the outer join I have right now:

APPS.PER_ALL_ASSIGNMENTS_F B 
   LEFT OUTER JOIN (SELECT * 
                    FROM APPS.PER_JOBS F_ED
                    WHERE F_ED.DATE_FROM = (SELECT MAX(F_ED1.DATE_FROM) 
                                            FROM APPS.PER_JOBS F_ED1
                                            WHERE F_ED1.JOB_ID = F_ED.JOB_ID
                                            AND F_ED1.DATE_FROM <= SYSDATE)) F
     ON  F.JOB_ID = B.JOB_ID 
         AND F.REQUEST_ID = B.REQUEST_ID

And here's the title substring:

SUBSTR(F.NAME,INSTR(F.NAME,'.')+1) AS "Occupation"

Thanks, Steve

Edit: Here's an example:

Without ‘F.REQUEST_ID = B.REQUEST_ID’:

Employee Number Occupation 597 Manager, Special Events 632 Web Developer 632 Software Developer 8392 Development Intern 8392 Software Developer

With ‘F.REQUEST_ID = B.REQUEST_ID’:

Employee Number Occupation 597 632 8392

smarca
  • 1
  • 1
  • 1
    As this question seems to be related to your data it is unlikely you'll get an answer unless you post some sample rows which demonstrate the problem. And perhaps the whole query - or at least the smallest working test case version of the query. – APC Jun 10 '13 at 17:37
  • Add some sample data. And try to add an expression 'F_ED.REQUEST_ID = F_ED1.REQUEST_ID' in your inner query, because it seems to be a part of the unique key of PER_JOBS table. – Mikhail Jun 10 '13 at 17:55

0 Answers0