Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Result should contain one open id which has minimum eff_dt

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
paritosh mathur

New User


Joined: 10 Apr 2006
Posts: 14

PostPosted: Fri Oct 03, 2008 8:40 pm    Post subject: Result should contain one open id which has minimum eff_dt
Reply with quote

i have following database
Code:

id    status     eff-dt        exp-dt
---------------------------------------------------
001    open     2008-05-01         _
002    open     2008-04-01         _
003    open     2008-03-01         _
004    closed   2008-03-01     2008-05-01
005    closed   2008-04-01     2008-07-01


my desired result should contain one open id which has minimum eff_dt and one closed id which has maximum expiry date.

like
Code:

id    status     eff-dt        exp-dt
---------------------------------------------------
003    open     2008-03-01         _
005    closed   2008-04-01     2008-07-01
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Oct 03, 2008 11:39 pm    Post subject:
Reply with quote

you've set-up unrealistic data. Require that solutions use this as column value definitions:
Code:

id    status     eff-dt        exp-dt
---------------------------------------------------
001    open     2008-05-01         _
002    open     2008-04-01         _
003    OPEN     2008-03-01         _
004    open     2008-04-01         _
005    Open     2008-03-01         _
006    closed   2008-03-01     2008-05-01
007    CLOSED   2008-04-01     2008-07-01
008    closed   2008-03-01     2008-05-01
009    Closed   2008-02-01     2008-07-01


unfortunately, in the real world, multiple rows with the same value (which ends up being a min or max) exist.
also
keep in mind, using lower case data a column values means you either must stipulate only one or the other, or look for both. Believe me, even though your INSERTs may be coded as upper or lower, some idiot with DB2 File-Aid or Spufi, or QMF can make life really difficult.

The solution taking into account these values is something you want someone to provide. A query for your data would not work for mine, unless coded for multiple MINs, MAXs, and case values.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Oct 03, 2008 11:40 pm    Post subject:
Reply with quote

Plus, are the date fields of DATE Datatype, integer, or char?
Back to top
View user's profile Send private message
paritosh mathur

New User


Joined: 10 Apr 2006
Posts: 14

PostPosted: Tue Oct 07, 2008 9:26 am    Post subject:
Reply with quote

hey dick i have shown that data as an example only. and date is in date format. even open and close cases are also defined while inserting data.
Anyway i have got the solution which is (as per my data)-

Code:

select * from table where
eff_dt in (
(select min(eff_dt) from table where status = 'open'),
(select eff_dt from table where exp_dt in (select max(exp_dt) from table where status = 'closed'))
);


thanx for ur suggestions.
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 CA-Endevor Admin - 3 years minimum vasanthz Mainframe Jobs 0 Tue Apr 04, 2017 5:36 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts scheduling between OPC and TWS give u... nsbl IBM Tools 4 Sun Mar 12, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts FTOPEN TEMP is failing due to dataset... mbenaud TSO/ISPF 7 Thu Jul 28, 2016 4:28 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us