Issue with using cursor: Static cursor with Fetch to update values

Database
Enthusiast

Issue with using cursor: Static cursor with Fetch to update values

Hi,

 

I have trouble using cursor, can i get some help please?

 

Created table:

CREATE MULTISET TABLE TC_STATE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
CITY VARCHAR(20) CHARACTER NOT NULL,
STATE VARCHAR(20) CHARACTER NOT NULL
)
NO PRIMARY INDEX ;

 

Populated values:

INSERT INTO TC_STATE values ('New York', 'NY');
INSERT INTO TC_STATE values ('Buffalo', 'NY');
INSERT INTO TC_STATE values ('Austin', 'TX');
INSERT INTO TC_STATE values ('Dallas', 'TX');
INSERT INTO TC_STATE values ('San Antanio', 'TX');
INSERT INTO TC_STATE values ('Atlanta', 'GA');
INSERT INTO TC_STATE values ('Smyrna', 'GA');

 

Created PROC:

replace procedure p_test_cursor
(
IN IN_STATE varchar(50)
)
DYNAMIC RESULT SETS 1
BEGIN
DECLARE var_city varchar(50);
DECLARE var_state varchar(50);
--DECLARE Rev_city varchar(50);
DECLARE Rev_state varchar(50);

DECLARE cr_test cursor FOR
select city, state from TC_STATE
where STATE=IN_STATE
order by city desc
;

OPEN cr_test;

FETCH cr_test INTO var_city, var_state;

if(var_city='Buffalo' or var_city='New York') then

set Rev_state='New York';
else
set Rev_state=var_state;

end if;

update TC_STATE set state=Rev_state where city=var_city;

CLOSE cr_test;
END;

 

Executed the proc

 

call p_test_cursor('NY');

 

Expected Result:

I expected both the records (Buffalo and New York) will be be updated to New York state on one call.

 

Actual Result:

But only one record (New York) updated when i 1st executed the proc and when i re executed it, the 2nd record (Buffalo) updated.

Questions:

  1. Is the fetch not pulling all the records and processing one after the other? Why the 2 record are not updated in the same call?
  2. How to fetch all the records one after the other to process at one call?

 

Thank you in advance!

 


Accepted Solutions
Teradata Employee

Re: Issue with using cursor: Static cursor with Fetch to update values

First, cursor fetches are usually done in a loop.  I don't see a loop in here so it looks like you are just fetching one row, and by chance it happens to be for NY.  If the city is Buffalo, you update where the city is Buffalo, and if it is New York then you update where the city is New York.  Then you close the cursor and end the procedure.  It looks to me like this will always update just one row (or none), depending how the cursor's select result set gets built.

Second, I don't think you mean to be returning any data to the user so there are no dynamic result sets.

Third, this looks like a learning exercise, and it's fine for that, but in general this is a bad way to update a Teradata table.  It is much more efficient to simply "Update TC_STATE set state='New York' where city='Buffalo' or city='New York';" - that exercises Teradata's parallel processing power.  Ref.: http://developer.teradata.com/blog/georgecoleman.

1 ACCEPTED SOLUTION
10 REPLIES
Teradata Employee

Re: Issue with using cursor: Static cursor with Fetch to update values

First, cursor fetches are usually done in a loop.  I don't see a loop in here so it looks like you are just fetching one row, and by chance it happens to be for NY.  If the city is Buffalo, you update where the city is Buffalo, and if it is New York then you update where the city is New York.  Then you close the cursor and end the procedure.  It looks to me like this will always update just one row (or none), depending how the cursor's select result set gets built.

Second, I don't think you mean to be returning any data to the user so there are no dynamic result sets.

Third, this looks like a learning exercise, and it's fine for that, but in general this is a bad way to update a Teradata table.  It is much more efficient to simply "Update TC_STATE set state='New York' where city='Buffalo' or city='New York';" - that exercises Teradata's parallel processing power.  Ref.: http://developer.teradata.com/blog/georgecoleman.

Enthusiast

Re: Issue with using cursor: Static cursor with Fetch to update values

Thank you @GJColeman

 

Yes, i am doing this for learning. I changed my proc to loop using for loop as below. 'NY' is working fine but when i give 'TX' or 'GA', it throwing error as "Cannot place a null value in a NOT NULL field."

Looks like the else part is not setting the Rev_state=var_state. I dont understand why it would go to else part?

-----------------------------------------------------------------------

replace procedure p_test_cursor_for_loop
(
IN IN_STATE varchar(50)
)
--DYNAMIC RESULT SETS 1
BEGIN
DECLARE var_city varchar(50);
DECLARE var_state varchar(50);
DECLARE Rev_state varchar(50);
FOR for_test_loop AS
cr_test cursor FOR select city, state from TC_STATE where STATE=IN_STATE
DO
set var_city=for_test_loop.city;
if(var_city='Buffalo' or var_city='New York') then

set Rev_state='New York';

elseif

(var_city='Dallas' or var_city='Austin') then
set Rev_state='Texas';
else
set Rev_state=var_state;

end if;


update TC_STATE set state=Rev_state WHERE city=var_city;

END FOR;
END;

Teradata Employee

Re: Issue with using cursor: Static cursor with Fetch to update values

After the line:

  set var_city=for_test_loop.city;
I think you forgot to add the line:

  set var_state=for_test_loop.state;

Enthusiast

Re: Issue with using cursor: Static cursor with Fetch to update values

Brilliant! Thank you. That worked perfect.

 

