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
 

 

How to Control WHERE cluse Variables in a 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: Wed Jun 25, 2008 5:22 pm    Post subject: How to Control WHERE cluse Variables in a Cursor?
Reply with quote

I have 15 cursors in my souce program and SELECT part for the all cursors are common but the WHERE cluse parts are different,Asper my requirement i need to construct the SINGLE CURSOR.Is there any idea to control WHERE CLAUSE variables.

I am implementing the Subroutine for to handle all the database enquiries in source programs and return the data into mainprogram for rest of the process.

For Example,I have 2 curosrs here,SELECT is common and WHERE is different,

EXEC SQL
DECLARE ARW_X9_CR_CURSOR CURSOR FOR
SELECT SEND_RT_NO,
RECV_RT_NO,
RESEND_IND,
FILE_RECV_CNT,
CREA_DTE,
CREA_TME,
CASH_LET_BUS_DTE,
REC_NUM,
ITEM_SEQ_NO,
CASH_LET_ID,
BUNDLE_ID
FROM ARW_X9
WHERE
ACCT_NO =: WS-CR-ACCOUNT
AND ITEM_AMT =: WS-CR-AMOUNT
AND PC_SER_NO =: WS-CR-MICR-PC
AND CREDIT_DEBIT_IND =: WS-DBL-CREDIT-IND
AND CASH_LET_BUS_DTE =: WS-DBL-BUSINESS-DATE
AND CASH_LET_ID =: WS-DBL-CASH-LET-ID
AND BUNDLE_ID =: WS-DBL-BUNDLE-ID
ORDER BY CASH_LET_BUS_DTE DESC
END-EXEC.


EXEC SQL
DECLARE ARW_X9_DB_CURSOR CURSOR FOR
SELECT SEND_RT_NO,
RECV_RT_NO,
RESEND_IND,
FILE_RECV_CNT,
CREA_DTE,
CREA_TME,
CASH_LET_BUS_DTE,
REC_NUM,
ITEM_SEQ_NO,
CASH_LET_ID,
BUNDLE_ID
FROM ARW_X9
WHERE PAYOR_RT_NO =: WS-DBL-ABA-NBR
AND ACCT_NO =: WS-DBL-ACCOUNT
AND ITEM_AMT = :WS-DBL-AMOUNT
AND PC_SER_NO = :WS-DBL-MICR-PC
AND CREDIT_DEBIT_IND = :WS-DBL-CREDIT-IND
ORDER BY CASH_LET_BUS_DTE DESC
END-EXEC.


Now I will club the both cursors

SELECT SEND_RT_NO,
RECV_RT_NO,
RESEND_IND,
FILE_RECV_CNT,
CREA_DTE,
CREA_TME,
CASH_LET_BUS_DTE,
REC_NUM,
ITEM_SEQ_NO,
CASH_LET_ID,
BUNDLE_ID
FROM ARW_X9
WHERE PAYOR_RT_NO =: WS-DBL-ABA-NBR
AND ACCT_NO =: WS-CR-ACCOUNT
AND ITEM_AMT =: WS-CR-AMOUNT
AND PC_SER_NO =: WS-CR-MICR-PC
AND CREDIT_DEBIT_IND =: WS-DBL-CREDIT-IND
AND CASH_LET_BUS_DTE =: WS-DBL-BUSINESS-DATE
AND CASH_LET_ID =: WS-DBL-CASH-LET-ID
AND BUNDLE_ID =: WS-DBL-BUNDLE-ID
AND ACCT_NO =: WS-DBL-ACCOUNT
AND ITEM_AMT =: WS-DBL-AMOUNT
AND PC_SER_NO =: WS-DBL-MICR-PC
AND CREDIT_DEBIT_IND =: WS-DBL-CREDIT-IND
ORDER BY CASH_LET_BUS_DTE DESC.


What i am looking here,i am passing the values for only WS-CR-ACCOUNT,WS-CR-AMOUNT,WS-CR-MICR-PC,WS-DBL-CREDIT-IND,WS-DBL-BUSINESS-DATE,WS-DBL-CASH-LET-ID,WS-DBL-BUNDLE-ID.Not for remaing variables.if i Initialize the varaibles then those contan default either Zero's or Spacess.

If i execute this query it won't returns any records because we did not pass data for some variables,then those are ZEROS and Spacess.When query exectre and search in table for records.finally it won't fetch any records bcz WS-DBL-ABA-NBR,WS-DBL-ACCOUNT. WS-DBL-MICR-PC WS-DBL-CREDIT-IND variables don't have any values.

