issue with timestamp differece

Bhanupratap Natva · New User Joined: 13 Jul 2007 Posts: 8 Location: Hyderabad

Hi,

I have below requirement :

Input table layout

seq-id char(5) not null
file-recv-time-stamp timestamp not null with default
file-process-start-time-ts timestamp not null with default
file-proc-end-time-ts timestamp not null with default

I want to populate the output file with the below details from the input-table.

Output-file-layout:

seq-id , file-process-start-time-ts, file-proc-end-time-ts, process-duration

I have tried the below query

SELECT seq-id, file-proc-end-time-ts, file-process-start-time-ts, TIME(file-proc-end-time-ts) - TIME(file-process-start-time-ts)
AS process-duration
FROM CUST_TABLE

Fetch cursor logic -->

Fetch Cursor_a
into :SEQ-ID,
: FILE-PROC-END-TS,
: FILE-PROC-STRT-TS,
: process-duration

When I run the above query in QMF it is working fine.
but when I tried in the program it is giving -303. Also I have defined
WS-PROCESS-TIME with X(26) in the program.

Could anyone tell me the way to populate process-time in the output-file.

dbzTHEdinosauer · Posted: Mon Nov 02, 2009 6:44 pm

I can only assume from your fractured question that process-duration
is improperly defined

there is a preview button
(though you do have to modify your profile to enable this)
which you could use to see if the letters that you have managed to type
convey anything intelligent in your post.

Craq Giegerich · Posted: Mon Nov 02, 2009 7:03 pm

process-duration or WS-PROCESS-TIME which ever one you used must be defined as s9(14)v9(6) field.

dbzTHEdinosauer · Posted: Mon Nov 02, 2009 7:13 pm

TIME(file-proc-end-time-ts) - TIME(file-process-start-time-ts)

defined as s9(14)v9(6) field - timestamp arithmetic

Bhanupratap Natva · New User Joined: 13 Jul 2007 Posts: 8 Location: Hyderabad

dbzTHEdinosauer · Posted: Tue Nov 03, 2009 2:27 pm

FFS, why don't you read the links?

your sql is performing TIME arithmetic
not TIMESTAMP.

Time arithmetic requires a integer length 6.

craig provided 14v6 for timestamp - according to the manual it is 20v6.

if you need to increase your COBOL numeric capabilities, look at compiler option: ARITH.

dbzTHEdinosauer · Posted: Tue Nov 03, 2009 4:57 pm

additional comments:

if you do not want to use compiler options to allow for more than 18 digits,
suggest you use both
TIME(ts) - TIME(ts)
and
DATE(ts) - DATE(ts).

you need to know if the difference in timestamps is more than 24 hours
and you can not derive that by only using TIME
and to use TIMESTAMP - TIMESTAMP you need a larger host variable.