|
View previous topic :: View next topic
|
| Author |
Message |
vijayakumar.yellala
New User
Joined: 19 Apr 2006
Posts: 63
Location: Chennai
|
|
|
|
Hi,
I am reading date field from the file and using date filed retrieving records from the table, but inthe compilation i am getting -206 abend code.
definition:
declare <table name> Cursor for
select
field1,
field2
where (date-field1 = '01/01/0001'
and date-field2 = '01/01/0001')
and (date-field1 = ws-file-date
or date-field2 = ws-file-date)
i am getting for the fields ws-file-date.
Thanks in advance.
Regards,
Vijay |
|
| Back to top |
|
 |
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| how about using :ws-file-date and reading about host variables. |
|
| Back to top |
|
|
vijayakumar.yellala
New User
Joined: 19 Apr 2006
Posts: 63
Location: Chennai
|
|
|
|
| already test that, that is not a host variable. its not worked |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| BTW, your query will not retrieve any rows unless ws-file-date has '01/01/0001' in which case, the where clause is redundant. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| vijayakumar.yellala wrote: |
| already test that, that is not a host variable. its not worked |
Tested what? if it did not work what was the error? Please post the error message completely.
And the variable in the SQL is a host variable. I am not bothered if you think otherwise. And as far as my knowledge goes, using a colon(':') before the host variable will definitely make it work. |
|
| Back to top |
|
|
vijayakumar.yellala
New User
Joined: 19 Apr 2006
Posts: 63
Location: Chennai
|
|
|
|
USING DB2ISDB AUTHORITY
STATEMENT=335
SQLCODE=-206
SQLSTATE=42703
TOKENS=WS-ACTIVITY-DATE
CSECT NAME=DSNXORSO
RDS CODE=-100
I have mentioned where clause wrongly.
right one is below.
where (date-field1 <> '01/01/0001'
and date-field2 <> '01/01/0001')
and (date-field1 = ws-file-date
or date-field2 = ws-file-date)
Regards
Vjay |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
Are you sure you are showing us the query which is giving error?
Because, WS-ACTIVITY-DATE is not used in the sql you are showing. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
of course it is not verbatim:
you must have : before ws-activty-date as already stated is post 2
afaik you can't have a column with - in the name, so date-field1 is not very likely. |
|
| Back to top |
|
|
vijayakumar.yellala
New User
Joined: 19 Apr 2006
Posts: 63
Location: Chennai
|
|
|
|
Hi all,
I have mentioned below what i have tested.
option1: WHERE (RTL_SALE_DT <> '01-01-0001'
AND IN_SVC_DT <> '01-01-0001')
AND (RTL_SALE_DT = WS-ACTIVITY-DATE
OR IN_SVC_DT = WS-ACTIVITY-DATE)
error msg:
- BIND SQL ERROR
USING DB2ISDB AUTHORITY
PLAN=ZRS1055P
DBRM=ZRS1055
STATEMENT=353
SQLCODE=-206
SQLSTATE=42703
TOKENS=WS-ACTIVITY-DATE
CSECT NAME=DSNXORSO
RDS CODE=-100
Option 2:
WHERE (RTL_SALE_DT <> '01-01-0001'
AND IN_SVC_DT <> '01-01-0001')
AND (RTL_SALE_DT = :WS-ACTIVITY-DATE
OR IN_SVC_DT = :WS-ACTIVITY-DATE)
error msg:
UNDEFINED OR UNUSABLE HOST VARIABLE "WS-ACTIVITY-DATE"
even i have defined in the working storage:
01 WS-ACTIVITY-DATE.
05 WS-ACTIVITY-DATE-MM PIC X(02).
05 FILLER PIC X(01) VALUE '-'.
05 WS-ACTIVITY-DATE-DD PIC X(02).
05 FILLER PIC X(01) VALUE '-'.
05 FILLER PIC X(02) VALUE '20'.
05 WS-ACTIVITY-DATE-YY PIC X(02).
Thanks in Advance.
Regards,
Vijay |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| The second sql is correct. The problem is with the declaration of the host variable WS-ACTIVITY-DATE. This should have a picture clause to be a usable host variable. Try giving PIC X(10) and check if it compiles. |
|
| Back to top |
|
|
vijayakumar.yellala
New User
Joined: 19 Apr 2006
Posts: 63
Location: Chennai
|
|
|
|
| Thanks Bharath Bhat...... |
|
| Back to top |
|
|
|
|
