Passing timstamp from input file to select statement

sree reddy · New User Joined: 20 Jul 2007 Posts: 27 Location: bangalore

i am writing a new cobol-db2 program, i am passing timstamp from input file to select statement, i need to compare the timestamp in the table with
the timestamp from the input file.

From the input file i am getting time and date as separate
time x(6) date x(8), what i am doing is i am combining this time and date
by moving to working storage ws-timestamp.

In the select query in the where clause when i specify timestamp from table > timestamp from working storage , i am getting error.
how should i specify this ,

SELECT XXX

FROM XXX
WHERE XXXX = :XXXX
AND XXX = :XXXX
AND XXX = :XXXX
AND XXX = :XXXX
AND XXXX IN ('XXXX',
'XXXX',
'XXXX',
'XXXX',
'XXXX',
'XXXX')
AND (XXX_XXX__TS > :WS-TIMESTAMP )

I am getting error as WS-TIMESTAMP undefined host-variable

Please suggest as early as possible

Thanks

dick scherrer · Posted: Wed Jul 08, 2009 10:00 pm

Hello,

Craq Giegerich · Posted: Wed Jul 08, 2009 10:01 pm

What is the definition of WS-TIMESTAMP?

sree reddy · New User Joined: 20 Jul 2007 Posts: 27 Location: bangalore

I declared ws-timestamp in my working storage as

dbzTHEdinosauer · Posted: Wed Jul 08, 2009 10:15 pm

we had a question like this a few weeks ago.

what is the level 01 definition?

or is it in a copybook?

sree reddy · New User Joined: 20 Jul 2007 Posts: 27 Location: bangalore

01 WS-MISC

i tried ws-timestamp as 01 level also, but i am getting error,please
suggest

thanks

dick scherrer · Posted: Wed Jul 08, 2009 10:23 pm

Hello,

It may help if you post the "real" sql from the program rather than the many xxx's . . .

Craq Giegerich · Posted: Wed Jul 08, 2009 10:24 pm

sree reddy · New User Joined: 20 Jul 2007 Posts: 27 Location: bangalore

it did not work

dbzTHEdinosauer · Posted: Wed Jul 08, 2009 10:41 pm

db2 wants a PIC clause, which is why a group item does not work.

sree reddy · New User Joined: 20 Jul 2007 Posts: 27 Location: bangalore

if my declaration is wrong , can you suggest how to declare and use it

thanks

dbzTHEdinosauer · Posted: Wed Jul 08, 2009 10:44 pm

sree reddy · New User Joined: 20 Jul 2007 Posts: 27 Location: bangalore

DSNH312I E DSNHSMUD LINE 2311 COL 49 UNDEFINED OR UNUSABLE HOST VARIABLE "WS-timestamp
DSNH312I E DSNHSMUD LINE 2313 COL 38 UNDEFINED OR UNUSABLE HOST VARIABLE "Ws-timestamp

I declared Ws-timestamp as below

01 WS-MISC
05 WS-TIMESTAMP PIC X(26)
VALUE '0000-00-00-00.00.00.000000'.
05 FILLER REDEFINES WS-TIMESTAMP.
10 WS-CCYY PIC X(04).
10 FILLER PIC X VALUE '-'.
10 WS-MONTH PIC X(02).
10 FILLER PIC X VALUE '-'.
10 WS-DAY PIC X(02).
10 FILLER PIC X VALUE '-'.
10 WS-HR PIC X(02).
10 FILLER PIC X VALUE '.'.
10 WS-MIN PIC X(02).
10 WS-MIN-9 REDEFINES
WS-MIN PIC 9(02).
10 FILLER PIC X VALUE '.'.
10 WS-SEC PIC X(02).
10 WS-SEC-9 REDEFINES
WS-SEC PIC 9(02).
10 WS-MILLI PIC X(07) value '.000000'.

The below is the sql

EXEC SQL
DECLARE F64CURSOR CURSOR FOR
SELECT MBR_ELIGY_CVRG_TMLY_FILG_TX,
MBR_ELIGY_CVRGE_EFFV_DT,
MBR_ELIGY_CVRGE_TERMTN_DT
FROM F64_A999
WHERE CUSTOMER_ID = :ARG-F64-A999.CUSTOMER-ID
AND CLIENT_ID = :ARG-F64-A999.CLIENT-ID
AND GROUP_ID = :ARG-F64-A999.GROUP-ID
AND MEMBER_ID = :ARG-F64-A999.MEMBER-ID
AND MBR_ELIGY_CVRG_TMLY_FILG_TX IN ('DFBDE-C',
'DSSI-C',
'DPART-C',
'DLIS-C',
'DPAPER-UC',
'DPOS-UC',
'DPOS-C')
AND (MBR_ELIGY_CVRGE_ADD_TS > :WS-TIMESTAMP
OR
LAST_MNT_TS > :WS-TIMESTAMP)
End-exec

Please suggest

Thanks

Craq Giegerich · Posted: Wed Jul 08, 2009 11:00 pm

My error it should be pic x(27)

sree reddy · New User Joined: 20 Jul 2007 Posts: 27 Location: bangalore

I did not understand what you mean.

Thanks

dbzTHEdinosauer · Posted: Wed Jul 08, 2009 11:59 pm

either MBR_ELIGY_CVRGE_ADD_TS or LAST_MNT_TS is not a timestamp.

or

your definition (if it is in the precompiler) comes after the DECLARE CURSOR statement.

for grins:
simply populate :ARG-F64-A999.MBR-ELIGY-CVRGE-ADD-TS and :ARG-F64-A999. LAST-MNT-TS with WS-TIMESTAMP,
and change the CURSOR DECLARATION to use the ARG-F64-A999 host variables.

and I asked you to cut&paste, you didn't:

HOST VARIABLE "WS-timestamp

and I wanted the WS-TIMESTAMP data division entry defining WS-TIMESTAMP cut&pasted from the precompiler output.

sree reddy · New User Joined: 20 Jul 2007 Posts: 27 Location: bangalore

dbzTHEdinosauer · Posted: Thu Jul 09, 2009 1:00 am

possibly

Anand Renganath S · New User Joined: 24 Dec 2007 Posts: 1 Location: chennai

There is a fix available from IBM for your pre-compiler.. Please check www-01.ibm.com/support/docview.wss?uid=swg1PQ73157 -

dbzTHEdinosauer · Posted: Mon Jun 28, 2010 5:53 pm

or you could simply populate your timestamp with a valid timestamp.

dbzTHEdinosauer · Posted: Mon Jun 28, 2010 6:41 pm

since it took me a while to find it,
I will provide you with a link to valid timestamp ranges

Rohit Umarjikar · Posted: Tue Jun 12, 2012 6:00 pm

any one has updates to this thread??

Naish · New User Joined: 07 Dec 2006 Posts: 82 Location: UK

Yearly job triggered. Need to investigate why it didn't run in 2011. (pun intended).

Rohit Umarjikar · Posted: Tue Jun 12, 2012 6:28 pm

Really

dick scherrer · Posted: Tue Jun 12, 2012 6:33 pm

Hello,