View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
I am getting error as WS-TIMESTAMP undefined host-variable |
Suggest this needs to be defined. . .
Not much we can help with when the system says a variable is not defined |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
What is the definition of WS-TIMESTAMP? |
|
Back to top |
|
|
sree reddy
New User
Joined: 20 Jul 2007 Posts: 27 Location: bangalore
|
|
|
|
I declared ws-timestamp in my working storage as
Code: |
05 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'. |
"Code"d |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
we had a question like this a few weeks ago.
what is the level 01 definition?
or is it in a copybook? |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
It may help if you post the "real" sql from the program rather than the many xxx's . . . |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
sree reddy wrote: |
I declared ws-timestamp in my working storage as
Code: |
05 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'. |
"Code"d |
DB2 does not like group items.
try this
Code: |
05 WS-TIMESTAMP pic x(26) value '0000-00-00-00.00.00.00000'.
05 filler redefines ws-timestamp.
10 WS-CCYY PIC X(04).
10 FILLER PIC X.
10 WS-MONTH PIC X(02).
10 FILLER PIC X.
10 WS-DAY PIC X(02).
10 FILLER PIC X.
10 WS-HR PIC X(02).
10 FILLER PIC X.
10 WS-MIN PIC X(02).
10 WS-MIN-9 REDEFINES
WS-MIN PIC 9(02).
10 FILLER PIC X.
10 WS-SEC PIC X(02).
10 WS-SEC-9 REDEFINES
WS-SEC PIC 9(02).
10 WS-MILLI PIC X(07). |
[/code] |
|
Back to top |
|
|
sree reddy
New User
Joined: 20 Jul 2007 Posts: 27 Location: bangalore
|
|
|
|
it did not work |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
db2 wants a PIC clause, which is why a group item does not work. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
what is the complete pre-compiler error message (cut&paste),
show us the definition of the timestamp Host variable (cut&paste from precompiler output)
show us the sql which received the pre-compiler error message (cut&paste from precompiler output) |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
My error it should be pic x(27) |
|
Back to top |
|
|
sree reddy
New User
Joined: 20 Jul 2007 Posts: 27 Location: bangalore
|
|
|
|
I did not understand what you mean.
Thanks |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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. |
|
Back to top |
|
|
sree reddy
New User
Joined: 20 Jul 2007 Posts: 27 Location: bangalore
|
|
|
|
Code: |
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'. |
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
possibly
dbzTHEdinosauer wrote: |
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.
|
try populating the other host variables and changing the DECLARE CURSOR statement as I suggested. |
|
Back to top |
|
|
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 - |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
or you could simply populate your timestamp with a valid timestamp.
Code: |
VALUE '0000-00-00-00.00.00.000000'. |
is invalid. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
any one has updates to this thread?? |
|
Back to top |
|
|
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).
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
Really |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
any one has updates to this thread?? |
If you had an actual question, it would have been better to start a new topic for your question rather than post a reply to a 2-year-old topic with nothing to contribute to that topic
Is there something you are tyring to learn?
d |
|
Back to top |
|
|
|