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

Hi Peter

Firstly thank you for your continued help with this issue.

The code you supplied did run, but only because you had restricted it to d2.code = "smi1". My code runs with this restriction too.

I ran the following alteration on the code you sent through.


SELECT weekend, family, SUM(amount) AS owing
FROM (
SELECT w.weekend, d2.code AS family, d2.amount
FROM weekendings w
JOIN dr02 d2 ON (w.weekend >= d2.date AND d2.t1 <> "g")
OR (w.weekend >= d2.weekend AND d2.t1 = "g")
WHERE d2.amount IS NOT NULL -- AND d2.code = "smi1"
) X
GROUP BY weekend, family

It also crashes with a full error.

When I remove , SUM(amount) AS owing

It works, as does my original code.

dr02.Amount is a Decimal(12,2).


Running Explain as well gives the following results. It doesn't seem useful, did I miss something?

Stage Duration
stage/sql/starting 0.0000
stage/sql/Executing hook on transaction begin. 0.0000
stage/sql/starting 0.0000
stage/sql/checking permissions 0.0000
stage/sql/checking permissions 0.0000
stage/sql/Opening tables 0.0000
stage/sql/init 0.0000
stage/sql/System lock 0.0000
stage/sql/optimizing 0.0000
stage/sql/statistics 0.0000
stage/sql/preparing 0.0000
stage/sql/Creating tmp table 0.0000
stage/sql/executing 21.5766
stage/sql/end 0.0000
stage/sql/query end 0.0000
stage/sql/removing tmp table 0.2247
stage/sql/closing tables 0.0000
stage/sql/freeing items 0.0000
stage/sql/logging slow query 0.0000
stage/sql/cleaning up 0.0000

Options: ReplyQuote


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


Sorry, you can't reply to this topic. It has been closed.

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.