Inserting values in to netezza using a stored procedure

Question

I'm working with netezza database and have a requirement to insert a Y flag for stores in California. I wrote the below procedure

CREATE OR REPLACE PROCEDURE MY_NEW_PROCEDURE() RETURNS BOOL
EXECUTE AS OWNER LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
    rec RECORD;
BEGIN
FOR rec in SELECT * from test_table 
LOOP
if rec.state_code ='CA'
EXECUTE IMMEDIATE 'INSERT INTO test_table (california_stores)' || 'values('y')';
END LOOP;
END;
END_PROC;

when I call the procedure using call MY_NEW_PROCEDURE() I get an error at line EXECUTE IMMEDIATE. I'm not sure what change I need to make here.

I only want to insert a flag based on store location. If stored procedure is not the way. I would welcome other ways of accomplishing this as well. Thanks. — karthikc, Aug 11 '14 at 15:32
Looks like you need to escape your single quotes around the 'y'. Probably should be values(''y'')'; — Shaun Peterson, Aug 12 '14 at 00:15
@ShaunPeterson I escaped the Y and executed it. I am getting the below error — karthikc, Aug 12 '14 at 17:15

score 0 · Answer 1 · answered Aug 15 '14 at 05:48

Don't know about netezza, but below is the procedure I used to test this (using SQL developer).

Works fine for me, although it would make more sense to update the row to set california_stores to 'Y'rather than insert a new row with california_stores = 'Y' for each calafornia store that you have.....

CREATE OR REPLACE PROCEDURE "MY_NEW_PROCEDURE" as
    rec test_table2%rowtype;
BEGIN
   FOR rec in (SELECT * from test_table2) LOOP
      if rec.state_code = 'CA' then
         EXECUTE IMMEDIATE 'INSERT INTO test_table2 (california_stores)' || 'values(''y'')';
      end if;
   END LOOP;
END;

score 0 · Answer 2 · answered Aug 24 '14 at 16:24

You didn't post the second error, but it looks to me like your insert statement isn't going to do what you want anyway. If the rec variable contains the attribute state_code and you're inserting a single value to test_table then the record will simply be empty except for a 'Y' in california_stores.

I'm going to guess that you're getting an error now either because of the spacing in the insert statement insert into test_table (california_stores)values('y') or because you didn't terminate the execute statement with a semicolon. The plsql for that line should be

execute immediate 'insert into test_table (california_stores) values (''y'');';

Inserting values in to netezza using a stored procedure

2 Answers2