IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

8 byte Char variable to 10 byte date variable conversion


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts 10 byte RBA conversion DB2 2
Search our Forums:

Back to Top