SQL-UDTF mit dynamischem SQL

[andreaspr@aon.at]

Hallo Dieter,

ja das geht gleich wie in RPG mit PREPARE & Co.
Du erstellst dir mit DECLARE .. deine Variablen.
Machst dann ein
Set Variable = 'Select ....';
usw.

lg Andreas

[dschroeder]

Hallo Andreas,
vielen Dank für die Antwort. Wenn ich dynamisch Daten lesen möchte, muss ich dann in der UDTF zwingend einen Cursor öffnen und mit Fetch abarbeiten?
Ich habe gestern mal eine sehr rudimentäre UDTF erstellt (ohne Dynamic), in der ich das Resultset direkt in der Return-Anweisung zurückgegeben habe. Ganz ohne Cursor:

create or replace function myLib/testUDTF()
returns table (schluessel decimal(15))
language sql
reads sql data
begin
return select distinct schluessel from testtable
        where contains(name, 'Franz* AND Mustermann*') = 1;
end;

In der obigen Funktion hätte ich das return-Statement gerne dynamisch. Aber das geht wahrscheinlich nicht, oder? Bisher hat es bei mir jedenfalls nicht geklappt.

Dieter

[Fuerchau]

Außer "Just for Fun" wüsste ich jetzt keine Anwendung dafür, da man den dynamischen SQL eben besser direkt in der Anwendung ausführt.
Da kann ich einfach mit Prepare, Execute, Open, Fetch, CLose arbeiten.

Und was den Set angeht, so gilt auch hier: keine Dynamic:

set Variable = (Select ..... fetch first 1 rows only);

[BenderD]

... Beispiel findest Du hier: https://www.google.de/url?sa=t&rct=j...7Xcja8T5-KXauU
- aber Vorsicht, die Beispiele sind zuweilen fehlerhaft (z.B.: Errorhandling)
D*B

[dschroeder]

... Beispiel findest Du hier: https://www.google.de/url?sa=t&rct=j...7Xcja8T5-KXauU
- aber Vorsicht, die Beispiele sind zuweilen fehlerhaft (z.B.: Errorhandling)
D*B

Vielen Dank. Das Handbuch scheint echt interessant zu sein. Ich habe gerade auch schon etwas gelesen. Leider kämpfe ich immer noch mit einigen Details.
Ich glaube, ich unterlege die UDTF doch einfach mit einem RPG-Programm. In der reinen SQL-Syntax scheint das ja schwierig zu sein.

[BenderD]

... Seite 25 rum hast Du doch ein komplettes Beispiel, das das tun sollte, was Du haben willst.

[dschroeder]

... Seite 25 rum hast Du doch ein komplettes Beispiel, das das tun sollte, was Du haben willst.

Das habe mir angesehen. Mir sind aber folgende Dinge nicht klar:

1. Muss ich einen CONTINUE HANDLER definieren, um herauszubekommen, wann meine Fetch-Schleife stoppen soll?
2. Wie gebe ich das gerade "gefetchte" Ergebnis zurück? Läuft das überhaupt genauso wie im RPG? Dort würde das RPG-Programm ja für jeden Fetch aufgerufen. Im SQL sagt mir der Syntaxchecker dagegen immer, die Funktion würde ein return benötigen. Wenn ich ein "return myKey" einbaue, sagt der Checker, dass Return ein Tabelle zurückgeben muss.

Wahrscheinlich sehe ich den Wald vor lauten Bäumen nicht.

In einem anderen Beispiel habe die Möglichkeit gefunden, eine GLOBAL TEMPORARY TABLE zu erzeugen und die ganze Tabelle dann mit Return zurückzugeben. (Ob das dynamisch geht, weiß ich noch nicht). Aber das hört sich erstmal irgendwie langsam an (Tabelle erzeugen und danach die Tabelle zurückgeben). Im Prinzip habe ich ja die direkte Select-Anweisung, um die Daten zu holen.

[dschroeder]

Mein letzter Versuch sieht jetzt so aus:

