By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

SQL Query from iSeries Data Transfer

P: n/a
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

Mar 12 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Mar 12 '07 #2

P: n/a
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

Mar 12 '07 #3

P: n/a
On Mar 12, 10:57 am, Lennart <erik.lennart.jons...@gmail.comwrote:
<... 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

---

Mar 12 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.