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
 
Subtract 1 day from a date dynamically

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

New User


Joined: 29 Sep 2011
Posts: 6
Location: India

PostPosted: Fri Dec 02, 2011 6:38 pm    Post subject: Subtract 1 day from a date dynamically
Reply with quote

Hi ,

I have an input file which a date field in format '02/11/2011' i need to subtract 1 day from this date and update in a table.

i need an sql which can do this for me as i am using easytrive to do this

there are around 20k records in the file with different dates

currently i am using the below code which is not working -

Code:
select (date (ws-date -1 day ))  from sysibm.sysdummy1


please help
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Dec 02, 2011 7:08 pm    Post subject:
Reply with quote

not working is a rather useless comment, don't you think?
why isn't it working?
you would have received some code, either from the compiler or spufi.

practice in spufi replacing ws-date with current_date until you get the syntax correct for the functions that you are using.

and once you get it correct, put it in the update statement.
get rid of the select and just make it part of the UPDATE Clause for the column.
i don't know how in ezytrieve sql to indicate a host variable,
but in cobol the variable name is prefaced by a colon (icon_smile.gif
Back to top
View user's profile Send private message
Sandesh Kirodian

New User


Joined: 29 Sep 2011
Posts: 6
Location: India

PostPosted: Fri Dec 02, 2011 9:18 pm    Post subject:
Reply with quote

i tried it doing with declaring a cursor i am getting the below error -


Code:
SQL DECLARE DATE_CSR CURSOR FOR +                                       
    £ SQL error, SQLCODE is -418                                       
    £ DSNT408I SQLCODE = -418, ERROR:  A STATEMENT STRING TO BE PREPARED
    £          CONTAINS AN INVALID USE OF PARAMETER MARKERS             
    £ DSNT418I SQLSTATE   = 42610 SQLSTATE RETURN CODE                 
£ SQL errors found                                                     
    SELECT (DATE (:IN-WS-PA-HIST-STR-DTE) - 1 DAY) +                   
    FROM SYSIBM.SYSDUMMY1 +                                             
    FOR FETCH ONLY
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Dec 02, 2011 9:36 pm    Post subject:
Reply with quote

don't know what you are doing or where,

normally parameter markers (place holders) are ?.
don't think we can use alias or variable name on mainframe.
on pc, yes, but it requires the declaration of the variable name to db2.

it appears that you want to execute two sql's.
1. get a date
2. update something with this date
for each of your 20k trigger-records.

why not include the date - 1 day bit in the UPDATE Clause,
and then only have one sql?
Back to top
View user's profile Send private message
Mickeydusaor

Active User


Joined: 24 May 2006
Posts: 258
Location: Salem, Oregon

PostPosted: Sat Dec 03, 2011 12:49 am    Post subject: Correct DB2 command format
Reply with quote

This is the correct format of the command....

Code:
SELECT (DATE(:IN-WS-PA-HIST-STR-DTE)) - 1 DAY 
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 Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts ICETOOL - RESIZE DYNAMICALLY kranthikumarb DFSORT/ICETOOL 0 Tue Nov 21, 2017 5:30 pm
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm

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