oracle SQL row join

Hi

I need to take the following sql and convert it to the before output to the new output and do not know how. It should be the last 2 rows I believe this is max and max - 1

select dame
        ,oid
       ,scht
      ,jname
       ,odate
      ,rerun_counter
      ,ended_status
 from runinfo_history
where order_date = '20131016'



dname  oid    scht    jname       odate    rerun_counter  ended_status
-----  -----  ------- --------  ---------  ---------      ------------
DEV    aaaaa  AA      JOB1      20131016   1              16
DEV    aaaaa  AA      JOB1      20131016   2              32


to be the output of

dname  oid    scht    jname       odate    rerun_counter  ended_status   odate      rerun_counter  ended_status
-----  -----  ------- --------  ---------  ---------      ------------   ---------  ---------      ------------
DEV    aaaaa  AA      JOB1      20131016   1              16             20131016   2              32          


Thanks
LVL 1
mikeysmailbox1Asked:
Who is Participating?
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.

SharathData EngineerCommented:
select t1.dame
        ,t1.oid
       ,t1.scht
      ,t1.jname
       ,t1.odate
      ,t1.rerun_counter
      ,t1.ended_status
,t2.rerun_counter
,t2.ended_status
 from runinfo_history t1
 join runinfo_history t2 on t1.dame = t2.dame and t1.oid = t2.oid
  and t1.scht = t2.scht and t1. jname = t2. jname
where order_date = '20131016'
and t1.rerun_counter = 1 and t2.rerun_counter = 2
0
chaauCommented:
You have not specified the grouping criteria, but assuming to group by dname, oid, scht, jname the query should look like this:
WITH cte AS (
select dame
       ,oid
       ,scht
       ,jname
       ,odate
       ,rerun_counter
       ,ended_status
       ,ROW_NUMBER() OVER (PARTITION BY dame, oid, scht, jname, odate ORDER BY rerun_counter DESC) AS rn
 from runinfo_history)
SELECT a.select dame
       ,a.oid
       ,a.scht
       ,a.jname
       ,b.odate
       ,b.rerun_counter
       ,b.ended_status
       ,a.odate
       ,a.rerun_counter
       ,a.ended_status
FROM cte a LEFT JOIN cte b ON a.dame, a.oid, a.scht, a.jname, a.odate = b.dame, b.oid, b.scht, b.jname, b.odate  AND a.rn = 1 AND b.rn = 2
where a.order_date = '20131016'

Open in new window

If you omit the last line with WHERE clause the query will return you the end statuses for all dates
0
chaauCommented:
@Sharath_123: your query works only when rerun_counter is 1 and 2. But what if they are 3 and 6?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PortletPaulfreelancerCommented:
>>what if they are 3 and 6?
it's helpful to all involved if sample data covers likely conditions.

Given that additional clue, and also assuming the odate might differ between one run and the next, I would suggest the following. Note I have included 2 more 'jobs'; JOB2 has states of 3 and 6; JOB3 does not have a paired record. e.g. sample results
| DNAME |   OID | SCHT | JNAME |  A_ODATE | A_RERUN_COUNTER | A_ENDED_STATUS |  B_ODATE | B_RERUN_COUNTER | B_ENDED_STATUS |
|-------|-------|------|-------|----------|-----------------|----------------|----------|-----------------|----------------|
|   DEV | aaaaa |   AA |  JOB1 | 20131016 |               1 |             16 | 20131016 |               2 |             32 |
|   DEV | aaaaa |   AA |  JOB2 | 20131016 |               3 |             16 | 20131017 |               6 |             32 |
|   DEV | aaaaa |   AA |  JOB3 | 20131017 |               1 |             32 |   (null) |          (null) |         (null) |

Open in new window

The following logic uses row_number() and is "partitioned" by 4 fields - it's not really known if this is correct e.g. perhaps jname is sufficient for this?
Also note that the where clause used is using modulus, so it selects only rn's with odd numbers, because rn's of even numbers will be included via the additional columns.

(you can test by removing the where clause to see what happens)
WITH cte
AS (
    SELECT
          dname
        , oid
        , scht
        , jname
        , odate
        , rerun_counter
        , ended_status
        , ROW_NUMBER() OVER (PARTITION BY dname, oid, scht, jname ORDER BY rerun_counter ASC) AS rn
    FROM runinfo_history
    )
SELECT
      a.dname
    , a.oid
    , a.scht
    , a.jname
    , a.odate             a_odate
    , a.rerun_counter     a_rerun_counter
    , a.ended_status      a_ended_status
    , b.odate             b_odate
    , b.rerun_counter     b_rerun_counter
    , b.ended_status      b_ended_status
FROM cte a
LEFT JOIN cte b ON a.dname = b.dname
               AND a.oid   = b.oid
               AND a.scht  = b.scht
               AND a.jname = b.jname
               AND a.rn = (b.rn - 1)
WHERE mod(a.rn,2) = 1
;


    CREATE TABLE RUNINFO_HISTORY
    	("DNAME" varchar2(3), "OID" varchar2(5), "SCHT" varchar2(2), "JNAME" varchar2(4), "ODATE" int, "RERUN_COUNTER" int, "ENDED_STATUS" int)
    ;
    
    INSERT ALL 
    	INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
    		 VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 1, 16)
    	INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
    		 VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 2, 32)
    	INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
    		 VALUES ('DEV', 'aaaaa', 'AA', 'JOB2', 20131016, 3, 16)
    	INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
    		 VALUES ('DEV', 'aaaaa', 'AA', 'JOB2', 20131017, 6, 32)
    	INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
    		 VALUES ('DEV', 'aaaaa', 'AA', 'JOB3', 20131017, 1, 32)
    SELECT * FROM dual
    ;

