This sql statement dies after using more than 40GB Tempspace:
INSERT INTO dcp.cmp_mo_p_bab_kart ( currency, year,
figure, period, month_date, company, center, account,
value )
SELECT
CASE
WHEN src.currency IS null
THEN dest.currency
ELSE src.currency END AS currency ,
CASE
WHEN src.year IS null
THEN dest.year
ELSE src.year END AS year ,
CASE
WHEN src.figure IS null
THEN dest.figure
ELSE src.figure END AS figure ,
CASE
WHEN src.period IS null
THEN dest.period
ELSE src.period END AS period ,
CASE
WHEN src.month_date IS null
THEN dest.month_date
ELSE src.month_date END AS month_date ,
CASE
WHEN src.company IS null
THEN dest.company
ELSE src.company END AS company ,
CASE
WHEN src.center IS null
THEN dest.center
ELSE src.center END AS center ,
CASE
WHEN src.account IS null
THEN dest.account
ELSE src.account END AS account , COALESCE(src.value,0)
AS value
FROM dcp.t_mo_p_bab_kart src FULL OUTER JOIN (
SELECT DISTINCT d.currency AS currency , d.year AS
year , d.figure AS figure , d.period AS period ,
d.month_date AS month_date , d.company AS company
, d.center AS center , d.account AS account , d.value
AS value
FROM dcp.mo_p_bab_kart d , dcp.t_mo_p_bab_kart s
WHERE d.company = s.company and d.year = s.year and
d.period = s.period and d.month_date = s.month_date
and d.figure = s.figure and d.currency = s.currency
) dest ON src.company = dest.company AND src.center
= dest.center AND src.year = dest.year AND src.period
= dest.period AND src.month_date = dest.month_date
AND src.figure = dest.figure AND src.currency =
dest.currency AND src.account = dest.account
Table dcp.mo_p_bab_kart has 2129489 cards , dcp.t_mo_p_bab_kart 189424
cards,
Runstats ok.
Perhaps is it better to use UNION ALL or work with temporary table?
WHY?
--
Mit freundlichen Gruessen / Best regards
_______________________________
Joachim Mueller
