Cursor within stored procedure

Database

Cursor within stored procedure

I am trying to use a cursor to select a value from one table and then use that value in a select statement to insert data into a second table.

 

CREATE PROCEDURE Outlier ()
BEGIN
DECLARE PlayerID INTEGER;
DECLARE OutTrip CURSOR FOR
SELECT Player_ID
FROM Trips order by PlayerID;
OPEN OutTrip;
WHILE (SQLCODE = 0)DO
FETCH NEXT OutTrip INTO PlayerID;
INSERT LastOffers
SELECT TOP 5 o.PlayerID, o.OfferID, o.OfferName, o.OfferStatus,
o.BookingStart, o.BookingEnd, oi.Template, oi.Copy, oi.Item,
ROW_NUMBER() OVER (Order By o.PlayerID) AS Offer_Seq
FROM Offer o
JOIN OfferItem oi on o.OfferID = oi.OfferID
WHERE oi.OfferCopy like '%Hotel%'
AND o.PlayerID = PlayerID
ORDER BY o.PlayerID, o.OfferID DESC;
END WHILE;
CLOSE OutTrip;
END;

 

 I am getting the error:  
[5526] SPL1027:E(L9), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like ';' between the 'OutTrip' keyword and the 'INTO' keyword.'.

 

I have years of SQL experience but am new to Teradata.  Thanks.

 

Craig

6 REPLIES
Teradata Employee

Re: Cursor within stored procedure

You need to precede "PlayerID" with a colon, e.g. "FETCH NEXT OutTrip INTO :PlayerID;" indicating it's a host variable.  Also in the Select staetment, "... WHERE ... AND o.PlayerID = :PlayerID".

On the other hand, this is not the most efficient way to do things in Teradata.  It is generally not a good idea to loop through a cursor doing updates/inserts if you can help it.  I have written a series of blog entries on this topic with lots of examples of how to convert such logic into single Set SQL statements, from easy to complex, at developer.teradata.com/blog/georgecoleman.

This might be be a start on a better approach:
INSERT LastOffers
SELECT TOP 5 o.PlayerID
    ,o.OfferID
    ,o.OfferName
    ,o.OfferStatus
    ,o.BookingStart
    ,o.BookingEnd
    ,oi.Template
    ,oi.Copy
    ,oi.Item
    ,ROW_NUMBER() OVER (Partition By o.PlayerID Order By o.OfferID DESC) AS Offer_Seq
FROM Offer o
JOIN Trips t
 ON   o.PlayerID = t.Player_ID
JOIN OfferItem oi
 ON   o.OfferID = oi.OfferID
WHERE oi.OfferCopy like '%Hotel%'
HAVING Offer_Seq < 6;

Highlighted
Teradata Employee

Re: Cursor within stored procedure

Oops! My editting of the copy-and-paste overlooked one thing:  the SELECT should not use "TOP 5!"  Just SELECT o.PlayerID, o.OfferID, ....

Re: Cursor within stored procedure

Select Failed.  (5876) Ordered Analytical Functions not allowed in HAVING clause.

Teradata Employee

Re: Cursor within stored procedure

Right. Change Having to Qualify.

Re: Cursor within stored procedure

This only gives me the top 5 rows for a single playerid whereas I am trying to get the top 5 rows for multiple playerid's

Teradata Employee

Re: Cursor within stored procedure

Did you remove the "TOP 5" phrase?

INSERT LastOffers
SELECT o.PlayerID
    ,o.OfferID
    ,o.OfferName
    ,o.OfferStatus
    ,o.BookingStart
    ,o.BookingEnd
    ,oi.Template
    ,oi.Copy
    ,oi.Item
    ,ROW_NUMBER() OVER (Partition By o.PlayerID Order By o.OfferID DESC) AS Offer_Seq
FROM Offer o
JOIN Trips t
 ON   o.PlayerID = t.Player_ID
JOIN OfferItem oi
 ON   o.OfferID = oi.OfferID
WHERE oi.OfferCopy like '%Hotel%'
QUALIFY Offer_Seq < 6;