Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
1
Erster Beitrag:
vor 7 Monaten, 3 Wochen
Beteiligte Autoren:
KenKnight

[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.


SELECT
SortOrder,
Label,
SubAmounts,
Amounts

FROM

(
SELECT
a.SortOrder,
a.Label,
SUM(a.SubAmounts) AS SubAmounts,
SUM(CAST(a.Amounts AS REAL)) AS Amounts

FROM
(

SELECT 1 AS SortOrder,
'Previous Statement Amount Due' AS Label,
0.0 AS SubAmounts,
CustomerAccountBilling.ActivityAmount AS Amounts
FROM CustomerAccountBilling
WHERE CustomerAccountBilling.CustomerAccountsID = 17
AND LOWER(CustomerAccountBilling.ActivityType) = 'statement billing'
AND CustomerAccountBilling.ActivityDate = LAST_DAY(ADD_MONTHS('20161101', -1))

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

) AS ChargeBacks

UNION ALL

SELECT 7 AS SortOrder,
' Charges & Fees' AS Label,
0.0 AS SubAmounts,
0.0 AS Amounts
FROM Dual

UNION ALL

SELECT 7 AS SortOrder,
' Charges & Fees' AS Label,
SUM(MerchantPaidFeeAmount) AS SubAmounts,
SUM(MerchantPaidFeeAmount) AS Amounts
FROM
( // Should be ripped straight from Charges & Fees
SELECT
SUM(ChargesAndFees.MerchantPaidFeeAmount) AS MerchantPaidFeeAmount

FROM
(
SELECT
SUM(CASE WHEN SendType IN ('SendClient','ReverseClient','BackoutClient') AND FeePMCOwesPB 0 THEN FeePMCOwesPB
ELSE 0.0
END) AS MerchantPaidFeeAmount
FROM CustomerAccount
JOIN TransactionHistory
ON TransactionHistory.AcctDBkey = CustomerAccount.AcctDBkey
AND TransactionHistory.PBEPaymentAccountID = CustomerAccount.EPaymentUniqueID
AND TransactionHistory.VendorDepositDate BETWEEN '20161101' AND '20161130'
AND TransactionHistory.VendorResponseType = 'A'
AND TransactionHistory.TestEnvironmentFlag = 0
WHERE CustomerAccount.CustomerAccountsID = 17

UNION ALL

SELECT
SUM(CASE LOWER(PaymentMethodType)
WHEN 'ach return' THEN TransactionHistory.FeePMCOwesforACHReturn
WHEN 'chargeback fee' THEN TransactionHistory.FeePMCOwesForChargeback
ELSE 0.0
END) AS MerchantPaidFeeAmount

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



) AS a

GROUP BY a.SortOrder, a.Label

) AS QryStatementSummary

ORDER BY QryStatementSummary.SortOrder, QryStatementSummary.Label


Thanks all!
Ken

Antworten:

Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.