Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: 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 22:54.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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).

  4. #4
    Join Date
    Nov 2007
    Posts
    265
    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 12:39.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    Nov 2007
    Posts
    265
    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!

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 14:16. Reason: Change step 2)

  10. #10
    Join Date
    Nov 2007
    Posts
    265
    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;

  11. #11
    Join Date
    Nov 2007
    Posts
    265
    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!!

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)

  13. #13
    Join Date
    Nov 2007
    Posts
    265
    Do you think this query might be faster than a bunch of full outer joins... maybe?

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 18:08. Reason: Add Note: to item 2).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •