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,

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,

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?

WebFOCUS 7.6
Windows, All Outputs

Closed due to no response