Is there a way inside an insert select statement to re-use an intermediate value that is required in the computation of several columns? Or mus it be computed for anew for each column?

On Jan 17, 9:17*pm, Dvd Avins <dvdav... (AT) pobox (DOT) com> wrote:
SET a user variable.

For each row, the variable would need a different value. That value would then be used in the calculation of multiple columns. I can use SET within the INSERT SELECT in that way?

On Jan 18, 2:58*pm, Dvd Avins <dvdav... (AT) pobox (DOT) com> wrote:
Can you give an example of what it is you actually want to do.
Otherwise I will keep coming up with answers and you will say "but I
need to do...." and so on.

Something of this form:

INSERT INTO c SELECT
a.f AS f,
a.x * a.r / b.r AS x,
a.y * a.r / b.r AS y,
a.z * a.r / b.r AS z
FROM a, b
WHERE a.g != "p" AND b.g = "p" AND b.f = a.f

It's actually more involved, but that is the essence. I would like to only calculate a.r / b.r once for each row. The real situation's calculations are sufficiently intensive that a subquery would be better than running the form above.

INSERT INTO c SELECT
a.f AS f,
a.x * t.r AS x,
a.y * t.r AS y,
a.z * t.r AS z
FROM a, (SELECT
a.f AS f,
a.r / b.r AS r
FROM a, b
WHERE a.g != "p" AND b.g = "p" and a.f = b.f) t
WHERE t.f = a.f

Does that make sense? Does SQL syntax require b to be listed in the outer FROM clause?

I wrote a longish reply a few minutes ago, which Google said "would be available shortly." If I see what I'm typing now and I still don't see that reply, I'll re-create it.

OK, here goes again. I'm recreating an example. This time I'll save it in case Google eats it again.

My situation involves more complex calculation and relational logic than this example, but the essence is as follows:

INSERT INTO n SELECT
a.f AS f
a.x * a.r / b.r AS x,
a.y * a.r / b.r AS y,
a.t * a.r / b.r AS z
FROM m a, m b
WHERE a.g != "p" and b.g = "p" and b.f = a.f

I want to only calculate a.r / b.r once for each row. At this point, could do that is with a subquery, which I suppose is what I'll have to do, unless SET actually does make sense in this context.

INSERT INTO n SELECT
a.f AS f,
a.x * t.r AS x,
a.y * t.r AS y,
a.z * t.r AS z
FROM m a, (SELECT
c.f AS r,
c.r / b.r AS r
FROM m c, m b
WHERE c.g != "p" AND b.g = "p" and b.f = c.f) t
WHERE t.f = a.f

Am I right that I cannot share references (aliases) for m between the inner and outer queries?

The 5th line of the upper query should read:
a.z * a.r / b.r AS z

Dvd Avins <dvdavins (AT) pobox (DOT) com> wrote:
This is rather pointless. CSE (common subexpression elimination)
is a standard optimization and you should rely on the DBMS to make
use of it.

Besides that it's very unlikely that the floating point operations
in this query have any significant effect on query execution time
at all! A single disk seek will take more time than several 1000
floating point divisions. And this JOIN will probably result in
many, many random disk reads (aka seeks)


XL

Axel Schwenke:

Point taken, but from a developer's point of view, there's still reason
enough to define the calculation rule just once. Suppose the rule is
likely to change, you want to keep it in one place (just once), so
application maintenance won't cost more than strictly necessary.



--
Erick