Open in new window

@chaau, I get the feeling that what I've done above is very similar to what you intended - sorry - but if you check I think you will find some parts were unfinished.

http://sqlfiddle.com/#!4/c55dc/2
0
mikeysmailbox1Author Commented:
The above works but it is displaying all of the runs.
I need to just display the last 2.

Do I need a max() and max() -1 added to I only display the last 2

example


displays multiple lines

| DNAME |   OID | SCHT | JNAME |  A_ODATE | A_RERUN_COUNTER | A_ENDED_STATUS |  B_ODATE | B_RERUN_COUNTER | B_ENDED_STATUS |
|-------|-------|------|-------|----------|-----------------|----------------|----------|-----------------|----------------|
|   DEV | 00001 |   AA |  JOB1 | 20131016 |               1 |             16 | 20131016 |               2 |             32 |
|   DEV | 00001 |   AA |  JOB1 | 20131016 |               3 |             16 | 20131016 |               4 |             32 |
|   DEV | 00001 |   AA |  JOB1 | 20131016 |               5 |             32 | 20131016 |               6 |             16 |
|   DEV | 00001 |   AA |  JOB1 | 20131016 |               7 |             16 | 20131016 |               8 |             16 |


only need the last 2 last first and then prior

| DNAME |   OID | SCHT | JNAME |  A_ODATE | A_RERUN_COUNTER | A_ENDED_STATUS |  B_ODATE | B_RERUN_COUNTER | B_ENDED_STATUS |
|-------|-------|------|-------|----------|-----------------|----------------|----------|-----------------|----------------|
|   DEV | 00001 |   AA |  JOB1 | 20131016 |               8 |             16 | 20131016 |               7 |             16 |


Thank you for help btw.
0
PortletPaulfreelancerCommented:
with extra sample data (8 reruns of JOB1), plus the existing extra data I tested the results now are as follows - note due this change a JOB without at least 2 runs will not be listed (i.e. my JOB3 onl has one rune hence it is excluded by the where clause):
| DNAME |   OID | SCHT | JNAME |  A_ODATE | A_RERUN_COUNTER | A_ENDED_STATUS |  B_ODATE | B_RERUN_COUNTER | B_ENDED_STATUS | RN | X |
|-------|-------|------|-------|----------|-----------------|----------------|----------|-----------------|----------------|----|---|
|   DEV | aaaaa |   AA |  JOB1 | 20131016 |               7 |             16 | 20131016 |               8 |             32 |  2 | 1 |
|   DEV | aaaaa |   AA |  JOB2 | 20131016 |               3 |             16 | 20131017 |               6 |             32 |  2 | 1 |

Open in new window

Full details below:
WITH cte
AS (
    SELECT
          dname
        , oid
        , scht
        , jname
        , odate
        , rerun_counter
        , ended_status
        , ROW_NUMBER() OVER (PARTITION BY dname, oid, scht, jname ORDER BY rerun_counter DESC) AS rn
    FROM runinfo_history
    )
SELECT
      a.dname
    , a.oid
    , a.scht
    , a.jname
    , a.odate             a_odate
    , a.rerun_counter     a_rerun_counter
    , a.ended_status      a_ended_status
    , b.odate             b_odate
    , b.rerun_counter     b_rerun_counter
    , b.ended_status      b_ended_status
, a.rn --<< for testing only
, b.rn x --<< for testing only
FROM cte a
LEFT JOIN cte b ON a.dname = b.dname
               AND a.oid   = b.oid
               AND a.scht  = b.scht
               AND a.jname = b.jname
               AND b.rn = (a.rn - 1)
WHERE a.rn = 2
;
    

CREATE TABLE RUNINFO_HISTORY
    ("DNAME" varchar2(3), "OID" varchar2(5), "SCHT" varchar2(2), "JNAME" varchar2(4), "ODATE" int, "RERUN_COUNTER" int, "ENDED_STATUS" int)
;

INSERT ALL 
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 1, 16)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 2, 32)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 3, 16)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 4, 32)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 5, 16)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 6, 32)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 7, 16)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB1', 20131016, 8, 32)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB2', 20131016, 3, 16)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB2', 20131017, 6, 32)
    INTO RUNINFO_HISTORY ("DNAME", "OID", "SCHT", "JNAME", "ODATE", "RERUN_COUNTER", "ENDED_STATUS")
         VALUES ('DEV', 'aaaaa', 'AA', 'JOB3', 20131017, 1, 32)
SELECT * FROM dual 
;


    
 http://sqlfiddle.com/#!4/af42bd/1

Open in new window

0

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
PortletPaulfreelancerCommented:
if the above isn't what you meant (and have to admit I didn't understand "and prior")

would you please provide sample data and the expected result
0
SharathData EngineerCommented:
<<@Sharath_123: your query works only when rerun_counter is 1 and 2. But what if they are 3 and 6?>>

@chaau, yes, you are right. I see some good posts by you and Paul. Lets wait for mikeysmailbox1's response.
0
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.