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: 1239
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: 6966
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: 6966
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 File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts Conversion of a VB file to FB. isssal DFSORT/ICETOOL 1 Mon Oct 31, 2016 10:23 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am


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