create or replace function SCR/BVS9AD_suchUDTF2(anw nvarchar(10000))
returns table (ad_rec_id decimal(15))

language sql
reads sql data

begin

prepare cmd from anw;
return anw;

end;

Die Funktion soll folgendermaßen aufgerufen werden:
select * from table(BVS9AD_suchUDTF2('select distinct sm_ad_rec from bvsadsuc')) as liste;

Leider hakt es schon beim Erstellen der Funktion wegen:
SQ20120 30 1 SQL-Tabellenfunktion muss ein Tabellenergebnis zurückgeben.

[BenderD]

Mein letzter Versuch sieht jetzt so aus:

create or replace function SCR/BVS9AD_suchUDTF2(anw nvarchar(10000))
returns table (ad_rec_id decimal(15))

language sql
reads sql data

begin

prepare cmd from anw;
return anw;

end;

Die Funktion soll folgendermaßen aufgerufen werden:
select * from table(BVS9AD_suchUDTF2('select distinct sm_ad_rec from bvsadsuc')) as liste;

Leider hakt es schon beim Erstellen der Funktion wegen:
SQ20120 30 1 SQL-Tabellenfunktion muss ein Tabellenergebnis zurückgeben.

... da fehlt der declare cursor noch, für selbigen machst du dann den prepare und open und gibst den cursor mit return zurück

[dschroeder]

Was heißt "den Cursor zurückgeben"? Also kein Fetch?
Etwa so:

create or replace function SCR/BVS9AD_suchUDTF2(anw nvarchar(10000))
returns table (ad_rec_id decimal(15))

language sql
reads sql data

begin

prepare cmd from anw;
declare c1 cursor for anw;
open c1;
return c1;
close c1;
end;

In der Zeile "declare c1 cursor for anw;" bekomme ich folgenden Fehler:
SQL0104 30 18 Position 9 Token C1 ungültig. Gültige Token: GLOBAL.

Irgendwie mag er das mit dem declare c1 nicht. Außerdem würde der close c1 ja nie ausgeführt, denke ich.

[B.Hauser]

Das funktioniert so nicht! So wie Du das vorhast kannst Du RESULT SETS ausgeben, jedoch keine UDTF. Bei der Ausgabe von RESULT SETS bleiben die Cursor geöffnet. In Folge-Aufrufen musst Du dafür sorgen, dass der CURSOR, sofern er nicht geschlossen ist, vor dem erneuten Öffenen zunächst geschlossen wird.

Für UDTFs kannst Du entweder eine temporäre Datei befüllen und diese dann im SELECT des RETURN-Statements ausgeben oder Du verwendest eine pipielined UDTF.
... etwa so:

Begin
   Declare SQLCODE as Integer Default 0;
   Declare YOURVAR as ....;
   Declare YOURSELECT as VarChar(1024) Default '';
   ... sonstige Variablen
   Declare C1 Cursor for DynSQL;

   Set YOURSELECT = 'Select .... '; 
   Prepare DYNSQL from YOURSELECT;
   Open C1;

   YOURLOOP: LOOP  Fetch Next From C1 into YourVar;
     IF SQLCODE = 100 Then Leave YourLoop;
     End IF;
     .....
     PIPE (YourVar);
   End Loop;
   Close C1;
   Return;
End;

Übrigens der Prepare darf nicht vor dem DECLARE CURSOR stehen, da alle DECLARE CURSOR Statements in der Declare-Section nach der Variablen Definition und vor den Handlern angegeben werden müssen.
Der PREPARE wie auch der OPEN, FETCH und CLOSE gehört in den Verarbeitungsteil der Prozedur.

[BenderD]

... ohne continue handler düst das Teil direkt beim ersten negativen SQLCODE ab, ohne dass Du noch die Chance hast etwas zu machen.
Die Beispiele für UDTFs sind weiter hinten und Du musst das kombinieren. Ich habe sowas zwar noch nicht gebraucht, aber ich würde einfach mal den Cursor nach dem open mit return zurückgeben.

D*B