One more loop i tried based on the example in the teradata manual is WHILE LOOP but it did not work for me. Can i get some help please? (https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1148-160K/bau...)

 

replace procedure p_test_cursor
(
IN IN_STATE varchar(50)
)
--DYNAMIC RESULT SETS 1
BEGIN
DECLARE var_city varchar(50);
DECLARE var_state varchar(50);
--DECLARE Rev_city varchar(50);
DECLARE Rev_state varchar(50);

DECLARE cr_test cursor FOR
select city from TC_STATE
where STATE=IN_STATE
order by city desc
;

OPEN cr_test;
WHILE (SQLCODE=0)
FETCH cr_test INTO var_city;
END WHILE;
if(var_city='Buffalo' or var_city='New York') then

set Rev_state='New York';
else
set Rev_state=var_state;

end if;

update TC_STATE set state=Rev_state where city=var_city;

CLOSE cr_test;
END;

 

Adding WHILE (SQLCODE=0) to the proc did not even let me compile it. why would that happen? where did i go wrong?

 

Thank you!

Teradata Employee

Re: Issue with using cursor: Static cursor with Fetch to update values

I don't see a problem with the WHILE offhand, but try moving the END WHILE to the line after the UPDATE statement and see if that works.

Enthusiast

Re: Issue with using cursor: Static cursor with Fetch to update values

I was missing DO after WHILE and i also moved the END WHILE after update as suggested. It updated the values as expected but the loop wouldn't exit and the program kept on running. so i had to kill it. After killing when i looked at the table, it updated the state values though, for NY as New York. why would it not exit the program?

 

replace procedure p_test_cursor_while_loop (IN IN_STATE varchar(50))

BEGIN

DECLARE var_city varchar(50);

DECLARE var_state varchar(50);

DECLARE Rev_state varchar(50);

DECLARE cr_test cursor FOR select city,state from TC_STATE where STATE=IN_STATE order by city desc;

 

OPEN cr_test;

WHILE (SQLCODE=0) do

FETCH cr_test INTO var_city,var_state;

if(var_city='Buffalo' or var_city='New York') then

set Rev_state='New York';

elseif

(var_city='Dallas' or var_city='Austin') then

set Rev_state='Texas'

else

set Rev_state=var_state; 

end if;

update TC_STATE set state=Rev_state where city=var_city;

END WHILE;

CLOSE cr_test;

END;

 

Teradata Employee

Re: Issue with using cursor: Static cursor with Fetch to update values

DO!  Duh! of course.  And I just realized now that you mention the loop that the UPDATE always sets SQLCODE to 0 if it succeeds, which of course it does, then back at the top of the loop SQLCODE is 0.  The fetch fails but the code doesn't catch that, just keeps doing the same update over and over.  So I think you need to add a label line before the WHILE, such as "WLoop:", then check SQLCODE after the FETCH and LEAVE WLoop if it's not 0.  You would also have a statement like "END WLoop;" following the END WHILE; that tells where to go when you leave the loop.

Enthusiast

Re: Issue with using cursor: Static cursor with Fetch to update values

@GJColeman I tried adding label but it is giving me parsing error on the line where i have the label (Wloop: ) and on the line where WHILE begins. 

 

replace procedure p_test_cursor_while_loop
(
IN IN_STATE varchar(50)
)
BEGIN
DECLARE var_city varchar(50);
DECLARE var_state varchar(50);
DECLARE Rev_state varchar(50);

DECLARE cr_test cursor FOR
select city,state from TC_STATE
where STATE=IN_STATE
order by city desc
;

OPEN cr_test;
WLoop:
WHILE (SQLCODE=0) do
FETCH cr_test INTO var_city,var_state;
IF SQLCODE <> 0 THEN
LEAVE LABEL1;
if(var_city='Buffalo' or var_city='New York') then

set Rev_state='New York';

elseif

(var_city='Dallas' or var_city='Austin') then
set Rev_state='Texas';
else
set Rev_state=var_state;

end if;
update TC_STATE set state=Rev_state where city=var_city;
END WHILE;
END Wloop;
CLOSE cr_test;

END;

Junior Contributor

Re: Issue with using cursor: Static cursor with Fetch to update values

 

REPLACE PROCEDURE p_test_cursor_while_loop
 (
   IN IN_STATE VARCHAR(50)
 )
BEGIN
   DECLARE var_city VARCHAR(50);
   DECLARE var_state VARCHAR(50);
   DECLARE Rev_state VARCHAR(50);
   
   DECLARE cr_test CURSOR FOR
   SELECT city,state FROM TC_STATE
   WHERE STATE=IN_STATE
   ORDER BY city DESC
   ;
   OPEN cr_test;
   
   WLoop:
   WHILE (SqlCode=0) DO
      FETCH cr_test INTO var_city,var_state;
      
      -- IF SQLCODE <> 0 THEN
      -- LEAVE LABEL1; -- missing END IF & wrong label name
      IF SqlCode <> 0 THEN LEAVE WLoop; END IF;
      
      IF(var_city='Buffalo' OR var_city='New York') THEN
         SET Rev_state='New York';
      ELSEIF (var_city='Dallas' OR var_city='Austin') THEN
         SET Rev_state='Texas';
      ELSE
         SET Rev_state=var_state;
      END IF;
      
      UPDATE TC_STATE SET state=Rev_state WHERE city=var_city;
   
   -- END WHILE;
   -- END Wloop; -- no END for a loop
   END WHILE WLoop; -- WLoop is optional

   CLOSE cr_test;
END;