Need a query!! please help

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
sganesh_85
Member
Posts: 14
Joined: Thu Jun 16, 2011 10:18 am

Need a query!! please help

Post by sganesh_85 » Wed Jun 22, 2011 10:38 am

Hi
i need a query which extracts data from a table based on timestamp.
structure of the table will be

Acc.no Data Timestamp

This table will get updated daily..
i need to retrieve the data which gets updated for the current date.

i have a jcl which invokes the cobol/db2 program which will have the query to create a report file have the records from the table..

Jcl is sheduled to run daily, so i need query which retrieves the data based on timestamp column.

Please help!!
Thanks,
Ganesh.

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Wed Jun 22, 2011 1:45 pm

So, what have you tried?
Regards,
Anuj

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Jun 22, 2011 1:48 pm

Code: Select all

WHERE <acc.no data timestamp> BETWEEN TIMESTAMP &#40;CURRENT_DATE, '00.00.00'&#41;
         AND TIMESTAMP &#40;CURRENT_DATE, '23.59.59'&#41; + 999999 MICROSECOND
you can test the above sql by the following in spufi:

Code: Select all

SELECT TIMESTAMP &#40;CURRENT_DATE, '00.00.00'&#41;
     , TIMESTAMP &#40;CURRENT_DATE, '23.59.59'&#41; + 999999 MICROSECOND
FROM SYSIBM.SYSDUMMY1
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

sganesh_85
Member
Posts: 14
Joined: Thu Jun 16, 2011 10:18 am

Post by sganesh_85 » Wed Jun 22, 2011 2:39 pm

Hi Dick,

Thankyou very much for your quick reply!!

i am getting the required result.. :D

i have another doubt, how to find the (CURRENT_DATE - 1 day)

i tried with below query

SELECT TIMESTAMP (SELECT(CURRENT_DATE - 1 days), '00.00.00' FROM SYSIBM.SYSDUMMY1) , TIMESTAMP ((SELECT (CURRENT_DATE - 1 days), '23.59.59')) + 999999 MICROSECOND
FROM SYSIBM.SYSDUMMY1

i am getting error !!

please correct me
Thanks,
Ganesh.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Jun 22, 2011 2:51 pm

and the error that you are getting?
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Jun 22, 2011 2:56 pm

SELECT TIMESTAMP(CURRENT_DATE - 1 DAY,'00.00.00')
FROM SYSIBM.SYSDUMMY1
;
SELECT TIMESTAMP(CURRENT_DATE - 1 DAY,'23.59.59') + 999999 MICROSECOND
FROM SYSIBM.SYSDUMMY1
;
you really need to learn how to debug you syntax.
why could you not experiement and determine the correct syntax on your own?
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others