SQL Query from iSeries Data Transfer

[ah077]

I am trying to run a SQL query as follows:

SELECT INVMST.INUMBR, INVMST.IDESCR,
CASE WHEN SUM(INVCHS.CHQT01) = 0 THEN SUM(INVCHS1.CHQT01) ELSE
SUM(INVCHS.CHQT01) END as QTY1,
CASE WHEN SUM(INVCHS.CHQT02) = 0 THEN SUM(INVCHS1.CHQT02) ELSE
SUM(INVCHS.CHQT02) END as QTY2,
SUM(QTY1+QTY2) as SUMQTY FROM ............etc

The actual query uses QTY3 through QTY12 with CASE statements as
well, I just chopped it down a bit for readability. My question is how
do I add a field to the query resutls that is the SUM of QTY+QTY2+...
+QTY12? The QTYX values are dependent on case statements for their
assigned values. The SUM(QTY1+QTY2+QTY3+etc.) statement gives an
error:

SQL0206 - Column QTY1 not in specified tables.

Cause ...... : QTY1 is not a column of table *N in *N, QTY1 is not a
column of any table or view that can be referenced. (etc.)

What is the correct SQL query way to return a field that is the SUM
of QTY1 through QTY12?

Thanks for any help!!

ASM

[Philip Wright]

ah***@chebucto.ns.ca wrote:

I am trying to run a SQL query as follows:

SELECT INVMST.INUMBR, INVMST.IDESCR,
CASE WHEN SUM(INVCHS.CHQT01) = 0 THEN SUM(INVCHS1.CHQT01) ELSE
SUM(INVCHS.CHQT01) END as QTY1,
CASE WHEN SUM(INVCHS.CHQT02) = 0 THEN SUM(INVCHS1.CHQT02) ELSE
SUM(INVCHS.CHQT02) END as QTY2,
SUM(QTY1+QTY2) as SUMQTY FROM ............etc

The actual query uses QTY3 through QTY12 with CASE statements as
well, I just chopped it down a bit for readability. My question is how
do I add a field to the query resutls that is the SUM of QTY+QTY2+...
+QTY12? The QTYX values are dependent on case statements for their
assigned values. The SUM(QTY1+QTY2+QTY3+etc.) statement gives an
error:

SQL0206 - Column QTY1 not in specified tables.

Cause ...... : QTY1 is not a column of table *N in *N, QTY1 is not a
column of any table or view that can be referenced. (etc.)

What is the correct SQL query way to return a field that is the SUM
of QTY1 through QTY12?

Could you SELECT QTY1..QTY12 into host variables and then SUM the host
variables?

-Philip

[Lennart]

ah***@chebucto.ns.ca wrote:

I am trying to run a SQL query as follows:

SELECT INVMST.INUMBR, INVMST.IDESCR,
CASE WHEN SUM(INVCHS.CHQT01) = 0 THEN SUM(INVCHS1.CHQT01) ELSE
SUM(INVCHS.CHQT01) END as QTY1,
CASE WHEN SUM(INVCHS.CHQT02) = 0 THEN SUM(INVCHS1.CHQT02) ELSE
SUM(INVCHS.CHQT02) END as QTY2,
SUM(QTY1+QTY2) as SUMQTY FROM ............etc

The actual query uses QTY3 through QTY12 with CASE statements as
well, I just chopped it down a bit for readability. My question is how
do I add a field to the query resutls that is the SUM of QTY+QTY2+...
+QTY12? The QTYX values are dependent on case statements for their
assigned values. The SUM(QTY1+QTY2+QTY3+etc.) statement gives an
error:

SQL0206 - Column QTY1 not in specified tables.

Cause ...... : QTY1 is not a column of table *N in *N, QTY1 is not a
column of any table or view that can be referenced. (etc.)

What is the correct SQL query way to return a field that is the SUM
of QTY1 through QTY12?

I dont know db2 for iseries so the following might not apply. Reason you
cant add QTY1, QTY2 etc is that the "rename" operation takes part after
the calculation, so QTY1 are not known by then.

Either sum using the case stmts:

SELECT INVMST.INUMBR, INVMST.IDESCR,
CASE WHEN SUM(INVCHS.CHQT01) = 0 THEN SUM(INVCHS1.CHQT01) ELSE
SUM(INVCHS.CHQT01) END as QTY1,
CASE WHEN SUM(INVCHS.CHQT02) = 0 THEN SUM(INVCHS1.CHQT02) ELSE
SUM(INVCHS.CHQT02) END as QTY2,
SUM(CASE WHEN SUM(INVCHS.CHQT01) = 0 THEN SUM(INVCHS1.CHQT01) ELSE
SUM(INVCHS.CHQT01) END + ... FROM

or encapsulate it in an inline view

SELECT INUMBR, IDESCR, QTY1, ..., QTY1+QTY2+... FROM (
<your query goes here>
) X

or in a CTE

WITH TMP (INUMBR, IDESCR, QTY1,...) as (
<your query goes here>
)
select TMP.*, QTE1+QTE2+... FROM TMP
HTH
/Lennart

[ah077]

On Mar 12, 10:57 am, Lennart <[e-mail]:
<... snip ...>

or in a CTE

WITH TMP (INUMBR, IDESCR, QTY1,...) as (
<your query goes here>
)
select TMP.*, QTE1+QTE2+... FROM TMP

HTH
/Lennart

Thanks a lot, this worked perfectly!

ASM

---