Hi,
Here is my source data..
100 1 3800 11/05/2005 10:21:01
100 1 6000 11/06/2005 10:20:02 100 2 3800 11/05/2005 10:22:01
101 1 3800 11/05/2005 11:20:01
101 2 6000 11/05/2005 11:23:01
I want my target file to be populated as
100 1 11/05/2005 11:20:01 11/06/2005 10:20:02
( Max date for 3800) (max dt for 6000)
100 2 11/05/2005 10:22:01 11/05/2005 11:23:01
I have done this in server jobs, But I am not able to do it into parallel job.. Please suggest me how should i proccedd...
I tried the following steps using parallel jobs and got the following results
1. used OCIStage and use the following SQL
select NBR,SEQ, EVNT_CD, max (EVNT_EFCTV_DT)
from Kav group by SEQ,EVNT_CD,NBR
AND GOT tHE FOLLOWING RESULT:-
100 1 3800 11-MAY-05 11:20:01.000000 AM
100 1 6000 11-JUN-05 10:20:02.000000 AM
100 2 3800 11-MAY-05 10:22:01.000000 AM
100 2 6000 11-MAY-05 11:23:01.000000 AM
But I have no idea how to procceed to get the result like as follows:
100 1 11-MAY-05 11:20:01.000000 AM 11-JUN-05 10:20:02.000000 AM
100 2 11-MAY-05 10:22:01.000000 AM 11-MAY-05 11:23:01.000000 AM
Regards
Neeraj Mahajan
Columns to Rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
Re: Columns to Rows
Can you have your database engine do the work?
Code: Select all
SELECT K1.NBR,
K1.SEQ,
( SELECT MAX(EVNT_EFCTV_DT)
FROM Kav K2
WHERE K1.NBR= K2.NBR
AND K1.SEQ = K2.SEQ
AND K2.EVNT_CD = 3800 ) AS "DT1",
( SELECT MAX(EVNT_EFCTV_DT)
FROM Kav K3
WHERE K1.NBR= K3.NBR
AND K1.SEQ = K3.SEQ
AND K3.EVNT_CD = 6000 ) AS "DT2"
FROM Kav K1
GROUP
BY K1.NBR,
K1.SEQ
How about some really simple SQL:
Code: Select all
SELECT NBR,
SEQ,
max(case when EVNT_CD=3800 then EVNT_EFCTV_DT else NULL end) "EVNT_EFCTV_DT_3800",
max(case when EVNT_CD=6000 then EVNT_EFCTV_DT else NULL end) "EVNT_EFCTV_DT_6000"
from Kav group by SEQ,NBR
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
kcbland wrote:How about some really simple SQL:Code: Select all
SELECT NBR, SEQ, max(case when EVNT_CD=3800 then EVNT_EFCTV_DT else NULL end) "EVNT_EFCTV_DT_3800", max(case when EVNT_CD=6000 then EVNT_EFCTV_DT else NULL end) "EVNT_EFCTV_DT_6000" from Kav group by SEQ,NBR
Hi
I tried this query.... and i got the following result
100 1 11-May-05 11:23:01 AM 11-Jun-05 10:20:02 AM
100 2 11-May-05 10:22:01 AM
101 1 11-May-05 11:20:01 AM
101 2 11-May-05 11:23:01 AM
But my expected results are:-
100 1 11-MAY-05 11:20:01.000000 AM 11-JUN-05 10:20:02.000000 AM
100 2 11-MAY-05 10:22:01.000000 AM 11-MAY-05 11:23:01.000000 AM
And the source data is:
100 1 3800 11/05/2005 10:21:01
100 1 6000 11/06/2005 10:20:02
100 2 3800 11/05/2005 10:22:01
101 1 3800 11/05/2005 11:20:01
101 2 6000 11/05/2005 11:23:01
Please do the needful..
Regards
NM
Re: Columns to Rows
Ultramundane wrote:Can you have your database engine do the work?
Code: Select all
SELECT K1.NBR, K1.SEQ, ( SELECT MAX(EVNT_EFCTV_DT) FROM Kav K2 WHERE K1.NBR= K2.NBR AND K1.SEQ = K2.SEQ AND K2.EVNT_CD = 3800 ) AS "DT1", ( SELECT MAX(EVNT_EFCTV_DT) FROM Kav K3 WHERE K1.NBR= K3.NBR AND K1.SEQ = K3.SEQ AND K3.EVNT_CD = 6000 ) AS "DT2" FROM Kav K1 GROUP BY K1.NBR, K1.SEQ
Hi tried this query.... and i got the following result
100 1 11-May-05 11:23:01 AM 11-Jun-05 10:20:02 AM
100 2 11-May-05 10:22:01 AM
101 1 11-May-05 11:20:01 AM
101 2 11-May-05 11:23:01 AM
But my expected results are:-
100 1 11-MAY-05 11:20:01.000000 AM 11-JUN-05 10:20:02.000000 AM
100 2 11-MAY-05 10:22:01.000000 AM 11-MAY-05 11:23:01.000000 AM
And the source data is:
100 1 3800 11/05/2005 10:21:01
100 1 6000 11/06/2005 10:20:02
100 2 3800 11/05/2005 10:22:01
101 1 3800 11/05/2005 11:20:01
101 2 6000 11/05/2005 11:23:01
Please do the needful..
Regards
NM
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
Re: Columns to Rows
I entered your data and I came up with this query. I hope someone can come up with a better answer for you. This query might not run well on your database and if you do run it, you may want your DBA (if not yourself) to validate indexes exist for this query.
Code: Select all
SELECT K1.NBR,
K1.SEQ,
(SELECT MAX(K2.EVNT_EFCTV_DT)
FROM Kav K2
WHERE K2.EVNT_CD = 3800
AND K1.SEQ = K2.SEQ
AND K2.NBR = ( SELECT MAX(K3.NBR)
FROM Kav K3
WHERE K3.EVNT_CD = K2.EVNT_CD
AND K1.SEQ = K3.SEQ ) ),
(SELECT MAX(K2.EVNT_EFCTV_DT)
FROM Kav K2
WHERE K2.EVNT_CD = 6000
AND K1.SEQ = K2.SEQ
AND K2.NBR = ( SELECT MAX(K3.NBR)
FROM Kav K3
WHERE K3.EVNT_CD = K2.EVNT_CD
AND K1.SEQ = K3.SEQ ) )
FROM Kav K1
WHERE K1.NBR = ( SELECT MIN(NBR) FROM Kav )
GROUP
BY K1.NBR,
K1.SEQ
Neeraj,
The query that Ken has posted and results thereof should be correct. What is the second date in your output's second row -
100 2 11-MAY-05 10:22:01.000000 AM 11-MAY-05 11:23:01.000000 AM
Your source doesn't have a seq 2 for NBR 100 and EVNT_CD 6000 but your result seems to have it. Or otherwise what did you mean by max dt for 6000? Is it max across NBR and EVNT_CD alone?
The query that Ken has posted and results thereof should be correct. What is the second date in your output's second row -
100 2 11-MAY-05 10:22:01.000000 AM 11-MAY-05 11:23:01.000000 AM
Your source doesn't have a seq 2 for NBR 100 and EVNT_CD 6000 but your result seems to have it. Or otherwise what did you mean by max dt for 6000? Is it max across NBR and EVNT_CD alone?