Re: File full error in simple select statement

[Derek McKinnon]

Mmmm. Running my full set of code is giving the error again, while the simple one that we have been working with is running fine.

Here is the full set of code.


DROP TABLE IF EXISTS d1;
DROP TABLE IF EXISTS fLeft;

-- SET LOCAL max_heap_table_size = 10000000000;
-- SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;

CREATE TEMPORARY TABLE d1 (CODE VARCHAR(7), PRIMARY KEY (CODE) ) ;
CREATE TEMPORARY TABLE fLeft (CODE VARCHAR(7), EndDate DATE, PRIMARY KEY (CODE) );
INSERT INTO d1 (CODE)
SELECT DISTINCT dr01.Code
FROM dr01 INNER JOIN children c ON dr01.Code = c.FamilyIDNo
WHERE (dr01.Bal = 0 OR dr01.Bal IS NULL) AND ((c.Monhrs IS NULL AND c.Tuehrs IS NULL AND c.Wedhrs IS NULL AND c.Thuhrs IS NULL AND c.Frihrs IS NULL) OR EndDate < CURDATE())
-- AND dr01.CODE = "CHE2"
;

INSERT INTO fLeft (CODE, EndDate)
SELECT CODE, MAX(DATE) AS EndDate
FROM dr02 WHERE t1 = "L" AND CODE <> '' AND Amount > 0 -- AND Code = "CHE2" -- <> ''
GROUP BY CODE;

SELECT
W.WeekEnd,
-- d2.Code as Family,
CASE WHEN d1.Code IS NULL THEN d2.Code ELSE CONCAT('ZZ_LEFT_ZERO_BAL_', d2.Code) END AS FAMILY,
CASE WHEN fLeft.Code IS NULL THEN 'N' ELSE (
CASE WHEN fLeft.EndDate < W.WeekEnd THEN 'N' ELSE 'Y' END
) END AS StillAttending,
SUM(IFNULL(d2.Amount, 0)) AS Owing,
CASE WHEN SUM(d2.Amount) >= 0 THEN 'Pos' ELSE 'Neg' END AS Balance,
fLeft.EndDate AS LastAttendanceDate

FROM weekendings W
INNER JOIN
dr02 d2
ON ((W.WeekEnd >= d2.Date AND d2.t1 <> "G") OR (W.WeekEnd >= d2.WeekEnd AND d2.t1 = "G"))
INNER JOIN
dr01
ON d2.Code = dr01.Code
LEFT OUTER JOIN
d1
ON d2.Code = d1.Code
LEFT OUTER JOIN
fLeft
ON d2.Code = fLeft.Code
-- WHERE dr01.CODE = "CHE2"
GROUP BY W.WeekEnd, d2.Code
ORDER BY W.WeekEnd DESC;