0

Does anyone know how I can save the output of the “HELP VOLATILE TABLE” statement as a table that I can use in a query later on.

My goal is to save a list of all Volatile Tables that are currently present.

I tried to use the “HELP VOLATILE TABLE” in a CTE, but it doesn’t do the trick. It refuses to run. Any help is useful.

Update: It seems HELP/SHOW statements can only return data to the client. They can’t be used in a query.

It seems it is possible to write an external stored procedure in f.e. Java that FETCHES this data and INSERTS it into a Global Temporary Table.

My question is whether someone knows how to write said external stored procedure in JAVA, and knows how to import it and use it?

Eren
  • 73
  • 1
  • 2
  • 8
  • HELP / SHOW only return data to the client; they can't be used in a subquery / CTE. The "client" can be an external stored procedure (e.g. written in Java). – Fred Oct 07 '22 at 13:38
  • I am not sure if I understand what you mean. When I run this statement, it does return a table in the Terdata Result Set Viewer. My goal is to save that result in Table or at least use the result of this statement. – Eren Oct 07 '22 at 14:01
  • The result set is returned to the issuer of HELP/SHOW but is not directly available for further SQL processing within the database - analogous to the WITH RETURN ONLY TO CLIENT option for Stored Procedure dynamic result sets. But an external stored procedure can open a cursor for the HELP/SHOW result, then FETCH and INSERT the rows into a Global Temporary Table instance that could be referenced in subsequent queries. – Fred Oct 07 '22 at 14:48
  • Could you maybe give an example of a query on how to define said stored procedure that fetches and inserts the rows into a Global Temporary Table? I’ll gladly accept the solution. – Eren Oct 07 '22 at 14:56
  • You can't do it with a "query". You would need to separately write and compile an external stored procedure, then use Create Procedure (External Form) to install it. See https://docs.teradata.com/r/Teradata-VantageTM-SQL-External-Routine-Programming/June-2022/External-Stored-Procedure-Code-Examples/Executing-SQL-in-Java-External-Stored-Procedures – Fred Oct 07 '22 at 19:39
  • I wanted to ask if you know how to write said external stored procedure. It seems it is in written in Java. I am no Java developer, unfortunately. – Eren Oct 09 '22 at 10:09
  • There was an article from Glenn McCall about running unsupported SQL from a XSP: Don't know why it's gone from Teradata's website, but still available web archive: https://web.archive.org/web/20160812130400/http://developer.teradata.com/extensibility/articles/running-unsupported-queries-from-a-stored-procedure – dnoeth Oct 10 '22 at 08:20

1 Answers1

0

For those using SAS, this is easy to do.

PROC SQL NOPRINT;
CONNECT TO TERADATA ( &connect_string. );
SELECT *
FROM CONNECTION TO TERADATA 
(HELP VOLATILE TABLE); 
DISCONNECT FROM TERADATA;
QUIT;

For those using Python, the same can be done easily too.

import os
import teradatasql
import pandas as pd

with teradatasql.connect('{"host":"your_host_name"}', user="", password = "") as connect:
df = pd.read_sql("HELP VOLATILE TABLE", connect)
Eren
  • 73
  • 1
  • 2
  • 8