SQL-UDTF mit dynamischem SQL

[dschroeder]

Hallo,
mal eine Frage, ob das geht:

Ich möchte eine UDTF direkt in SQL schreiben (also ohne unterliegendes RPG-Programm). Die SQL UDTF soll als Parameter die auszuführende Select-Anweisung bekommen. Ich will immer nur eine Spalte zurückgeben (den Primärschlüssel).

Kann man dynamisches SQL überhaupt direkt in SQL ausführen?

Vielen Dank im Voraus.

Dieter

[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]

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.

Auf die Frage habe ich gewartet :-)
Es gibt leider doch einen Sinn: Wir setzen jetzt Omnifind ein und haben festgestellt, dass es da ein Problem gibt, das auch die IBM Hotline nicht lösen kann: Die contains-Funktion von Omnifind lässt sich nicht performant mit anderen Bedingungen verknüpfen, wenn die Verknüpfungsbedingung ein OR ist.
Beispiel:
... where contains(name, 'müller' AND 'hans') = 1 and datum < %date; => Das läuft performant

... where contains(name, 'müller' AND 'hans') = 1 or datum < %date; => Das läuft überhaupt nicht performant (es sei denn, mann hat nur ein paar hundert Datensätze).

Da ich in meinen SQL-Abfrage aber ein OR benötige, möchte ich problematische where-Teile in eine UDTF auslagern. Die kann ich dann ja wieder mit OR verknüpfen.

[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]

... 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

[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