Error Big query: Need Help

[2969]

hi,
i have this query and when executed in mySQL query window throws error that "Big Query - Query not completely stored in memory". Also executed thru app, the program justs hangs. I have checked all indexes, they all look good.

----------------------------------------------------
Select sum(qtk.span) as Linqty , qth.qtnum, qtk.qtlin, sum(qtk.lnqty) as lnqty,qtl.idesc,qtk.plies,qtk.untot,qtk.ptype,(qth.csnam)as selectcust,cus.grpcd,(qtk.span) as span, (qth.qtalt) as ID from qth,cus,qtk,qtl where cus.ccode = qth.ccode and qth.qtnum =qtk.qtnum and qth.qtor = 'OR' and qth.qtnum IN (Select qth.qtnum from qth where qth.invdt >= {d '2005-02-01'} AND qth.invdt <= {d '2005-03-17'}) And cus.grpcd like '%'group by qth.qtnum,qtl.idesc ,qtk.qtlin,
qtk.plies,qtk.untot,qtk.ptype,qth.csnam,cus.grpcd,qtk.span order by qtl.idesc

 

[r937]

you have sum(qtk.span) as Linqty in the SELECT, but also qtk.span as span in the SELECT, as well as qtk.span in the GROUP BY

that can't possibly give you anything meaningful, can it?

you want a sum for each different value of qtk.span???

also, you are cross-joining all rows to every row of the qtl table, i think you've omitted a join condition here

you don't need the LIKE condition for cus.grpid, because that will return all of them

finally, this --

and qth.qtnum
IN (Select qth.qtnum
from qth
where qth.invdt >= {d '2005-02-01'}
AND qth.invdt <= {d '2005-03-17'})

seems unnecessary, and may in fact involve two copies of the qth table, when you only want one

my suggestion is that you build your query up starting with just two tables correctly joined together, then add the third, then the fourth, at each point making sure you join the tables correctly

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[2969]

hello r937

1. i need sum a sum for each different value of qtk.span..
2. Yes you are right as i have ommitted a qtl join condition by mistake

I will work as you have suggested

 

[2969]

hello r937

i worked as you had suggested..I am using the follpwing query

Select sum(qtk.span) as Linqty , qth.qtnum, qtk.qtlin,sum(qtk.lnqty) as lnqty,qtl.idesc,qtk.plies,qtk.untot,qtk.ptype,(qth.csnam)as selectcust,cus.grpcd,(qtk.span) as span, (qth.qtalt) as ID from qth,cus,qtk, qtl where cus.ccode = qth.ccode and qth.qtnum =qtk.qtnum and qtk.qtlky = qtl.qtlky and qth.qtor = 'OR' and qth.invdt >= {d '2005-02-01'} AND qth.invdt <= {d '2005-03-17'} And cus.grpcd like '%'group by qth.qtnum ,qtk.qtlin,qtk.plies,qtk.untot,qtk.ptype,qth.csnam,cus.grpcd,qtk.span

the error I get "Got error 134 with Storage engine". This I get as soon as I add 1 more field to the query. If i remove qtl.idesc from the query, the query gives the result but when i add that field I start getting it.

 

[r937]

change your GROUP BY to this --

group
by qth.qtnum
, qtk.qtlin
, qtl.idesc
, qtk.plies
, qtk.untot
, qtk.ptype
, qth.csnam
, cus.grpcd
, qtk.span
, qth.qtalt

these are all the non-aggregate columns in the SELECT

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)