Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
To compare data of two rows of a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: To compare data of two rows of a table
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

Site Director


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

PostPosted: Thu Mar 29, 2007 9:23 pm    Post subject:
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    Post subject:
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    Post subject: Re: To compare data of two rows of a table
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

Site Director


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

PostPosted: Fri Mar 30, 2007 9:53 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts opening a dataset after reading it fr... arunsoods DFSORT/ICETOOL 5 Wed Oct 04, 2017 3:54 pm
This topic is locked: you cannot edit posts or make replies. PS file data should be passed as symb... d_sarlie JCL & VSAM 15 Tue Oct 03, 2017 5:18 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us