IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

To compare data of two rows of a table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
gsbrar16

New User


Joined: 21 Dec 2006
Posts: 27
Location: india

PostPosted: Thu Mar 29, 2007 7:12 pm
Reply with quote

Hi All,

I want to formulate a query in such a way that i want to compare the EFF_DATE field of a row with the EFF_DATE field of the last row(order by primary key). Can it be done?

Actually in my case data in table is like this

EFF_DATE
2006-09-01
2006-08-01
2006-07-01
2006-05-01

2006-04-01

i want to find rows where there is discontinuation in the EFF_DATE like the one shown above in bold letters. Ideally there must have been 2006-06-01 after 2006-07-01, But it is not there.

Please help me in this..


Thanks
Guralam Singh
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Mar 29, 2007 9:23 pm
Reply with quote

Hello,

This should be straight forward if you declare a cursor that selects the rows in descending date order. As you fetch each row, compare the date of the row just fetched against the previous date.

If they are not one day different, do what you need to. . . .
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Mar 30, 2007 1:15 pm
Reply with quote

I'm afraid, You cannot do that just in a SQL query as per my knowledge.
Back to top
View user's profile Send private message
gsbrar16

New User


Joined: 21 Dec 2006
Posts: 27
Location: india

PostPosted: Fri Mar 30, 2007 5:21 pm
Reply with quote

HI All,
Thanks for the replies. I dont want to use the cursor. I just want to use a query. Can it be done through the use of a correlated sub query.

Thanks,
Guralam Singh
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Mar 30, 2007 9:53 pm
Reply with quote

And you don't want to use a cursor because. . .?
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Fri Mar 30, 2007 9:59 pm
Reply with quote

Hi Guralam,


Since I am not very sure of your table keys, I have created a similar solution with an emp table having emp_no and eff_dt with key as the emp_no. This query will make sure that all the records which are not having higher the next date or lower key having the previous date. I think we can tune the query still..But this can give you a starting point.

This query will also return the min and max key record as that also not satifies the condition you have mentioned.(If you want to remove it, may be you can use min and max fn in base table). Please see the query below:
SELECT * FROM EMP_TBL TMP
WHERE EMP_NO NOT IN(
SELECT TM.EMP_NO FROM EMP_TBL TM
WHERE EXISTS
( SELECT 1 FROM EMP_TBL TM1
WHERE TM1.EFF_DT = TM.EFF_DT + 1 DAY
AND TM1.EMP_NO IN
(SELECT MIN(TM2.EMP_NO)
FROM EMP_TBL TM2
WHERE TM2.EMP_NO > TM.EMP_NO) )

AND EXISTS
( SELECT 1 FROM EMP_TBL TM3
WHERE TM3.EFF_DT = TM.EFF_DT - 1 DAY
AND TM3.EMP_NO IN
(SELECT MAX(TM4.EMP_NO)
FROM EMP_TBL TM4
WHERE TM4.EMP_NO < TM.EMP_NO) ) )

Please see if this helps
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Apr 03, 2007 6:34 am
Reply with quote

If you are willing to do the date comparision part, which shouldn't be too difficult, here is an SQL to check for out of sequence rows.

The table1 here is assumed to contain only one column with numerc data. This displays col1 of any row if it doesn't find any other row with col1 where the difference is only 1.

Code:
SELECT T1.COL1                             
FROM TABLE1 T1                             
WHERE EXISTS (SELECT 1 FROM TABLE1 T2       
        WHERE T2.COL1-T1.COL1 > 1           
        AND NOT EXISTS(                     
            SELECT 1 FROM TABLE1 T3         
            WHERE T3.COL1>T1.COL1           
            AND T3.COL1<T2.COL1)) 
Back to top
View user's profile Send private message
gsbrar16

New User


Joined: 21 Dec 2006
Posts: 27
Location: india

PostPosted: Tue Apr 10, 2007 8:55 pm
Reply with quote

Hi Wanderer,Prajesh
Thanks for your replies. With some modification/addition to your queries, i was able to solve my problem.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
Search our Forums:

Back to Top