Problem with Between predicate in declare cursor?

babu_hi · Active User Joined: 11 Apr 2006 Posts: 66

i am getting error,when i submit bind jcl i got -206 error.
i have declared cursor like this,

DECLARE CONTROL_TABLE_CSR CURSOR FOR
SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'A' OR CNTL_FLAG = 'H' AND
CNTL_DTE BETWEEN WS-START-DATE AND WS-CURRENT-DATE
ORDER BY CNTL_DTE DESC

please look for bind error information

DSNX200I - BIND SQL ERROR
USING ARW01D AUTHORITY
PLAN=(NOT APPLICABLE)
DBRM=ARWFTB
STATEMENT=82
SQLCODE=-206
SQLSTATE=42703
TOKENS=WS-START-DATE
CSECT NAME=DSNXORSO
RDS CODE=-100
DSNT233I - UNSUCCESSFUL BIND FOR
PACKAGE = DSN0S.ARWCB0.ARWFTB.()

please tell me the solution for this?

Craq Giegerich · Posted: Tue May 13, 2008 5:44 pm Post subject:

ashimer · Posted: Tue May 13, 2008 5:45 pm Post subject:

babu,

change ur cursor declaration as

ashimer · Posted: Tue May 13, 2008 5:45 pm Post subject:

oops sorry craq ... i didnt c ur post ...

babu_hi · Active User Joined: 11 Apr 2006 Posts: 66

if tried with :(colon)..but i am getting compilation error,
DSNH312I E DSNHSMUD LINE 88 COL 36 UNDEFINED OR UNUSABLE HOST VARIABLE
DSNH312I E DSNHSMUD LINE 88 COL 55 UNDEFINED OR UNUSABLE HOST VARIABLE

Thanks
babu

ashimer · Posted: Tue May 13, 2008 6:05 pm Post subject:

Did you define WS-START-DATE and WS-CURRENT-DATE in WS ?

Craq Giegerich · Posted: Tue May 13, 2008 6:34 pm Post subject:

What are the definitions of ws-start-date and ws-current-date?

babu_hi · Active User Joined: 11 Apr 2006 Posts: 66

Yes..i have declared both variables in working storage section,
05 WS-START-DATE PIC 9(06) VALUE ZEROS.
05 WS-CURRENT-DATE PIC 9(06) VALUE ZEROS.

and i have modified the cursor declaration as per Craq Giegerich post reply,

EXEC SQL
DECLARE CONTROL_TABLE_CSR CURSOR FOR
SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'A' OR CNTL_FLAG = 'H'
AND (CNTL_DTE BETWEEN :WS-START-DATE AND :WS-CURRENT-DATE)
ORDER BY CNTL_DTE DESC
END-EXEC.

and still i am getting same error,

DSNH312I E DSNHSMUD LINE 88 COL 37 UNDEFINED OR UNUSABLE HOST VARIABLE "WS-START-DATE"
DSNH312I E DSNHSMUD LINE 88 COL 56 UNDEFINED OR UNUSABLE HOST VARIABLE "WS-CURRENT-DATE" .

How do i solve this problem,plz privide solution?

babu_hi · Active User Joined: 11 Apr 2006 Posts: 66

problem got solved with change of pic clause..
Thanks for your valuble reply's