I need help with getting data from this SQL. When i put in the whole SQL i get no errors but no data. If i take part of the SQL only i can get it to return data. I'm thinking it has to do with the UNION but I don't know why. Any ideas?
Here is the statement:
ENGINE DB2 SET DEFAULT_CONNECTION WCONAQHA SQL DB2
SELECT A.*, (A.TOTALAMOUNTRECEIVED - a.USEDAMOUNT)*-1 As UnusedAmount FROM (SELECT TRANSACTION_ID, STATUS, PAYMENT_STATUS, SUM(INVOICEPAID) AS INVOICEPAID, SUM(TEMP.AMOUNTRECEIVED) AS TOTALAMOUNTRECEIVED, SUM(TEMP.SERVICEAMOUNT) AS TOTALSERVICECOST, SUM(TEMP.DISCOUNT) AS TOTALDISCOUNT, SUM(TEMP.TAX) AS TOTALTAX, SUM(TEMP.RUSHAMOUNT) AS TOTALRUSHAMOUNT, SUM(TEMP.INVOICED) AS INVOICED, SUM(TEMP.EXCESSHOLDAMOUNT) AS TOTALEXCESSHOLD, SUM(TEMP.EXCESSUSEDAMOUNT) AS TOTALEXCESSUSED, SUM(TEMP.REFUNDAMOUNT) AS TOTALREFUND , SUM(TEMP.UNUSEDAMOUNT) AS USEDAMOUNT,batch_id, batch_number, SUM(TEMP.SERVICEAMOUNT + TEMP.RUSHAMOUNT + TEMP.TAX - TEMP.DISCOUNT + TEMP.EXCESSHOLDAMOUNT + TEMP.REFUNDAMOUNT - TEMP.AMOUNTRECEIVED - TEMP.EXCESSUSEDAMOUNT - TEMP.INVOICED + INVOICEPAID) AS AMOUNTDUE FROM (SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS, 0.00 AS INVOICEPAID, 0.00 AS AMOUNTRECEIVED, SUM(TI.SERVICE_AMOUNT) AS SERVICEAMOUNT, SUM(TI.DISCOUNT) AS DISCOUNT, SUM(TI.TAX) AS TAX, SUM(TI.RUSH_AMOUNT) AS RUSHAMOUNT, 0.00 AS INVOICED, 0.00 AS EXCESSHOLDAMOUNT, 0.00 AS EXCESSUSEDAMOUNT, 0.00 AS REFUNDAMOUNT , 0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number FROM AQHPRDDAT/TRANSACTION_ITEM TI INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID = TI.TRANSACTION_ID INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND TI.IS_DELETED=0 GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number UNION ALL SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS, 0.00 AS INVOICEPAID, 0.00 AS AMOUNTRECEIVED, 0 AS SERVICEAMOUNT, 0 AS DISCOUNT, 0 AS TAX, 0 AS RUSHAMOUNT, 0.00 AS INVOICED, 0.00 AS EXCESSHOLDAMOUNT, 0.00 AS EXCESSUSEDAMOUNT, 0.00 AS REFUNDAMOUNT, (SUM(TI.SERVICE_AMOUNT) + SUM(TI.TAX) + SUM(TI.RUSH_AMOUNT) - SUM(TI.DISCOUNT)) AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number FROM AQHPRDDAT/TRANSACTION_ITEM TI INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID = TI.TRANSACTION_ID INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND TI.IS_DELETED=0 AND TI.STATUS = 100 GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number UNION ALL SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS, 0.00 AS INVOICEPAID, SUM(CASE WHEN P.MODE = 70800000006 THEN (P.AMOUNT_RECEIVED) ELSE P.AMOUNT_RECEIVED END) AS AMOUNTRECEIVED, 0.00 AS SERVICEAMOUNT, 0.00 AS DISCOUNT, 0.00 AS TAX, 0.00 AS RUSHAMOUNT, 0.00 AS INVOICED, 0.00 AS EXCESSHOLDAMOUNT, 0.00 AS EXCESSUSEDAMOUNT, 0.00 AS REFUNDAMOUNT , 0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number FROM AQHPRDDAT/PAYMENT P LEFT OUTER JOIN AQHPRDDAT/OTHER_PAYMENT OP ON P.PAYMENT_ID = OP.PAYMENT_ID INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID = P.TRANSACTION_ID INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND (OP.OTHER_PAY_TYPE IS NULL OR OP.OTHER_PAY_TYPE <> 71400000005 ) AND P.STATUS= 100 AND P.IS_DELETED=0 GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number UNION ALL SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS, SUM(P.AMOUNT_RECEIVED) AS INVOICEPAID, 0.00 AS AMOUNTRECEIVED, 0.00 AS SERVICEAMOUNT, 0.00 AS DISCOUNT, 0.00 AS TAX, 0.00 AS RUSHAMOUNT, 0.00 AS INVOICED, 0.00 AS EXCESSHOLDAMOUNT, 0.00 AS EXCESSUSEDAMOUNT, 0.00 AS REFUNDAMOUNT , 0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number FROM AQHPRDDAT/PAYMENT P INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID = P.TRANSACTION_ID INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID INNER JOIN AQHPRDDAT/INVOICE I ON P.INVOICE_ID = I.INVOICE_ID AND P.STATUS= 100 AND P.IS_DELETED=0 AND I.IS_DELETED=0 AND I.INVOICE_STATUS NOT IN ( 70) GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number UNION ALL SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS, 0.00 AS INVOICEPAID, 0.00 AS AMOUNTRECEIVED, 0.00 AS SERVICEAMOUNT, 0.00 AS DISCOUNT, 0.00 AS TAX, 0.00 AS RUSHAMOUNT, 0.00 AS INVOICED, SUM(CASE WHEN EH.TYPE = 71500000002 THEN EH.AMOUNT ELSE 0.00 END) AS EXCESSHOLDAMOUNT, SUM(CASE WHEN EH.TYPE = 71500000003 THEN EH.AMOUNT ELSE 0.00 END) AS EXCESSUSEDAMOUNT, 0.00 AS REFUNDAMOUNT , 0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number FROM AQHPRDDAT/EXCESS_HOLD EH INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID =EH.TRANSACTION_ID INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND EH.TYPE NOT IN (71500000004 , 71500000005 ) GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number UNION ALL SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS, 0.00 AS INVOICEPAID, 0.00 AS AMOUNTRECEIVED, 0.00 AS SERVICEAMOUNT, 0.00 AS DISCOUNT, 0.00 AS TAX, 0.00 AS RUSHAMOUNT, SUM(I.INVOICE_AMOUNT) AS INVOICED, 0.00 AS EXCESSHOLDAMOUNT, 0.00 AS EXCESSUSEDAMOUNT, 0.00 AS REFUNDAMOUNT , 0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number FROM AQHPRDDAT/INVOICE I INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID =I.TRANSACTION_ID INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND I.IS_DELETED=0 AND I.INVOICE_STATUS NOT IN ( 70) GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number UNION ALL SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS, 0.00 AS INVOICEPAID, 0.00 AS AMOUNTRECEIVED, 0.00 AS SERVICEAMOUNT, 0.00 AS DISCOUNT, 0.00 AS TAX, 0.00 AS RUSHAMOUNT, 0.00 AS INVOICED, 0.00 AS EXCESSHOLDAMOUNT, 0.00 AS EXCESSUSEDAMOUNT, SUM(PA.PAYOUT_AMOUNT) AS REFUNDAMOUNT , 0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number FROM AQHPRDDAT/PAYOUT PA INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID =PA.TRANSACTION_ID INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND (PA.PAYOUT_SOURCE= 71700000004 OR PA.PAYOUT_SOURCE= 71700000005) AND PA.PAYOUT_STATUS <> 430 AND PA.PAYOUT_STATUS <> 110 AND PA.IS_DELETED=0 GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number ) TEMP GROUP BY TRANSACTION_ID,STATUS,PAYMENT_STATUS, Batch_Id, Batch_Number) A WHERE BATCH_NUMBER in ('013059408');This message has been edited. Last edited by: SPetrey,
WebFOCUS 7.6 Windows, All Outputs
March 28, 2013, 05:09 AM
Michael_86
Does each of your individual select statements return results with the where clause for batch number added? i.e.
SELECT T.TRANSACTION_ID AS TRANSACTION_ID, T.STATUS AS STATUS, T.PAYMENT_STATUS AS PAYMENT_STATUS, 0.00 AS INVOICEPAID, 0.00 AS AMOUNTRECEIVED, 0.00 AS SERVICEAMOUNT, 0.00 AS DISCOUNT, 0.00 AS TAX, 0.00 AS RUSHAMOUNT, 0.00 AS INVOICED, 0.00 AS EXCESSHOLDAMOUNT, 0.00 AS EXCESSUSEDAMOUNT, SUM(PA.PAYOUT_AMOUNT) AS REFUNDAMOUNT , 0.00 AS UNUSEDAMOUNT, B.Batch_Id, B.Batch_Number FROM AQHPRDDAT/PAYOUT PA INNER JOIN AQHPRDDAT/TRANSACTION T ON T.TRANSACTION_ID =PA.TRANSACTION_ID INNER JOIN AQHPRDDAT/BATCH B ON T.BATCH_ID = B.BATCH_ID AND (PA.PAYOUT_SOURCE= 71700000004 OR PA.PAYOUT_SOURCE= 71700000005) AND PA.PAYOUT_STATUS <> 430 AND PA.PAYOUT_STATUS <> 110 AND PA.IS_DELETED=0 Where Batch_Number in ('013059408') GROUP BY T.TRANSACTION_ID, T.STATUS, T.PAYMENT_STATUS , B.Batch_Id, B.Batch_Number
and etc...
basically have you established that your where clause will bring back a result?This message has been edited. Last edited by: Michael_86,
Webfocus 7.7.03 Windows Excel
April 02, 2013, 05:12 PM
SPetrey
I have this giving me results now using the entire statement(s), but it has some performance issues. Perhaps because it is pulling such a large amount of data. It times out if i request a date range greater than 3 months. I'm using the follwoing to get the results out:
I changed the where statement -
WHERE RECEIVED_DATE BETWEEN '&SRECEIVEDATE' AND '&ERECEIVEDATE' ;
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS HLD1 END
TABLE FILE HLD1 PRINT * ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT &WFFMT.(,,,).Select type of display output. ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = endeflt, $ ENDSTYLE END
I guessed at this part and it does work but only on small date ranges.
Any suggestions on a more efficient way to do this?