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
 

 

Problem with Between predicate in declare cursor?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Tue May 13, 2008 5:40 pm    Post subject: Problem with Between predicate in declare cursor?
Reply with quote

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?
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue May 13, 2008 5:44 pm    Post subject:
Reply with quote

Code:
CNTL_DTE BETWEEN WS-START-DATE AND WS-CURRENT-DATE


You need a colon : to identify the host variables
Code:
CNTL_DTE BETWEEN :WS-START-DATE AND :WS-CURRENT-DATE
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue May 13, 2008 5:45 pm    Post subject:
Reply with quote

babu,

change ur cursor declaration as

Code:


BETWEEN :WS-START-DATE AND :WS-CURRENT-DATE



these are host variables
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue May 13, 2008 5:45 pm    Post subject:
Reply with quote

oops sorry craq ... i didnt c ur post ...
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Tue May 13, 2008 5:53 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue May 13, 2008 6:05 pm    Post subject:
Reply with quote

Did you define WS-START-DATE and WS-CURRENT-DATE in WS ?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue May 13, 2008 6:34 pm    Post subject:
Reply with quote

What are the definitions of ws-start-date and ws-current-date?
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed May 14, 2008 9:55 am    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed May 14, 2008 10:30 am    Post subject:
Reply with quote

problem got solved with change of pic clause..
Thanks for your valuble reply's
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 SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm


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