ORACLE QUERY QUESTION

m_jundi
m_jundi used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Oracle DBA
Commented:
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

Most Valuable Expert 2011
Top Expert 2012
Commented:
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

Author

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
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> 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 DBA

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial