| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-11-10, 21:47
|
|
Registered User
|
|
Join Date: Nov 2007
Posts: 248
|
|
|
Query that works at the command line but not in a sproc
|
|
This is so confusing.. I have the table SESSION.TMP_POPREADINGS with the following records --
db2 "select * from session.tmp_popreadings where readingval > 0"
PARENTID PARENTTYPE READINGVAL READINGDATETIME USAGE UMSR PERIODTYPE
----------- ------------------------ ------------------------ -------------------------- ------------------------ -------- ----------------------------------------------------------------
1713 Site +2.47554010700000E+003 2010-07-13-05.00.00.000000 +2.47554010700000E+002 kWh On Peak
1713 Site +1.03147504458333E+002 2010-07-13-05.00.00.000000 +1.03147504458333E+001 kW On Peak
1713 Site +7.66620035000000E+002 2010-07-13-05.00.00.000000 +3.06648014000000E+001 kWh Intermediate
1713 Site +3.19425014583333E+001 2010-07-13-05.00.00.000000 +1.27770005833333E+000 kW Intermediate
1713 Site +6.51420022000000E+002 2010-07-13-05.00.00.000000 +2.60568008800000E+001 kWh Intermediate
1713 Site +2.71425009166667E+001 2010-07-13-05.00.00.000000 +1.08570003666667E+000 kW Intermediate
1713 Site +9.00540039000000E+002 2010-07-13-05.00.00.000000 +1.80108007800000E+001 kWh Off Peak
1713 Site +3.75225016250000E+001 2010-07-13-05.00.00.000000 +7.50450032500000E-001 kW Off Peak
If I run this query ( am just using a bunch of full outer joins), I get the following result which is what I want --
db2 "SELECT OFFPKW.PARENTID, OFFPKW.READINGDATE, ONPKW.ONPEAKVALKW, ONPKW.ONPEAKUSAGEKW, ONPKWH.ONPEAKVALKWH, ONPKWH.ONPEAKUSAGEKWH, INTPKW.INTPEAKVALKW, INTPKW.INTPEAKUSAGEKW, INTPKWH.INTPEAKVALKWH, INTPKWH.INTPEAKUSAGEKWH, OFFPKW.OFFPEAKVALKW, OFFPKW.OFFPEAKUSAGEKW, OFFPKWH.OFFPEAKVALKWH, OFFPKWH.OFFPEAKUSAGEKWH FROM ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKW, SUM(USAGE) AS ONPEAKUSAGEKW FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'On Peak' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKW FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKWH, SUM(USAGE) AS ONPEAKUSAGEKWH FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'On Peak' AND UMSR = 'kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKWH ON ONPKW.READINGDATE = ONPKWH.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKW, SUM(USAGE) AS INTPEAKUSAGEKW FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Intermediate' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKW ON ONPKWH.READINGDATE = INTPKW.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKWH, SUM(USAGE) AS INTPEAKUSAGEKWH FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Intermediate' AND UMSR = 'kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKWH ON INTPKW.READINGDATE = INTPKWH.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKW, SUM(USAGE) AS OFFPEAKUSAGEKW FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Off Peak' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKW ON INTPKWH.READINGDATE = OFFPKW.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKWH, SUM(USAGE) AS OFFPEAKUSAGEKWH FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Off Peak' AND UMSR = 'kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKWH ON OFFPKW.READINGDATE = OFFPKWH.READINGDATE"
RESULT ---
PARENTID READINGDATE ONPEAKVALKW ONPEAKUSAGEKW ONPEAKVALKWH ONPEAKUSAGEKWH INTPEAKVALKW INTPEAKUSAGEKW INTPEAKVALKWH INTPEAKUSAGEKWH OFFPEAKVALKW OFFPEAKUSAGEKW OFFPEAKVALKWH OFFPEAKUSAGEKWH
----------- ----------- ------------------------ ------------------------ ------------------------ ------------------------ ------------------------ ------------------------ ------------------------ ------------------------ ------------------------ ------------------------ ------------------------ ------------------------
1713 07/13/2010 +1.03147504458333E+002 +1.03147504458333E+001 +2.47554010700000E+003 +2.47554010700000E+002 +5.90850023750000E+001 +2.36340009500000E+000 +1.41804005700000E+003 +5.67216022800000E+001 +3.75225016250000E+001 +7.50450032500000E-001 +9.00540039000000E+002 +1.80108007800000E+001
If I give the same query in a stored procedure --
SET selectTmpPopStatement = 'SELECT ONPKW.PARENTID, ONPKW.READINGDATE, ONPKW.ONPEAKVALKW, ONPKW.ONPEAKUSAGEKW, '
|| 'ONPKWH.ONPEAKVALKWH, ONPKWH.ONPEAKUSAGEKWH, INTPKW.INTPEAKVALKW, INTPKW.INTPEAKUSAGEKW, INTPKWH.INTPEAKVALKWH, '
|| 'INTPKWH.INTPEAKUSAGEKWH, OFFPKW.OFFPEAKVALKW, OFFPKW.OFFPEAKUSAGEKW, OFFPKWH.OFFPEAKVALKWH, OFFPKWH.OFFPEAKUSAGEKWH FROM '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKW, SUM(USAGE) AS ONPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''On Peak''' || ' AND UMSR = ' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKW FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKWH, SUM(USAGE) AS ONPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''On Peak''' || ' AND UMSR = ' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKWH ON ONPKW.READINGDATE = ONPKWH.READINGDATE FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKW, SUM(USAGE) AS INTPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Intermediate''' || ' AND UMSR = ' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKW ON ONPKWH.READINGDATE = INTPKW.READINGDATE '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKW, SUM(USAGE) AS INTPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Intermediate''' || ' AND UMSR =' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKW ON ONPKWH.READINGDATE = INTPKW.READINGDATE FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKWH, SUM(USAGE) AS INTPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Intermediate''' || ' AND UMSR =' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKWH ON INTPKW.READINGDATE = INTPKWH.READINGDATE FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKW, SUM(USAGE) AS OFFPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Off Peak''' || ' AND UMSR = ' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKW ON INTPKWH.READINGDATE = OFFPKW.READINGDATE FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKWH, SUM(USAGE) AS OFFPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Off Peak''' || ' AND UMSR =' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKWH ON OFFPKW.READINGDATE = OFFPKWH.READINGDATE ';
PREPARE s5 FROM selectTmpPopStatement;
BEGIN
DECLARE c5 CURSOR WITH RETURN FOR s5;
OPEN c5;
END;
It gives the following error --
SQL0104N An unexpected token "FROM" was found following "E) AS INTPEAKUSAGEKW". Expected tokens may include: "<table_expr>". SQLSTATE=42601
What am I doing wrong? Is there another way to write this query out... Thanks!!
|
Last edited by db2user24; 08-11-10 at 21:54.
|
08-11-10, 22:05
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
One thing that you're doing wrong is not formatting your SQL statements so that people don't have to melt their brains looking at them 10 o'clock at night.
|
|
08-11-10, 23:53
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
You forgot 'FULL OUTER JOIN '.
To find the place to be inserted the phrase, you should follow the advice of n_i(i.e. format your SQL statements).
|
|
08-12-10, 11:32
|
|
Registered User
|
|
Join Date: Nov 2007
Posts: 248
|
|
sorry about that.. formatting is not quite taking effect when I paste it here...seems like I had an extra line in selectTmpPopStatement so now I have this --
SET selectTmpPopStatement = 'SELECT ONPKW.PARENTID, ONPKW.READINGDATE, ONPKW.ONPEAKVALKW, ONPKW.ONPEAKUSAGEKW, '
|| 'ONPKWH.ONPEAKVALKWH, ONPKWH.ONPEAKUSAGEKWH, INTPKW.INTPEAKVALKW, INTPKW.INTPEAKUSAGEKW, INTPKWH.INTPEAKVALKWH, '
|| 'INTPKWH.INTPEAKUSAGEKWH, OFFPKW.OFFPEAKVALKW, OFFPKW.OFFPEAKUSAGEKW, OFFPKWH.OFFPEAKVALKWH, OFFPKWH.OFFPEAKUSAGEKWH FROM '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKW, SUM(USAGE) AS ONPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''On Peak''' || ' AND UMSR = ' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKW '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKWH, SUM(USAGE) AS ONPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''On Peak''' || ' AND UMSR = ' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKWH ON ONPKW.READINGDATE = ONPKWH.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKW, SUM(USAGE) AS INTPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Intermediate''' || ' AND UMSR =' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKW ON ONPKWH.READINGDATE = INTPKW.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKWH, SUM(USAGE) AS INTPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Intermediate''' || ' AND UMSR =' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKWH ON INTPKW.READINGDATE = INTPKWH.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKW, SUM(USAGE) AS OFFPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Off Peak''' || ' AND UMSR = ' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKW ON INTPKWH.READINGDATE = OFFPKW.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKWH, SUM(USAGE) AS OFFPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Off Peak''' || ' AND UMSR =' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKWH ON OFFPKW.READINGDATE = OFFPKWH.READINGDATE ';
and when I call the sproc, I still get SQL0104N An unexpected token "FROM" was found following "E) AS
INTPEAKUSAGEKW". Expected tokens may include: "<table_expr>".
SQLSTATE=42601
|
Last edited by db2user24; 08-12-10 at 11:39.
|
08-12-10, 12:38
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I had no syntax error from your newer code.
So, I guessed that your copy of the code was not exactly same as in your procedure
or you copied from different place.
I tested as followings:
1) copy the right side expression of "SET selectTmpPopStatement = '...';", then get the result of the expression by
VALUES '....';
2) Add test data by common-table expression, then execute the query, like:
(I checked syntax error, so values of test data were not important.)
WITH
/*SESSION.*/TMP_POPREADINGS
(PARENTID , READINGDATETIME , READINGVAL , USAGE , PERIODTYPE , UMSR) AS (
VALUES
(1 , CURRENT_TIMESTAMP , 10 , 11 , 'On Peak' , 'kW')
, (2 , CURRENT_TIMESTAMP , 20 , 21 , 'On Peak' , 'kWh')
, (3 , CURRENT_TIMESTAMP , 30 , 31 , 'Intermediate' , 'kW')
, (4 , CURRENT_TIMESTAMP , 40 , 41 , 'Intermediate' , 'kWh')
, (5 , CURRENT_TIMESTAMP , 50 , 51 , 'Off Peak' , 'kW')
, (6 , CURRENT_TIMESTAMP , 60 , 61 , 'Off Peak' , 'kWh')
)
-- copy the result of step 1) and replace "SESSION." with "/*SESSION.*/"
--
Actual results were too long to put all in one post.
So, I'll copy actual codes in following two posts.
|
|
08-12-10, 12:40
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
1) copy the right side expression of "SET selectTmpPopStatement = '...';", then get the result of the expression by
VALUES '....';
Code:
------------------------------ Commands Entered ------------------------------
VALUES 'SELECT ONPKW.PARENTID, ONPKW.READINGDATE, ONPKW.ONPEAKVALKW, ONPKW.ONPEAKUSAGEKW, '
|| 'ONPKWH.ONPEAKVALKWH, ONPKWH.ONPEAKUSAGEKWH, INTPKW.INTPEAKVALKW, INTPKW.INTPEAKUSAGEKW, INTPKWH.INTPEAKVALKWH, '
|| 'INTPKWH.INTPEAKUSAGEKWH, OFFPKW.OFFPEAKVALKW, OFFPKW.OFFPEAKUSAGEKW, OFFPKWH.OFFPEAKVALKWH, OFFPKWH.OFFPEAKUSAGEKWH FROM '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKW, SUM(USAGE) AS ONPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''On Peak''' || ' AND UMSR = ' || '''kW''' || ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKW '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKWH, SUM(USAGE) AS ONPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''On Peak''' || ' AND UMSR = ' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKWH ON ONPKW.READINGDATE = ONPKWH.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKW, SUM(USAGE) AS INTPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Intermediate''' || ' AND UMSR =' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKW ON ONPKWH.READINGDATE = INTPKW.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKWH, SUM(USAGE) AS INTPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Intermediate''' || ' AND UMSR =' || '''kWh''' || ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKWH ON INTPKW.READINGDATE = INTPKWH.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKW, SUM(USAGE) AS OFFPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Off Peak''' || ' AND UMSR = ' || '''kW''' || ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKW ON INTPKWH.READINGDATE = OFFPKW.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKWH, SUM(USAGE) AS OFFPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Off Peak''' || ' AND UMSR =' || '''kWh''' || ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKWH ON OFFPKW.READINGDATE = OFFPKWH.READINGDATE ';
------------------------------------------------------------------------------
1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT ONPKW.PARENTID, ONPKW.READINGDATE, ONPKW.ONPEAKVALKW, ONPKW.ONPEAKUSAGEKW, ONPKWH.ONPEAKVALKWH, ONPKWH.ONPEAKUSAGEKWH, INTPKW.INTPEAKVALKW, INTPKW.INTPEAKUSAGEKW, INTPKWH.INTPEAKVALKWH, INTPKWH.INTPEAKUSAGEKWH, OFFPKW.OFFPEAKVALKW, OFFPKW.OFFPEAKUSAGEKW, OFFPKWH.OFFPEAKVALKWH, OFFPKWH.OFFPEAKUSAGEKWH FROM ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKW, SUM(USAGE) AS ONPEAKUSAGEKW FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'On Peak' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKW FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKWH, SUM(USAGE) AS ONPEAKUSAGEKWH FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'On Peak' AND UMSR = 'kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKWH ON ONPKW.READINGDATE = ONPKWH.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKW, SUM(USAGE) AS INTPEAKUSAGEKW FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Intermediate' AND UMSR ='kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKW ON ONPKWH.READINGDATE = INTPKW.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKWH, SUM(USAGE) AS INTPEAKUSAGEKWH FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Intermediate' AND UMSR ='kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKWH ON INTPKW.READINGDATE = INTPKWH.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKW, SUM(USAGE) AS OFFPEAKUSAGEKW FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Off Peak' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKW ON INTPKWH.READINGDATE = OFFPKW.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKWH, SUM(USAGE) AS OFFPEAKUSAGEKWH FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Off Peak' AND UMSR ='kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKWH ON OFFPKW.READINGDATE = OFFPKWH.READINGDATE
1 record(s) selected.
|
|
08-12-10, 12:42
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
2) Add test data by common-table expression, then execute the query, like:
(I checked syntax error, so values of test data were not important.)
Code:
------------------------------ Commands Entered ------------------------------
WITH
/*SESSION.*/TMP_POPREADINGS
(PARENTID , READINGDATETIME , READINGVAL , USAGE , PERIODTYPE , UMSR) AS (
VALUES
(1 , CURRENT_TIMESTAMP , 10 , 11 , 'On Peak' , 'kW')
, (2 , CURRENT_TIMESTAMP , 20 , 21 , 'On Peak' , 'kWh')
, (3 , CURRENT_TIMESTAMP , 30 , 31 , 'Intermediate' , 'kW')
, (4 , CURRENT_TIMESTAMP , 40 , 41 , 'Intermediate' , 'kWh')
, (5 , CURRENT_TIMESTAMP , 50 , 51 , 'Off Peak' , 'kW')
, (6 , CURRENT_TIMESTAMP , 60 , 61 , 'Off Peak' , 'kWh')
)
SELECT ONPKW.PARENTID, ONPKW.READINGDATE, ONPKW.ONPEAKVALKW, ONPKW.ONPEAKUSAGEKW, ONPKWH.ONPEAKVALKWH, ONPKWH.ONPEAKUSAGEKWH, INTPKW.INTPEAKVALKW, INTPKW.INTPEAKUSAGEKW, INTPKWH.INTPEAKVALKWH, INTPKWH.INTPEAKUSAGEKWH, OFFPKW.OFFPEAKVALKW, OFFPKW.OFFPEAKUSAGEKW, OFFPKWH.OFFPEAKVALKWH, OFFPKWH.OFFPEAKUSAGEKWH FROM ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKW, SUM(USAGE) AS ONPEAKUSAGEKW FROM /*SESSION.*/TMP_POPREADINGS WHERE PERIODTYPE = 'On Peak' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKW FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKWH, SUM(USAGE) AS ONPEAKUSAGEKWH FROM /*SESSION.*/TMP_POPREADINGS WHERE PERIODTYPE = 'On Peak' AND UMSR = 'kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKWH ON ONPKW.READINGDATE = ONPKWH.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKW, SUM(USAGE) AS INTPEAKUSAGEKW FROM /*SESSION.*/TMP_POPREADINGS WHERE PERIODTYPE = 'Intermediate' AND UMSR ='kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKW ON ONPKWH.READINGDATE = INTPKW.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKWH, SUM(USAGE) AS INTPEAKUSAGEKWH FROM /*SESSION.*/TMP_POPREADINGS WHERE PERIODTYPE = 'Intermediate' AND UMSR ='kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKWH ON INTPKW.READINGDATE = INTPKWH.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKW, SUM(USAGE) AS OFFPEAKUSAGEKW FROM /*SESSION.*/TMP_POPREADINGS WHERE PERIODTYPE = 'Off Peak' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKW ON INTPKWH.READINGDATE = OFFPKW.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKWH, SUM(USAGE) AS OFFPEAKUSAGEKWH FROM /*SESSION.*/TMP_POPREADINGS WHERE PERIODTYPE = 'Off Peak' AND UMSR ='kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKWH ON OFFPKW.READINGDATE = OFFPKWH.READINGDATE;
------------------------------------------------------------------------------
PARENTID READINGDATE ONPEAKVALKW ONPEAKUSAGEKW ONPEAKVALKWH ONPEAKUSAGEKWH INTPEAKVALKW INTPEAKUSAGEKW INTPEAKVALKWH INTPEAKUSAGEKWH OFFPEAKVALKW OFFPEAKUSAGEKW OFFPEAKVALKWH OFFPEAKUSAGEKWH
----------- ----------- ----------- ------------- ------------ -------------- ------------ -------------- ------------- --------------- ------------ -------------- ------------- ---------------
1 2010-08-13 10 11 20 21 30 31 40 41 50 51 60 61
1 record(s) selected.
|
|
08-12-10, 12:57
|
|
Registered User
|
|
Join Date: Nov 2007
Posts: 248
|
|
thanks for the reply.. my post at 10:32 has the correct sql.. ( it matches the db2 query that I ran at the command line which works just fine). It's only when I try to execute the code in a stored procedure ,that it fails.. any idea why? I'm just trying to return the result set in a cursor. Do I have to change the format in the stored procedure? It's so odd because the query works just fine at the command line. Thanks!
|
|
08-12-10, 13:12
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
What result did you got, if you did same as my test?
1) copy the right side expression of "SET selectTmpPopStatement = '...';" in your stored procedure, then get the result of the expression by
db2 "VALUES '....'"
or
db2 "SELECT '...' FROM sysibm.sysdummy1"
2) Add test data by common-table expression, then execute the query, like:
(I checked syntax error, so values of test data were not important.
You can use other more practical data, if you want.)
db2 "WITH
/*SESSION.*/TMP_POPREADINGS
(PARENTID , READINGDATETIME , READINGVAL , USAGE , PERIODTYPE , UMSR) AS (
VALUES
(1 , CURRENT_TIMESTAMP , 10 , 11 , 'On Peak' , 'kW')
, (2 , CURRENT_TIMESTAMP , 20 , 21 , 'On Peak' , 'kWh')
, (3 , CURRENT_TIMESTAMP , 30 , 31 , 'Intermediate' , 'kW')
, (4 , CURRENT_TIMESTAMP , 40 , 41 , 'Intermediate' , 'kWh')
, (5 , CURRENT_TIMESTAMP , 50 , 51 , 'Off Peak' , 'kW')
, (6 , CURRENT_TIMESTAMP , 60 , 61 , 'Off Peak' , 'kWh')
)
-- copy the result of step 1) and replace "SESSION." with "/*SESSION.*/"
"
|
Last edited by tonkuma; 08-12-10 at 13:16.
Reason: Change step 2)
|
08-12-10, 14:50
|
|
Registered User
|
|
Join Date: Nov 2007
Posts: 248
|
|
I'm not sure how to change what I currently have in the sproc since I want it to return a cursor ... this is what I currently have ( that fails when it tries to open the cursor) ..
SET selectTmpPopStatement = 'SELECT ONPKW.PARENTID, ONPKW.READINGDATE, ONPKW.ONPEAKVALKW, ONPKW.ONPEAKUSAGEKW, '
|| 'ONPKWH.ONPEAKVALKWH, ONPKWH.ONPEAKUSAGEKWH, INTPKW.INTPEAKVALKW, INTPKW.INTPEAKUSAGEKW, INTPKWH.INTPEAKVALKWH, '
|| 'INTPKWH.INTPEAKUSAGEKWH, OFFPKW.OFFPEAKVALKW, OFFPKW.OFFPEAKUSAGEKW, OFFPKWH.OFFPEAKVALKWH, OFFPKWH.OFFPEAKUSAGEKWH FROM '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKW, SUM(USAGE) AS ONPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''On Peak''' || ' AND UMSR = ' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKW '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKWH, SUM(USAGE) AS ONPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''On Peak''' || ' AND UMSR = ' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKWH ON ONPKW.READINGDATE = ONPKWH.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKW, SUM(USAGE) AS INTPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Intermediate''' || ' AND UMSR =' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKW ON ONPKWH.READINGDATE = INTPKW.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKWH, SUM(USAGE) AS INTPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Intermediate''' || ' AND UMSR =' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKWH ON INTPKW.READINGDATE = INTPKWH.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKW, SUM(USAGE) AS OFFPEAKUSAGEKW '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Off Peak''' || ' AND UMSR = ' || '''kW'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKW ON INTPKWH.READINGDATE = OFFPKW.READINGDATE '
|| 'FULL OUTER JOIN '
|| '( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKWH, SUM(USAGE) AS OFFPEAKUSAGEKWH '
|| 'FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = ' || '''Off Peak''' || ' AND UMSR =' || '''kWh'''
|| ' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKWH ON OFFPKW.READINGDATE = OFFPKWH.READINGDATE ';
END IF;
PREPARE s5 FROM selectTmpPopStatement;
BEGIN
DECLARE c5 CURSOR WITH RETURN FOR s5;
OPEN c5;
END;
|
|
08-12-10, 15:06
|
|
Registered User
|
|
Join Date: Nov 2007
Posts: 248
|
|
Actually never mind, I solved it by doing this --
BEGIN
DECLARE c5 CURSOR WITH RETURN FOR SELECT OFFPKW.PARENTID, OFFPKW.READINGDATE, ONPKW.ONPEAKVALKW, ONPKW.ONPEAKUSAGEKW, ONPKWH.ONPEAKVALKWH, ONPKWH.ONPEAKUSAGEKWH, INTPKW.INTPEAKVALKW, INTPKW.INTPEAKUSAGEKW, INTPKWH.INTPEAKVALKWH, INTPKWH.INTPEAKUSAGEKWH, OFFPKW.OFFPEAKVALKW, OFFPKW.OFFPEAKUSAGEKW, OFFPKWH.OFFPEAKVALKWH, OFFPKWH.OFFPEAKUSAGEKWH FROM ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKW, SUM(USAGE) AS ONPEAKUSAGEKW FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'On Peak' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKW FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS ONPEAKVALKWH, SUM(USAGE) AS ONPEAKUSAGEKWH FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'On Peak' AND UMSR = 'kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS ONPKWH ON ONPKW.READINGDATE = ONPKWH.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKW, SUM(USAGE) AS INTPEAKUSAGEKW FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Intermediate' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKW ON ONPKWH.READINGDATE = INTPKW.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS INTPEAKVALKWH, SUM(USAGE) AS INTPEAKUSAGEKWH FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Intermediate' AND UMSR = 'kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS INTPKWH ON INTPKW.READINGDATE = INTPKWH.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKW, SUM(USAGE) AS OFFPEAKUSAGEKW FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Off Peak' AND UMSR = 'kW' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKW ON INTPKWH.READINGDATE = OFFPKW.READINGDATE FULL OUTER JOIN ( SELECT PARENTID, DATE(READINGDATETIME) AS READINGDATE, SUM(READINGVAL) AS OFFPEAKVALKWH, SUM(USAGE) AS OFFPEAKUSAGEKWH FROM SESSION.TMP_POPREADINGS WHERE PERIODTYPE = 'Off Peak' AND UMSR = 'kWh' GROUP BY PARENTID, DATE(READINGDATETIME)) AS OFFPKWH ON OFFPKW.READINGDATE = OFFPKWH.READINGDATE;
OPEN c5;
END;
As a string it was definitely interpreting it as something else.. thanks for your help anyways!!
|
|
08-12-10, 16:07
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
For your required output, this might be better.
Though, it is not equivalent to your query.
Code:
SELECT PARENTID
, DATE(READINGDATETIME) AS READINGDATE
, SUM(CASE
WHEN PERIODTYPE = 'On Peak'
AND UMSR = 'kW' THEN
READINGVAL
ELSE 0
END) AS ONPEAKVALKW
, SUM(CASE
WHEN PERIODTYPE = 'On Peak'
AND UMSR = 'kW' THEN
USAGE
ELSE 0
END) AS ONPEAKUSAGEKW
, SUM(CASE
WHEN PERIODTYPE = 'On Peak'
AND UMSR = 'kWh' THEN
READINGVAL
ELSE 0
END) AS ONPEAKVALKWH
, SUM(CASE
WHEN PERIODTYPE = 'On Peak'
AND UMSR = 'kWh' THEN
USAGE
ELSE 0
END) AS ONPEAKUSAGEKWH
, SUM(CASE
WHEN PERIODTYPE = 'Intermediate'
AND UMSR = 'kW' THEN
READINGVAL
ELSE 0
END) AS INTPEAKVALKW
, SUM(CASE
WHEN PERIODTYPE = 'Intermediate'
AND UMSR = 'kW' THEN
USAGE
ELSE 0
END) AS INTPEAKUSAGEKW
, SUM(CASE
WHEN PERIODTYPE = 'Intermediate'
AND UMSR = 'kWh' THEN
READINGVAL
ELSE 0
END) AS INTPEAKVALKWH
, SUM(CASE
WHEN PERIODTYPE = 'Intermediate'
AND UMSR = 'kWh' THEN
USAGE
ELSE 0
END) AS INTPEAKUSAGEKWH
, SUM(CASE
WHEN PERIODTYPE = 'Off Peak'
AND UMSR = 'kW' THEN
READINGVAL
ELSE 0
END) AS OFFPEAKVALKW
, SUM(CASE
WHEN PERIODTYPE = 'Off Peak'
AND UMSR = 'kW' THEN
USAGE
ELSE 0
END) AS OFFPEAKUSAGEKW
, SUM(CASE
WHEN PERIODTYPE = 'Off Peak'
AND UMSR = 'kWh' THEN
READINGVAL
ELSE 0
END) AS OFFPEAKVALKWH
, SUM(CASE
WHEN PERIODTYPE = 'Off Peak'
AND UMSR = 'kWh' THEN
USAGE
ELSE 0
END) AS OFFPEAKUSAGEKWH
FROM SESSION.TMP_POPREADINGS
GROUP BY
PARENTID
, DATE(READINGDATETIME)
|
|
08-12-10, 16:29
|
|
Registered User
|
|
Join Date: Nov 2007
Posts: 248
|
|
Do you think this query might be faster than a bunch of full outer joins... maybe?
|
|
08-12-10, 16:40
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
For your required output, this might be better.
Though, it is not equivalent to your query.
|
The reasons I thought that are...
1) It's simplicity.
2) If some data was not present(for example: no data with PERIODTYPE = 'On Peak' AND UMSR = 'kWh' for a PARENTID),
the result of your query(with full outer join) would be separated into more than two rows for a PARENTID.
Those data may not be present in your environment(or in your procedure), it would be better to remove such possibility.
Note: (This is a common consideration for full outer joins with more than three tables.)
To remove the possibility being separated into more than two rows,
use COALESCE in SELECT list and ON conditions, Like:
SELECT COALESCE(ONPKW.PARENTID , ONPKWH.PARENTID , INTPKW.PARENTID , INTPKWH.PARENTID , OFFPKW.PARENTID , OFFPKWH.PARENTID) AS PARENTID
...
FROM (...) AS ONPKW
FULL OUTER JOIN (...) AS ONPKWH
ON ONPKW.READINGDATE = ONPKWH.READINGDATE
FULL OUTER JOIN (...) AS INTPKW
ON COALESCE(ONPKW.READINGDATE , ONPKWH.READINGDATE) = INTPKW.READINGDATE
FULL OUTER JOIN (...) AS INTPKWH
ON COALESCE(ONPKW.READINGDATE , ONPKWH.READINGDATE , INTPKW.READINGDATE) = INTPKWH.READINGDATE
...
3) Probably, execution performance would be better than the query with full outer join.
|
Last edited by tonkuma; 08-12-10 at 17:08.
Reason: Add Note: to item 2).
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Query that works at the command line but not in a sproc
