All Forums Database
rupert160 131 posts Joined 09/10
05 Sep 2015
Stored Procedure (SELECTION FORM) EXPLAIN/SHOW/HELP

The documentation for 14 and 15 says that the following should be possible but I cannot get it to work. Any suggestions team?

REPLACE PROCEDURE financial.showObject()
DYNAMIC RESULT SETS 1
BEGIN
   DECLARE cus CURSOR with RETURN FOR 
   EXPLAIN SELECT * FROM dbc.tables;
   OPEN cus;
END;

this should work for EXPLAIN and HELP also but instead I get:
SPL1007:E(L6), Unexpected text 'EXPLAIN' in place of cursor SELECT statement.

 

dnoeth 4628 posts Joined 11/04
05 Sep 2015

The manual is for both Embedded SQL and Stored Procedures and clearly states that you cannot use EXPLAIN/SHOW/HELP in a SP:
http://www.info.teradata.com/htmlpubs/DB_TTU_15_00/index.html#page/SQL_Reference/B035_1148_015K/ch05.140.38.html
 
Have a look a Glenn McCall's article how to enable it:
Running Unsupported Queries from a Stored Procedure

Dieter

rupert160 131 posts Joined 09/10
05 Sep 2015

Yes but this seems a contradiction from the manual here:
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1148_112A/ch03.149.063.html
and here:
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1148_112A/ch03.149.070.html
Addmittedly it's a declare cursor...

dnoeth 4628 posts Joined 11/04
06 Sep 2015

The previous page:
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1148_112A/ch03.149.062.html

Invocation

Nonexecutable preprocessor declaration.

Embedded SQL only.

Dieter

rupert160 131 posts Joined 09/10
06 Sep 2015

Thanks for the followup Dieter. I'll do some more reading on Embedded SQL.

You must sign in to leave a comment.