Teradata Error: something missing between Insert and Declare statements

PeterFrb
PeterFrb used Ask the Experts™
on
I have been haggling with this (seemingly) simple stored procedure (In the left margin, I have provided line numbers where errors occur):

Replace PROCEDURE MyProc()
BEGIN
	Insert into 
		MyTable
	Select
		*
    From
        ReferenceTable;
		
	Declare cur1 Cursor with return only For
	    Select
			*
	    From
	        MyTable;

	Open cur1;
END;

Open in new window


When I try to run this, I get the following error, between the two statements:

Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'Declare' keyword.'.

Open in new window


I have run the two processes individually, outside of a stored procedure, and they work.  Please help explain this mysterious error.  Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
Don't know Teradata very much, but going by other databases, I would guess the code needs to be:
Replace PROCEDURE MyProc()
	Declare cur1 Cursor with return only For
	    Select
			*
	    From
	        MyTable;
BEGIN
	Insert into 
		MyTable
	Select
		*
    From
        ReferenceTable;
		
	Open cur1;
END;

Open in new window

I don't believe you can declare a variable in the middle of the procedure.
PeterFrbData anslyst, tableau visualization developer, vb certified

Author

Commented:
I think you're right.  That was a new revelation to me.  But there is a rub here, that I discovered when I moved the Declare statement tot he top.  I want to create a volatile table: one whose shelf life dies with the termination of the procedure.  Given that this is true, the table I'm defining does not exist at the beginning of the procedure.  I therefore have to create the table before I can define it in a cursor.  That puts me in a bind: getting an error because the table doesn't yet exist, or getting an error because the declare statement does not come at the beginning.

Given this, I did some more research and found the following code, which does run, but it hasn't returned any results yet.  The routine compiles, and it even runs, which in itself is progress.  Yet, regardless of how I populate the my_sql statement, it returns 0 rows, and it doesn't open up a window the a data structure; it only sends a message, saying zero rows were returned.  If I can get this to do something meaningful, it would be an effective way to circumnavigate the double bind in which the previous method was putting me.


Replace  PROCEDURE CURSOR_SAMPLE()
BEGIN
	DECLARE my_sql VARCHAR(1000);
	DECLARE cursor_name CURSOR  FOR  my_statement;

	SET my_sql =  'SELECT Fields FROM Table WHERE MyField = 1;';

	PREPARE my_statement from my_sql;

	OPEN cursor_name;

END;

Open in new window

johnsoneSenior Oracle DBA

Commented:
Like I said, Teradata isn't my area of expertise.  I just saw the syntax didn't look correct.  From what I can see in this documentation, that looks correct.

Try changing this:

DECLARE cursor_name CURSOR  FOR  my_statement;

to this:

DECLARE cursor_name CURSOR WITH RETURN ONLY FOR  my_statement;

That is a guess, but matches the documentation that I found.

Also, are you sure that the query you are putting into the procedure will return rows?  Your original procedure has an insert and that is missing in the new one.
PeterFrbData anslyst, tableau visualization developer, vb certified

Author

Commented:
Thank you for researching into what is, for you, uncharted territory.  That's quite generous and kind.  I will try it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial