Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
8 byte Char variable to 10 byte date variable conversion

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

New User


Joined: 09 Dec 2008
Posts: 23
Location: india

PostPosted: Mon May 11, 2009 12:33 pm    Post subject: 8 byte Char variable to 10 byte date variable conversion
Reply with quote

Hi,

I have a query related to char-date conversion.
following is the data:

Input variable is 8 bytes charater
Variable in table is 10 bytes date field(yyyy-mm-dd format)
Want to compare these two fields in select query.

Used following group variable:
05 WS-DATE.
10 WS-YEAR PIC X(04).
10 Filler PIC X(01) VALUE '-'.
10 WS-MONTH PIC X(02).
10 Filler PIC X(01) VALUE '-'.
10 WS-DAY PIC X(02).

when using WS-DATE VARIABLE in where clause of query.
it is not allowing me to compile the program.
Please help.

Regards,
Vishu
Back to top
View user's profile Send private message

Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Mon May 11, 2009 1:26 pm    Post subject:
Reply with quote

Your data types are not the same. Convert the 8 byte input variable to the same format as WS-DATE before executing your SQL. It would help if you show the definitions of all relevant fields and your SQL code.
Back to top
View user's profile Send private message
replytovishu

New User


Joined: 09 Dec 2008
Posts: 23
Location: india

PostPosted: Mon May 11, 2009 1:44 pm    Post subject:
Reply with quote

Hi,

Please find below the types of different fields:

Name of variable Type
input field in table date (10 bytes yyyy-mm-dd format)
variable in prog WS-DATE X(10) YYYY-MM-DD
input field to prog X(8)

Not getting any SQL code as getting error in precompilation itself.
giving undefined or unresolved host variable.

Regards,
Vishu
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon May 11, 2009 5:22 pm    Post subject:
Reply with quote

what Terry meant was not SQLCODE
but
your coded SQL.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon May 11, 2009 5:54 pm    Post subject:
Reply with quote

Don't use group items for a host variable (except for var character fields).
Back to top
View user's profile Send private message
replytovishu

New User


Joined: 09 Dec 2008
Posts: 23
Location: india

PostPosted: Mon May 11, 2009 6:11 pm    Post subject:
Reply with quote

SELECT COUNT(*)
FROM table1
WHERE MATCH_SYS = 'X'
AND (ST_DATE = :ws-date
OR :ws-date = ' ')

ST_DATE is date in date in table
:ws-date is group variable as defined previously.

i am not able to compile this code as it is giving unknown host variable.

regards,vishu
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon May 11, 2009 6:24 pm    Post subject:
Reply with quote

I would suggest two sqls.

one when ws-date = spaces
Code:
SELECT COUNT(*)
FROM table1
WHERE MATCH_SYS = 'X'


and one when ws-date NOT = spaces
Code:
SELECT COUNT(*)
FROM table1
WHERE MATCH_SYS = 'X'
AND ST_DATE = :WS-HOST-VAR-DATE


where

Code:

05 WS-DATE.
......
05  WS-HOST-VAR-DATE
      REDEFINES
      WS-DATE                 PIC X(10).


since you have value clauses in your elementary items of ws-date
you will never, never have ws-date = spaces.
Code:
05 WS-DATE.
  10 WS-YEAR PIC X(04).
  10 Filler PIC X(01) VALUE '-'.
  10 WS-MONTH PIC X(02).
  10 Filler PIC X(01) VALUE '-'.
  10 WS-DAY PIC X(02).


and why do you insist on having a group item as a Host variable?
Back to top
View user's profile Send private message
replytovishu

New User


Joined: 09 Dec 2008
Posts: 23
Location: india

PostPosted: Mon May 11, 2009 6:26 pm    Post subject:
Reply with quote

Thanks i removed group item from the query.
it worked.
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 Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts Moving values to a variable of copybo... Vignesh Sid COBOL Programming 6 Wed Sep 06, 2017 1:04 pm
No new posts Julian Date to CICS ABSTTIME blayek CICS 3 Wed Aug 30, 2017 11:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us