View previous topic :: View next topic
|
Author |
Message |
replytovishu
New User
Joined: 09 Dec 2008 Posts: 23 Location: india
|
|
|
|
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 |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
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 |
|
|
replytovishu
New User
Joined: 09 Dec 2008 Posts: 23 Location: india
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
what Terry meant was not SQLCODE
but
your coded SQL. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Don't use group items for a host variable (except for var character fields). |
|
Back to top |
|
|
replytovishu
New User
Joined: 09 Dec 2008 Posts: 23 Location: india
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
replytovishu
New User
Joined: 09 Dec 2008 Posts: 23 Location: india
|
|
|
|
Thanks i removed group item from the query.
it worked. |
|
Back to top |
|
|
|