473,581 Members | 2,694 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Query from iSeries Data Transfer

I am trying to run a SQL query as follows:

SELECT INVMST.INUMBR, INVMST.IDESCR,
CASE WHEN SUM(INVCHS.CHQT 01) = 0 THEN SUM(INVCHS1.CHQ T01) ELSE
SUM(INVCHS.CHQT 01) END as QTY1,
CASE WHEN SUM(INVCHS.CHQT 02) = 0 THEN SUM(INVCHS1.CHQ T02) ELSE
SUM(INVCHS.CHQT 02) 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+Q TY3+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
3 10130
ah***@chebucto. ns.ca wrote:
I am trying to run a SQL query as follows:

SELECT INVMST.INUMBR, INVMST.IDESCR,
CASE WHEN SUM(INVCHS.CHQT 01) = 0 THEN SUM(INVCHS1.CHQ T01) ELSE
SUM(INVCHS.CHQT 01) END as QTY1,
CASE WHEN SUM(INVCHS.CHQT 02) = 0 THEN SUM(INVCHS1.CHQ T02) ELSE
SUM(INVCHS.CHQT 02) 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+Q TY3+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
ah***@chebucto. ns.ca wrote:
I am trying to run a SQL query as follows:

SELECT INVMST.INUMBR, INVMST.IDESCR,
CASE WHEN SUM(INVCHS.CHQT 01) = 0 THEN SUM(INVCHS1.CHQ T01) ELSE
SUM(INVCHS.CHQT 01) END as QTY1,
CASE WHEN SUM(INVCHS.CHQT 02) = 0 THEN SUM(INVCHS1.CHQ T02) ELSE
SUM(INVCHS.CHQT 02) 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+Q TY3+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.CHQT 01) = 0 THEN SUM(INVCHS1.CHQ T01) ELSE
SUM(INVCHS.CHQT 01) END as QTY1,
CASE WHEN SUM(INVCHS.CHQT 02) = 0 THEN SUM(INVCHS1.CHQ T02) ELSE
SUM(INVCHS.CHQT 02) END as QTY2,
SUM(CASE WHEN SUM(INVCHS.CHQT 01) = 0 THEN SUM(INVCHS1.CHQ T01) ELSE
SUM(INVCHS.CHQT 01) 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
On Mar 12, 10:57 am, Lennart <erik.lennart.j ons...@gmail.co mwrote:
<... 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
7785
by: Thad Rizzi | last post by:
My webserver is an iSeries and I want to run some PHP scripts on it using SQL to access data on the iSeries. Does anyone have any exapmles or resources on the most effective way to do this? I am rather green with PHP. Any help would be appreciated, Thad Rizzi
8
43072
by: VPaul | last post by:
Is there a web page out there like this one ... http://www.sql-tutor.com/sql_tutor/database.asp that would allow me to test some SQL to a DB2? In particular to an AS/400. I am learning and find that the commands, fuctions etc are different than going to an Access DB on my home PC. Another thing I am looking for is there a way to...
4
7687
by: erkki | last post by:
Hi, I have to move iSeries library to new DB2 database. Is there any easier way to do this moving operation than exporting all tables in iSeries into text files and then import them into DB2 Express? I am looking for same kind of function like db2move between two DB2 databases.
1
14909
by: Yanik Reid | last post by:
When I run the following vb.net code, I'm not able to acces the my database AMFLIB. I use to be able to access AMFLIB with the old ODBC driver from IBM iSeries Access but now I want to use the new managed IBM.Data.DB2.iSeries data provider from iSeries Access V5R3. When I remove Database=AMFLIB from the connection string, I can connect...
6
6697
by: MadMan2004 | last post by:
Hello all! I'm having a problem with a project I'm working on and I'd like to ask for anyone's input that might be helpful. I'm building a rather large front-end application connecting to an AS400 for the back end database and I'm experiencing slow response times when executing sql statements. Some select statement response times are bad. ...
3
2112
by: roberto | last post by:
I have a table (ORDERS) with this kind structure (this is not the really table): OrderId Product Amount Agent1 Agent2 Agent3 ________ _______ ______ ______ ______ ______ 000001 P1 20 AA BB XX 000002 P2 7 BB CC 000003 P2 12 CC
1
2191
by: kellyj00 | last post by:
We have clients who have not been exposed to SQL and have built a few thousand queries using the iSeries Query tool via their 5250 sessions. Is there any way to gather the code behind these queries that they have saved? Rather than opening each of them to reverse engineer how they work and rebuild them in SQL? Thank You! Jason
0
27239
by: maheshmohta | last post by:
Background Often while remodeling legacy application, one of the important tasks for the architects is to have an optimum usage of storage capabilities of database. Most of the legacy applications are constrained by the technology available at the time of their development and hence aren’t optimum as per current scenario. One of such cases is the...
1
2576
by: sqldood | last post by:
Hi, We have currently made an application on db2 express c but now our boss wants to transfer it on Power6/Iseries after the recent purchase. We want to migrate all the tables with the data, triggers, sequences intact to the iseries db2. Any help in this regard will be appreciated. Thanks, Mike.
0
7783
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8296
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8157
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6533
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5665
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3806
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2295
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1394
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1118
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.