October 8, 2009 at 9:41 am
Hi,
I recently stumbled over your articles on using CTEs and wanted to start using them myself. However, I realized that when doing so, some data is missing in the result and I can't figure out why. Without knowing the reason for this inconsistency, I obviously can't use CTEs anymore, so your help would be greatly appreciated.
The script written using a temporary table looks like this (result is ok):
-- individual transactions ----------------------
SELECTA.konto_id, A.inhaber_nr
,zahlart = CAST(CASE WHEN B.lsv_ermaechtigung_datum IS NULL THEN 'ESR' ELSE 'LSV' END AS char(3))
,kauf_datum, erfassung_datum, betrag, jecas_bew_kopf_id
,ist_kauf = CASE WHEN A.bewegungstyp_id BETWEEN 1 AND 8
AND A.betrag > 0
AND A.ist_fraud_chargeback NOT IN (2, 5, 10, 15)
AND A.bewegungstyp_id <> 91
THEN 1 ELSE 0 END
INTO ##trx_im_zeitraum
FROM jemas_base..sales_fact A
LEFT JOIN jemas_base.dbo.konto_kreditdaten B ON B.konto_id = A.konto_id
WHERE vertragstyp_id = 11
AND ((B.lsv_ermaechtigung_datum IS NULL AND A.erfassung_datum >= '20090912')-- ESR-Konti
OR(B.lsv_ermaechtigung_datum IS NOT NULL AND A.erfassung_datum >= '20090917') )-- LSV-Konti
UNION
SELECTD.konto_id, D.inhaber_nr
,zahlart = CAST(CASE WHEN E.lsv_ermaechtigung_datum IS NULL THEN 'ESR' ELSE 'LSV' END AS char(3))
,kauf_datum, erfassung_datum, betrag, jecas_bew_kopf_id
,ist_kauf = 0
FROM jemas_base..fees_fact D
LEFT JOIN jemas_base.dbo.konto_kreditdaten E ON E.konto_id = D.konto_id
WHERE vertragstyp_id = 11
AND ((E.lsv_ermaechtigung_datum IS NULL AND D.erfassung_datum >= '20090912')-- ESR-Konti
OR(E.lsv_ermaechtigung_datum IS NOT NULL AND D.erfassung_datum >= '20090917') )-- LSV-Konti
-- aggregate transactions ----------------------
SELECTX.konto_id, X.zahlart, Y.kontostatus_id, Y.betreibungsstatus_id, Y.letzte_mahnstufe
,CAST(0 AS decimal(7,2)) AS saldo_letzter
,SUM(X.betrag) AS saldo_aenderung
,CAST(0 AS decimal(7,2)) AS saldo_dd_aktuell
,COUNT(DISTINCT jecas_bew_kopf_id) anz_trx
,COUNT(DISTINCT CASE WHEN X.ist_kauf = 1 THEN jecas_bew_kopf_id ELSE NULL END) AS anz_kaeufe
,MIN(X.erfassung_datum) min_edat, MAX(X.erfassung_datum) max_edat
INTO ##aktiv_im_zeitraum
FROM ##trx_im_zeitraum X
JOIN jemas_base..konto Y ON Y.konto_id = X.konto_id
GROUP BY X.konto_id, X.zahlart, Y.kontostatus_id, Y.betreibungsstatus_id, Y.letzte_mahnstufe
I rewrote this code to use a CTE instead of the temporary table ##aktiv_im_zeitraum:
DROP TABLE ##aktiv_im_zeitraum
;WITH trx_im_zeitraum AS
(SELECTA.konto_id, A.inhaber_nr
,zahlart = CAST(CASE WHEN B.lsv_ermaechtigung_datum IS NULL THEN 'ESR' ELSE 'LSV' END AS char(3))
,kauf_datum, erfassung_datum, betrag, jecas_bew_kopf_id
,ist_kauf = CASE WHEN A.bewegungstyp_id BETWEEN 1 AND 8
AND A.betrag > 0
AND A.ist_fraud_chargeback NOT IN (2, 5, 10, 15)
AND A.bewegungstyp_id <> 91
THEN 1 ELSE 0 END
FROM jemas_base..sales_fact A
LEFT JOIN jemas_base.dbo.konto_kreditdaten B ON B.konto_id = A.konto_id
WHERE vertragstyp_id = 11
AND ((B.lsv_ermaechtigung_datum IS NULL AND A.erfassung_datum >= '20090912')-- ESR-Konti
OR(B.lsv_ermaechtigung_datum IS NOT NULL AND A.erfassung_datum >= '20090917') )-- LSV-Konti
UNION
SELECTD.konto_id, D.inhaber_nr
,zahlart = CAST(CASE WHEN E.lsv_ermaechtigung_datum IS NULL THEN 'ESR' ELSE 'LSV' END AS char(3))
,kauf_datum, erfassung_datum, betrag, jecas_bew_kopf_id
,ist_kauf = 0
FROM jemas_base..fees_fact D
LEFT JOIN jemas_base.dbo.konto_kreditdaten E ON E.konto_id = D.konto_id
WHERE vertragstyp_id = 11
AND ((E.lsv_ermaechtigung_datum IS NULL AND D.erfassung_datum >= '20090912')-- ESR-Konti
OR(E.lsv_ermaechtigung_datum IS NOT NULL AND D.erfassung_datum >= '20090917') )-- LSV-Konti
)
SELECTX.konto_id, X.zahlart, Y.kontostatus_id, Y.betreibungsstatus_id, Y.letzte_mahnstufe
,CAST(0 AS decimal(7,2)) AS saldo_letzter
,SUM(X.betrag) AS saldo_aenderung
,CAST(0 AS decimal(7,2)) AS saldo_dd_aktuell
,COUNT(DISTINCT jecas_bew_kopf_id) anz_trx
,COUNT(DISTINCT CASE WHEN X.ist_kauf = 1 THEN jecas_bew_kopf_id ELSE NULL END) AS anz_kaeufe
,MIN(X.erfassung_datum) min_edat, MAX(X.erfassung_datum) max_edat
INTO ##aktiv_im_zeitraum
FROM trx_im_zeitraum X
JOIN jemas_base..konto Y ON Y.konto_id = X.konto_id
GROUP BY X.konto_id, X.zahlart, Y.kontostatus_id, Y.betreibungsstatus_id, Y.letzte_mahnstufe
The problem now is, that some values from jemas_base..sales_fact for certain customers (konto_id) are simply not making it into the output. Strangely enough, if I don't run the script on all customers (konto_id) but add another where clause restricting it to the customer that doesn't show all the values, the missing values are there. Are CTEs not able to handle large amounts of date properly?
Your help is greatly appreciated! Thank you very much!
Hanspeter
October 8, 2009 at 9:52 am
Sorry, problem solved or at least I could figure out it had nothing to do with using or not using the CTE.
regards
- spit
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply