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?
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
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.
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
