Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts Can I traverse a STEM variable? chong.zhou CLIST & REXX 5 Wed May 31, 2017 3:54 pm
No new posts Converting Variable block file to Fix... bhavana yalavarthi DFSORT/ICETOOL 10 Tue May 16, 2017 2:20 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us