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

    Unanswered: Help.. what is wrong with my statement?

    I have a command that works just fine at the command line but when I execute it in a stored procedure, I get an error ---

    SET selectTmpStatement = '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 = ' || '''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
    FROM ' || insertTable ||
    ' GROUP BY PARENTID
    , DATE(READINGDATETIME)';


    I get this error --
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "
    ELSE 0 ". Expected tokens may include: "END". SQLSTATE=42601

    If I remove the last CASE block, it works just fine .. that is if I remove this block --


    SUM(CASE
    WHEN PERIODTYPE = ' || '''Off Peak''' ||
    ' AND UMSR = ' || '''kW''' || ' THEN
    USAGE
    ELSE 0
    END ) AS OFFPEAKUSAGEKW


    Can you find my syntax error? Thanks!!

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You have to count quotes:

    SUM(CASE
    WHEN PERIODTYPE = ' || '''Off Peak''' ||
    ' AND UMSR = ' || '''kW''' || ' THEN
    USAGE
    ELSE 0
    END ) AS OFFPEAKUSAGEKW

    ==>

    SUM(CASE
    WHEN PERIODTYPE = ' ' || 'Off Peak' || ' AND UMSR = ' || 'kW' || ' '
    THEN USAGE
    ELSE 0
    END ) AS OFFPEAKUSAGEKW


    Lenny

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    Not sure I understand what you mean.. I thought strings are supposed to be surrounded by double quotes ( or 2 single quotes) to escape them... based on that, I think my statement looks correct? Thanks!!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2user24 View Post
    I thought strings are supposed to be surrounded by double quotes ( or 2 single quotes) to escape them.
    I'm afraid that's not correct.

    String literals in DB2 are surrounded by single quotes. If there is a single quotation mark within such a literal, it needs to be escaped by adding another single quote to it.

    Examples of valid string literals:
    Code:
    select * from tab1 where col1 = 'Please do'
    select * from tab1 where col1 <> 'Please don''t'

  5. #5
    Join Date
    Jul 2009
    Posts
    150

    Wink 2 quotes, not 3

    Quote Originally Posted by db2user24 View Post
    Not sure I understand what you mean.. I thought strings are supposed to be surrounded by double quotes ( or 2 single quotes) to escape them... based on that, I think my statement looks correct? Thanks!!
    If || just 2 characters inside of the string, not a concat sign, you have to use double quotes, but you use tripple quotes.

    This is your mistake, I think:

    Code:
    SUM(CASE
    WHEN PERIODTYPE = ' || ''Off Peak'' ||
    ' AND UMSR = ' || ''kW'' || ' 
    THEN USAGE 
    ELSE 0
    END ) AS OFFPEAKUSAGEKW
    But it looks for me like concat sign, and Lenny wrote to you right statement.

    Kara
    Last edited by DB2Plus; 08-17-10 at 23:41.

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    thanks, I get this error now --

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "On Peak''" was found following "PERIODTYPE = '
    || ''". Expected tokens may include: "<space>". LINE NUMBER=217.
    SQLSTATE=42601


    With this code ---

    SET selectTmpStatement = ' SELECT PARENTID
    , DATE(READINGDATETIME) AS READINGDATE
    , SUM(CASE
    WHEN PERIODTYPE = ' || ''On Peak'' ||
    ' AND UMSR =' || ''kW'' || ' THEN
    READINGVAL
    ELSE 0
    END ) AS ONPEAKVALKW
    FROM SESSION.TMP_POPREADINGS
    GROUP BY PARENTID, DATE(READINGDATETIME)';

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    db2user24,

    I got no error from your original statement, like this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    BEGIN ATOMIC
    DECLARE selectTmpStatement , insertTable
            VARCHAR(1000);
    
    SET selectTmpStatement = '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 = ' || '''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
    FROM ' || insertTable ||
    ' GROUP BY PARENTID 
    , DATE(READINGDATETIME)';
    
    END!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    I tried to see the resulting string by modifying a little:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(insertTable) AS (
    VALUES '***table name***'
    )
    /* SET selectTmpStatement = */ SELECT
     '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 = ' || '''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
    FROM ' || insertTable ||
    ' GROUP BY PARENTID 
    , DATE(READINGDATETIME)'
    
    FROM test_data
    ;
    ------------------------------------------------------------------------------
    
    1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    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 = '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 FROM ***table name*** GROUP BY PARENTID , DATE(READINGDATETIME)
    
      1 record(s) selected.
    The resulting string(select statement) looks good,
    the formatted result looks like this:
    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 = '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
      FROM ***table name***
     GROUP BY
           PARENTID
         , DATE(READINGDATETIME)
    So, I thought that some previous statements had syntax error(s) or your copied code was not exactly same as you executed.

  8. #8
    Join Date
    Nov 2007
    Posts
    265
    Thanks for the reply. It's definitely weird when I execute this from a stored procedure .. as an experiment, I tried this :

    onpeakval, uomkw, etc are all declared variables that are pre-defined.


    SET selectTmpStatement = ' SELECT PARENTID
    , DATE(READINGDATETIME) AS READINGDATE
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR =''' || uomkw || ''' THEN
    READINGVAL
    ELSE 0
    END ) AS ONPEAKVALKW
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkw || ''' THEN
    USAGE
    ELSE 0
    END ) AS ONPEAKUSAGEKW
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkwh || ''' THEN
    READINGVAL
    ELSE 0
    END ) AS ONPEAKVALKWH
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkwh || ''' THEN
    USAGE
    ELSE 0
    END ) AS IPEAKUSAGEKWH
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkwh || ''' THEN
    USAGE
    ELSE 0
    END ) AS JPEAKUSAGEKWH
    FROM SESSION.TMP_POPREADINGS
    GROUP BY PARENTID, DATE(READINGDATETIME)';

    PREPARE s6 FROM selectTmpStatement;
    BEGIN
    DECLARE c6 CURSOR WITH RETURN FOR s6;
    OPEN c6;
    END;


    When I call the sproc, I get this result :

    Result set 1
    --------------

    PARENTID READINGDATE ONPEAKVALKW ONPEAKUSAGEKW ONPEAKVALKWH IPEAKUSAGEKWH JPEAKUSAGEKWH
    ----------- ----------- ------------------------ ------------------------ ------------------------ ------------------------ ------------------------
    1713 07/12/2010 +8.65296036800000E+003 +8.65296036800000E+002 +0.00000000000000E+000 +0.00000000000000E+000 +0.00000000000000E+000
    1713 07/13/2010 +1.37907456057500E+004 +1.37907456057500E+003 +7.42662032100000E+004 +7.42662032100000E+003 +7.42662032100000E+003
    1713 07/14/2010 +1.39031105847500E+004 +1.39031105847500E+003 +8.17668035700000E+004 +8.17668035700000E+003 +8.17668035700000E+003
    1713 07/15/2010 +1.47339005312500E+004 +1.47339005312500E+003 +7.86024031500000E+004 +7.86024031500000E+003 +7.86024031500000E+003
    1713 07/16/2010 +5.15056520975000E+003 +5.15056520975000E+002 +8.66862032100000E+004 +8.66862032100000E+003 +8.66862032100000E+003

    5 record(s) selected.

    Return Status = 0



    Now, if I add this block to the above -
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkwh || ''' THEN
    USAGE
    ELSE 0
    END ) AS KPEAKUSAGEKWH


    so it becomes :

    SET selectTmpStatement = ' SELECT PARENTID
    , DATE(READINGDATETIME) AS READINGDATE
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR =''' || uomkw || ''' THEN
    READINGVAL
    ELSE 0
    END ) AS ONPEAKVALKW
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkw || ''' THEN
    USAGE
    ELSE 0
    END ) AS ONPEAKUSAGEKW
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkwh || ''' THEN
    READINGVAL
    ELSE 0
    END ) AS ONPEAKVALKWH
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkwh || ''' THEN
    USAGE
    ELSE 0
    END ) AS IPEAKUSAGEKWH
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkwh || ''' THEN
    USAGE
    ELSE 0
    END ) AS JPEAKUSAGEKWH
    , SUM(CASE
    WHEN PERIODTYPE = ''' || onpeakval ||
    ''' AND UMSR = ''' || uomkwh || ''' THEN
    USAGE
    ELSE 0
    END ) AS KPEAKUSAGEKWH
    FROM SESSION.TMP_POPREADINGS
    GROUP BY PARENTID, DATE(READINGDATETIME)';

    PREPARE s6 FROM selectTmpStatement;
    BEGIN
    DECLARE c6 CURSOR WITH RETURN FOR s6;
    OPEN c6;
    END;


    When I call the stored procedure now, I get this --

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END ) AS
    KPEAKUSAG". Expected tokens may include: "<table_expr>". SQLSTATE=42601

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you declared enough length for selectTmpStatement?

  10. #10
    Join Date
    Nov 2007
    Posts
    265
    oh my gosh, thank you!!!!! I had it declared as VARCHAR(1000) and increased it to 3000.. it works!!

Posting Permissions

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