Help.. what is wrong with my statement?

db2user24 · #1 (**permalink**) 08-17-10, 11:59

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!!

Lenny77 · #2 (**permalink**) 08-17-10, 12:50

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

db2user24 · #3 (**permalink**) 08-17-10, 17:52

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!!

n_i · #4 (**permalink**) 08-17-10, 22:05

Quote:

Originally Posted by db2user24

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'

DB2Plus · #5 (**permalink**) 08-17-10, 22:35

Quote:

Originally Posted by db2user24

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

db2user24 · #6 (**permalink**) 08-18-10, 10:33

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)';

tonkuma · #7 (**permalink**) 08-18-10, 11:15

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.

db2user24 · #8 (**permalink**) 08-18-10, 12:34

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