Re: File full error in simple select statement
Posted by: Derek McKinnon
Date: April 29, 2021 10:39PM

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;

Options: ReplyQuote


Subject
Written By
Posted
Re: File full error in simple select statement
April 29, 2021 10:39PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.