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
 

 

select null or max end date

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

New User


Joined: 15 Jun 2006
Posts: 13

PostPosted: Fri Jan 25, 2008 12:07 pm    Post subject: select null or max end date
Reply with quote

Hi,
I have a doubt in writing a query, the purpose of the query is it retrieve a date from a table.
In the table if there are rows with null. then i should get null
but if there is no null then i should get max of the date.

eg:
if table 01 has 4 rows and the values are as below then i should get NULL

date
2007-10-19
2007-09-08
null
2007-02-01

answer = null

if table 01 has 4 rows and the values are as below then i should get 2007-10-19

date
2007-10-19
2007-09-08
2007-02-03
2007-02-01

answer = 2007-10-19

can anyone help me in writing a shingle query for the same. we can do the same with a cursor, but I want to know if we can do it through a single query or not.
Back to top
View user's profile Send private message

rag swain

New User


Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA

PostPosted: Fri Jan 25, 2008 11:36 pm    Post subject: Reply to: select null or max end date
Reply with quote

you can write query like
Code:

select max(char(date_col))
   from table_name;


and use function NULLIF(CHAR(DATE_COL) with the query to find the result, though not yet tested. might some other day, quite late.
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm


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