Is there any solution to deselect some of the where cluse variables while executing the query in Dynemic SQL.

I am looking solution on either Static SQL or Dynemic Sql?Please tell youe ideas?
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


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

PostPosted: Wed Jun 25, 2008 5:30 pm    Post subject:
Reply with quote

You could use dynamic SQL along with it's additional overhead and programming. Or you could use multiple static cursors and fetches with a switch to control which cursor to open and fetch (my preferred solution). Since you are fetching the same columns for both cursors the remainder of the logic should be nearly the same for either cursor.
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Wed Jun 25, 2008 6:03 pm    Post subject: reply
Reply with quote

Try to write the cursor as

EXEC SQL
DECLARE ARW_X9_CR_CURSOR CURSOR FOR
SELECT SEND_RT_NO,
RECV_RT_NO,
RESEND_IND,
FILE_RECV_CNT,
CREA_DTE,
CREA_TME,
CASH_LET_BUS_DTE,
REC_NUM,
ITEM_SEQ_NO,
CASH_LET_ID,
BUNDLE_ID
FROM ARW_X9
WHERE
ACCT_NO =: WS-CR-ACCOUNT
AND ITEM_AMT =: WS-CR-AMOUNT
AND PC_SER_NO =: WS-CR-MICR-PC
AND CREDIT_DEBIT_IND =: WS-DBL-CREDIT-IND
AND CASH_LET_BUS_DTE >=: WS-DBL-BUSINESS-DATE1
AND AND CASH_LET_BUS_DTE <=: WS-DBL-BUSINESS-DATE2
AND CASH_LET_ID >=: WS-DBL-CASH-LET-ID1
AND CASH_LET_ID <=: WS-DBL-CASH-LET-ID2
AND BUNDLE_ID >=: WS-DBL-BUNDLE-ID1
AND BUNDLE_ID >=: WS-DBL-BUNDLE-ID2
AND PAYOR_RT_NO >=: WS-DBL-ABA-NBR1
AND PAYOR_RT_NO <=: WS-DBL-ABA-NBR2

ORDER BY CASH_LET_BUS_DTE DESC
END-EXEC.




NOW FOR QUERY1 give the values as


ACCT_NO =: WS-CR-ACCOUNT
ITEM_AMT =: WS-CR-AMOUNT
PC_SER_NO =: WS-CR-MICR-PC
CREDIT_DEBIT_IND =: WS-DBL-CREDIT-IND

for WS-DBL-BUSINESS-DATE1 and WS-DBL-BUSINESS-DATE2 give whatever u gave in WS-DBL-BUSINESS-DATE

for WS-DBL-CASH-LET-ID1 and WS-DBL-CASH-LET-ID2 give whatever u gave in WS-DBL-CASH-LET-ID

for WS-DBL-BUNDLE-ID1 and WS-DBL-BUNDLE-ID1 give whatever u gave in WS-DBL-BUNDLE-ID

In WS-DBL-CASH-LET-ID1 move low-values and in WS-DBL-CASH-LET-ID2 move high-values
same low and high values in WS-DBL-ABA-NBR1 and WS-DBL-ABA-NBR2




Now for query2 give the values as

move WS-DBL-ABA-NBR to WS-DBL-ABA-NBR1
WS-DBL-ABA-NBR2
ACCT_NO =: WS-CR-ACCOUNT
ITEM_AMT =: WS-CR-AMOUNT
PC_SER_NO =: WS-CR-MICR-PC
CREDIT_DEBIT_IND =: WS-DBL-CREDIT-IND


for WS-DBL-BUSINESS-DATE1 and WS-DBL-BUSINESS-DATE2 move low and high values

for WS-DBL-CASH-LET-ID1 and WS-DBL-CASH-LET-ID2 move low and high values

for WS-DBL-BUNDLE-ID1 and WS-DBL-BUNDLE-ID1 move low and high values
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed Jun 25, 2008 6:04 pm    Post subject:
Reply with quote

Thanks....Craq Giegerich,Could you please write pesudo code for this in Dynemic 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: Wed Jun 25, 2008 6:26 pm    Post subject:
Reply with quote

babu_hi wrote:
Thanks....Craq Giegerich,Could you please write pesudo code for this in Dynemic SQL.


You would have to check the manuals, I have only used dynamic sql once.
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 Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Protection Exception while move 0 to ... Kevin Vaz CICS 10 Tue Oct 18, 2016 4:19 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts Is it possible to pass variables of s... Csongor CLIST & REXX 2 Fri Jun 10, 2016 7:33 pm


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