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
 

 

Need to subtract X hours from timestamp in where clause

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

New User


Joined: 08 Jan 2011
Posts: 16
Location: India

PostPosted: Thu May 09, 2013 7:02 pm    Post subject: Need to subtract X hours from timestamp in where clause
Reply with quote

Hi,

I have to use a query in cobol code as shown below:

Code:

EXEC SQL                                                 
 SELECT COUNT(*)                                         
   INTO                                                 
        :WS-COUNT                                 
   FROM table1                                 
   WHERE LST_UPDT_DTTM <= CURRENT TIMESTAMP - :WS-X HOURS
END-EXEC                                                 


Earlier i was using like "- 8 HOURS" and it was working perfect. But now, requirement is to hour from dataset and so, need to subtract like shown above.

It is failing in bind with SQLCODE=-171 SQLSTATE=42815.

WS-X is defined as x(02) in program. Please suggest
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu May 09, 2013 7:25 pm    Post subject:
Reply with quote

Define it as 9(02) icon_cool.gif

you can't substract with X(02)
Back to top
View user's profile Send private message
Ankit Kumar Gupta

New User


Joined: 08 Jan 2011
Posts: 16
Location: India

PostPosted: Thu May 09, 2013 7:29 pm    Post subject:
Reply with quote

I have tried that... Current Timestamp is system defined as X(26)
Back to top
View user's profile Send private message
Ankit Kumar Gupta

New User


Joined: 08 Jan 2011
Posts: 16
Location: India

PostPosted: Thu May 09, 2013 7:31 pm    Post subject:
Reply with quote

when 9(02), it throws compilation error as 'UNDEFINED OR UNUSABLE HOST VARIABLE "WS-X" '
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu May 09, 2013 7:36 pm    Post subject:
Reply with quote

Apologise Define it as S9(04) COMP
Back to top
View user's profile Send private message
Ankit Kumar Gupta

New User


Joined: 08 Jan 2011
Posts: 16
Location: India

PostPosted: Thu May 09, 2013 7:49 pm    Post subject:
Reply with quote

It worked... THanks a much Pandora...
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 Converting EBCDIC to Unicode (TimeStamp) muralikrishnan_new COBOL Programming 2 Mon Jul 25, 2016 10:42 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts Convert Timestamp Value mrdinesh DFSORT/ICETOOL 7 Thu May 05, 2016 6:01 pm
No new posts Comparing timestamp in Synscort l.nethaji SYNCSORT 13 Wed Jan 20, 2016 4:47 pm
No new posts pass numeric value of length 14 to ti... Ralph Zbrog Java & MQSeries 4 Fri Jan 15, 2016 3:20 pm


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