PowerBuilder v12.1
Oracle 12C Database
Windows 7, 64 bit platfrom
I have a tab with two tabpages: parms and display.
On the PARMS tabpage, the users enter the paramemters for a report, the data is saved as a string.
On the DISPLAY tabpage, I am trying to replace the arguments in the SQL with the values from the PARMS tabpage.
In the pfc_retrieve event, I get the retrieval syntax from the DWO, replace the arguments with the values specified from the PARMS tabpage, Modify the DWO SQL, then do a retrieve.
The Modify has no errors, but when I call ldw.Retrieve (), I get a "Specify Retrieval Arguments" popup for the DWO!
Q: WHY is it not simply retrieving the data?
Code:
// Get the retrieval SQL
ls_select = ldw.Describe ("DataWindow.Table.Select")
ls_mod_string = 'DataWindow.Table.Select="' + ls_select + '"'
ls_status = ldw.Modify (ls_mod_string)
IF (ls_status = "") THEN
ll_count = ldw.Retrieve ()
END IF
Example:
ls_select = "
SELECT
'Access Facilities Revenue - '||DECODE(FA_ORDER.BILL_GROUP_CODE,'OSA','OSA','TSA','TSA','XXX') as customer_type,
BILL_MASTER.IC_NAME as customer_name,
NCTA_STATE.NAME as billing_market,
BILL_MASTER.USER_INVOICE_NUM as invoice_number,
BILL_MASTER.AMOUNT_DUE as invoice_amount,
'INVOICE' as transaction_type,
to_char(BILL_MASTER.BILL_DATE,'MM-DD-YYYY') as transaction_date
FROM
BILL_MASTER,
ncta_state,
FA_ORDER
WHERE
BILL_MASTER.ST_ID = NCTA_STATE.NCTA_STATE and
BILL_MASTER.BILL_TYPE = 'FA' AND
BILL_MASTER.BILL_FCCID = FA_ORDER.BILL_FCCID AND
BILL_MASTER.IC_CIC = FA_ORDER.IC_CIC AND
BILL_MASTER.CIC_LIST = FA_ORDER.CIC_LIST AND
BILL_MASTER.BAN = FA_ORDER.BAN AND
BILL_MASTER.BILL_FCCID >= :from_fccid AND
BILL_MASTER.BILL_FCCID <= :thru_fccid AND
BILL_MASTER.BILL_DATE >= :from_bill_date AND
BILL_MASTER.BILL_DATE <= :thru_bill_date"
ls_mod_string = "
DataWindow.Table.Select="SELECT
'Access Facilities Revenue - '||DECODE(FA_ORDER.BILL_GROUP_CODE,'OSA','OSA','TSA','TSA','XXX') as customer_type,
BILL_MASTER.IC_NAME as customer_name,
NCTA_STATE.NAME as billing_market,
BILL_MASTER.USER_INVOICE_NUM as invoice_number,
BILL_MASTER.AMOUNT_DUE as invoice_amount,
'INVOICE' as transaction_type,
to_char(BILL_MASTER.BILL_DATE,'MM-DD-YYYY') as transaction_date
FROM
BILL_MASTER,
ncta_state,
FA_ORDER
WHERE
BILL_MASTER.ST_ID = NCTA_STATE.NCTA_STATE and
BILL_MASTER.BILL_TYPE = 'FA' AND
BILL_MASTER.BILL_FCCID = FA_ORDER.BILL_FCCID AND
BILL_MASTER.IC_CIC = FA_ORDER.IC_CIC AND
BILL_MASTER.CIC_LIST = FA_ORDER.CIC_LIST AND
BILL_MASTER.BAN = FA_ORDER.BAN AND
BILL_MASTER.BILL_FCCID >= '026D' AND
BILL_MASTER.BILL_FCCID <= '026D' AND
BILL_MASTER.BILL_DATE >= To_Date('2/1/2018 00:00:00', 'mm/dd/yyyy hh24:MI:ss') AND
BILL_MASTER.BILL_DATE <= To_Date('3/1/2018 00:00:00', 'mm/dd/yyyy hh24:MI:ss')"
The ldw.retrieve() command generates this:
