ORACLE QUERY QUESTION

Hello
Have the following 3 tables :

Table One : Jobs
Job No.    Job date
1                10/09/2015
2                11/09/2015

Table Two : Progress

Job No.  Progress_code  Progress Date
1              1                         10/09/2015
1              2                         15/09/2015
1              3                         16/09/2015
2              1                         11/09/2015
2              2                         11/09/2015
2              3                         12/09/2015  

Table Three : Pro_progress

1  Receive
2  Repair
3 Test

Required Query result :

Job No.        Job Date             1-Receive       Days Between Repair and Receive    2-Repair            days betw test/repair          3-Test
1               10/09/2015            10/09/2015                             5                                 15/09/2015                 1                                 16/09/2015
2               11/09/2015            11/09/2015                             0                                 11/09/2015                 1                                 12/09/2015
m_jundiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
This gave me your expected result:
SELECT a.job_no, 
       a.job_date, 
       b.progress_date                   "1-Receive", 
       c.progress_date - b.progress_date "days betw repair/receive", 
       c.progress_date                   "2-Repair", 
       d.progress_date - c.progress_date "days betw test/repair", 
       d.progress_date                   "3-Test" 
FROM   jobs a 
       left outer join (SELECT e.job_no, 
                               e.progress_date 
                        FROM   progress e 
                               join pro_progress f 
                                 ON e.progress_code = f.progress_code 
                        WHERE  f.progress_desc = 'Receive') b 
                    ON a.job_no = b.job_no 
       left outer join (SELECT g.job_no, 
                               g.progress_date 
                        FROM   progress g 
                               join pro_progress h 
                                 ON g.progress_code = h.progress_code 
                        WHERE  h.progress_desc = 'Repair') c 
                    ON a.job_no = c.job_no 
       left outer join (SELECT i.job_no, 
                               i.progress_date 
                        FROM   progress i 
                               join pro_progress j 
                                 ON i.progress_code = j.progress_code 
                        WHERE  j.progress_desc = 'Test') d 
                    ON a.job_no = d.job_no;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
I think you're trying to get a SQL statement what will change the columns based on the data.
SQL can't do that.

You can however generate the results your requested by specifying the pro_progress yourself within the sql

SELECT job_no,
       job_date,
       receipt_date,
       repair_date - receipt_date days_betw_repair_and_receive,
       repair_date,
       test_date - repair_date days_betw_test_and_repair,
       test_date
  FROM (  SELECT j.job_no,
                 j.job_date,
                 MAX(CASE WHEN pp.name = 'Receive' THEN p.progress_date END) receipt_date,
                 MAX(CASE WHEN pp.name = 'Repair' THEN p.progress_date END) repair_date,
                 MAX(CASE WHEN pp.name = 'Test' THEN p.progress_date END) test_date
            FROM jobs j, progress p, pro_progress pp
           WHERE j.job_no = p.job_no AND pp.progress_code = p.progress_code
        GROUP BY j.job_no, j.job_date);


or same idea, using PIVOT syntax

SELECT job_no,
       job_date,
       receipt_date,
       repair_date - receipt_date days_betw_repair_and_receive,
       repair_date,
       test_date - repair_date days_betw_test_and_repair,
       test_date
  FROM (SELECT j.job_no,
               j.job_date,
               p.progress_date,
               pp.name
          FROM jobs j, progress p, pro_progress pp
         WHERE j.job_no = p.job_no AND pp.progress_code = p.progress_code)
       PIVOT
           (MAX(progress_date) FOR name IN ('Receive' receipt_date, 'Repair' repair_date, 'Test' test_date));


note the MAX isn't important, you need to aggregate the results of the multiple rows into a single row, you could use MIN just as well
m_jundiAuthor Commented:
mmm, becuase I have just posted a sample for Pro_progress, it has more than Receive, Repair,Test....other 20 Records
so Need something Dynamic
sdstuberCommented:
>>> so Need something Dynamic

you can't.  
As noted above - SQL doesn't work that way.

If you can use pl/sql and generate a report then ok.  If you're looking for a sql statement then you'll have to specify the 20 fields
johnsoneSenior Oracle DBACommented:
Is it truly dynamic?  Could there really be a new status added to the tables without some sort of other significant code change?  It would seem to me that while the number is large, it should be fairly static.  A lengthy query to write, but certainly possible.  What has been posted already is your framework, just keep adding more status codes to it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.