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
 
Retrieve the Data form Current Month to Past 24 months

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

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Tue Apr 29, 2008 2:51 pm    Post subject: Retrieve the Data form Current Month to Past 24 months
Reply with quote

I want to retrieve the Data form Current Month to Past 24 months data form the Db2 table.

I have a control table and it is controlling the 85 tables and every time only one table is in Active Satae(A) and remaining are in Hestory Status(H) for the status flag column in CONTROL TABLE, Now i want to implement a interface must be able to return one or many table names to it based upon what the calling program request. For example I was to starting search the 85 tables starting with the current month for the last 24 month of history in this case you would return the last 24 table name who date match my search.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Tue Apr 29, 2008 2:54 pm    Post subject: Reply to: Db2 Querey Required?
Reply with quote

posting twice will not get You a faster answer..
I will delete the other topic icon_biggrin.gif
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 29, 2008 3:02 pm    Post subject:
Reply with quote

try this ....

Code:


SELECT * FROM table WHERE
DAY(EMPDATE) between 1 and  DAY(CURRENT DATE) AND
MONTH(EMPDATE) between 1 and  MONTH(CURRENT DATE) AND
YEAR(EMPDATE) BETWEEN YEAR(CURRENT DATE)-2 AND YEAR(CURRENT DATE);

where EMPDATE will be the date field in your table ..

try and let us know ..

Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Tue Apr 29, 2008 3:06 pm    Post subject: query..
Reply with quote

Try this...

SELECT * FROM tablename
WHERE date_column > (CURRENT_DATE - 2 YEARS);
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Tue Apr 29, 2008 3:17 pm    Post subject: Re: query..
Reply with quote

nabarundas wrote:
Try this...

SELECT * FROM tablename
WHERE date_column > (CURRENT_DATE - 2 YEARS);



insted > I think he needs last of Last two years

So he has to use < comparision
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Tue Apr 29, 2008 4:36 pm    Post subject:
Reply with quote

while executing this query i am getting sqlcode=180 SELECT * FROM tablename
WHERE date_column > (CURRENT_DATE - 2 YEARS);


here my date column contain only year and month with type "200803".
error info :

DSNT408I SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE CNTL_DTE IS
INVALID
DSNT418I SQLSTATE = 22007 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODTV SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -120 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF88' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Tue Apr 29, 2008 5:16 pm    Post subject: reply
Reply with quote

Hi babu_hi,
Can you tell me the datatype of the date column. If it is char then we have to go for different procedure.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 29, 2008 5:23 pm    Post subject:
Reply with quote

200803 is not a valid date format

here ull have to use


SELECT * FROM tablename
WHERE substr(date_column,1,4) > year(CURRENT_DATE - 2 YEARS) and substr(date_column,5,2) >= month(current date);
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 Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 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
No new posts File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm

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