[HFSQL] need help with some SQL

Startbeitrag von KenKnight am 27.02.2017 16:21

Hi All,

We've hit a big snag with some SQL against an HFSQL server that works fine elsewhere. It's also worth noting that if you trim down the number of references to the dual table, it will also work, but the second you add that additional dual union it blows up.

If anyone is interested in taking a look at this to see if they can find something wrong I would greatly appreciate it. I have setup a special account to a dummy database for this testing. So just shoot me an email at kenknight[at]helpconsulting[dot]net and I'll send you the credentials and address.

This is a bit of a large piece of SQL so if its too much I also understand.

UNION ALL
SELECT 1 AS SortOrder,
'Previous Statement Amount Due' AS Label,
CAST(NULL AS REAL) AS SubAmounts,
0.0 AS Amounts
FROM Dual

UNION ALL

SELECT 2 AS SortOrder,
'Payments' AS Label,
CAST(NULL AS REAL) AS SubAmounts,
0.0 AS Amounts
FROM Dual

UNION ALL

SELECT 2 AS SortOrder,
'Payments' AS Label,
CAST(NULL AS REAL) AS SubAmounts,
SUM(CustomerAccountBilling.ActivityAmount) AS Amounts
FROM CustomerAccount
JOIN CustomerAccountBilling
ON CustomerAccountBilling.CustomerAccountsID = CustomerAccount.CustomerAccountsID
AND LOWER(CustomerAccountBilling.ActivityType) = 'payment'
AND CustomerAccountBilling.ActivityDate BETWEEN '20161101' AND '20161130'
WHERE CustomerAccount.CustomerAccountsID = 17
GROUP BY SortOrder, Label

UNION ALL

SELECT 4 AS SortOrder,
'Processing Activity' AS Label,
CAST(NULL AS REAL) AS SubAmounts,
CAST(NULL AS REAL) AS Amounts
FROM Dual

UNION ALL

SELECT 5 AS SortOrder,
' Deposit Amount' AS Label,
0.0 AS SubAmounts,
CAST(NULL AS REAL) AS Amounts
FROM Dual

UNION ALL

SELECT 5 AS SortOrder,
' Deposit Amount' AS Label,

(SELECT SUM(TransactionHistory.AmountBilledPayer *
(CASE WHEN TransactionHistory.SendType IN ('ReverseClient', 'BackoutClient') THEN -1 ELSE 1 END))
FROM TransactionHistory
WHERE TransactionHistory.AcctDBkey = CustomerAccount.AcctDBkey
AND TransactionHistory.PBEPaymentAccountID = CustomerAccount.EPaymentUniqueID
AND TransactionHistory.VendorDepositDate BETWEEN '20161101' AND '20161130'
AND TransactionHistory.SendType IN ('SendClient', 'ReverseClient', 'BackoutClient')
AND TransactionHistory.TestEnvironmentFlag = 0
AND TransactionHistory.VendorResponseType = 'A'
) AS SubAmounts,
CAST(NULL AS REAL) AS Amounts
FROM CustomerAccount

WHERE CustomerAccountsID = 17
//Group by SortOrder, Label

UNION ALL

SELECT 6 AS SortOrder,
' Chargebacks & Reversals' AS Label,
0.0 AS SubAmounts,
CAST(NULL AS REAL) AS Amounts
FROM Dual

UNION ALL

SELECT 6 AS SortOrder,
' Chargebacks & Reversals' AS Label,
SUM(ChargeBacks.Amount) AS SubAmounts,
CAST(NULL AS REAL) AS Amounts
FROM (
SELECT
TransactionHistory.PaymentMethodType AS PaymentMethodType,
TransactionHistory.ACHReturnDate AS DatePosted,
TransactionHistory.PBAccountName AS AccountName,
TransactionHistory.ACHReturnDescription AS Reason,
TransactionHistory.ACHReturnAmount AS Amount
FROM CustomerAccount
JOIN TransactionHistory
ON TransactionHistory.AcctDBkey = CustomerAccount.AcctDBkey
AND TransactionHistory.PBEPaymentAccountID = CustomerAccount.EPaymentUniqueID
AND (TransactionHistory.ACHReturnReportDate BETWEEN '20161101' AND '20161130'
OR TransactionHistory.ChargebackReportDate BETWEEN '20161101' AND '20161130')
AND TransactionHistory.VendorResponseType = 'A'
AND TransactionHistory.TestEnvironmentFlag = 0
AND TransactionHistory.SendType IN ('SendClient','ReverseClient','BackoutClient')
AND LOWER(TransactionHistory.PaymentMethodType) IN ('ach return')

WHERE CustomerAccount.CustomerAccountsID = 17

UNION ALL

SELECT
TransactionHistory.PaymentMethodType AS PaymentMethodType,
TransactionHistory.ChargebackDateTime AS DatePosted,
TransactionHistory.PBAccountName AS AccountName,
TransactionHistory.ChargeBackResponseDescription AS Reason,
TransactionHistory.FeePMCOwesForChargeback AS Amount
FROM CustomerAccount
JOIN TransactionHistory
ON TransactionHistory.AcctDBkey = CustomerAccount.AcctDBkey
AND TransactionHistory.PBEPaymentAccountID = CustomerAccount.EPaymentUniqueID
AND (TransactionHistory.ACHReturnReportDate BETWEEN '20161101' AND '20161130'
OR TransactionHistory.ChargebackReportDate BETWEEN '20161101' AND '20161130')
AND TransactionHistory.VendorResponseType = 'A'
AND TransactionHistory.TestEnvironmentFlag = 0
AND TransactionHistory.SendType IN ('SendClient','ReverseClient','BackoutClient')
AND LOWER(TransactionHistory.PaymentMethodType) IN ('chargeback fee')
WHERE CustomerAccount.CustomerAccountsID = 17

FROM CustomerAccount
JOIN TransactionHistory
ON TransactionHistory.AcctDBkey = CustomerAccount.AcctDBkey
AND TransactionHistory.PBEPaymentAccountID = CustomerAccount.EPaymentUniqueID
AND TransactionHistory.ACHReturnReportDate BETWEEN '20161101' AND '20161130'
AND TransactionHistory.VendorResponseType = 'A'
AND TransactionHistory.TestEnvironmentFlag = 0
AND SendType IN ('SendClient','ReverseClient','BackoutClient')
AND LOWER(PaymentMethodType) IN ('ach return','chargeback fee')
WHERE CustomerAccountsID = 17

) AS ChargesAndFees

) AS ChargesAndFeesTotals
GROUP BY SortOrder, Label

UNION ALL

SELECT 3 AS SortOrder,
CustomerAccountBilling.Description AS Label,
CAST(NULL AS REAL) AS SubAmounts,
CustomerAccountBilling.ActivityAmount AS Amounts
FROM CustomerAccount
JOIN CustomerAccountBilling
ON CustomerAccountBilling.CustomerAccountsID = CustomerAccount.CustomerAccountsID
AND LOWER(CustomerAccountBilling.ActivityType) = 'miscellaneous'
AND CustomerAccountBilling.ActivityDate BETWEEN '20161101' AND '20161130'
WHERE CustomerAccount.CustomerAccountsID = 17