CREATE FUNCTION mit select im Bauch ?

[cheffe1008]

Hallo zusammen:

Ich möchte ein SQL schreiben, das mir eine Liste ausgibt mit je einer Zeile für einen Artikel, hinter dem alle Lieferanten in dieser Zeile stehen.


Die Voraussetzungen schaffen:
CREATE SCHEMA CARSTEN IN ASP 1

CREATE TABLE CARSTEN/TABLE1 (ARTNR CHAR (5 ) NOT NULL WITH DEFAULT, LIEFNR CHAR (5 ) NOT NULL WITH DEFAULT)

INSERT INTO CARSTEN/TABLE1 (ARTNR, LIEFNR) VALUES('A', '1')
INSERT INTO CARSTEN/TABLE1 (ARTNR, LIEFNR) VALUES('A', '2')
INSERT INTO CARSTEN/TABLE1 (ARTNR, LIEFNR) VALUES('A', '3')
INSERT INTO CARSTEN/TABLE1 (ARTNR, LIEFNR) VALUES('B', '1')
INSERT INTO CARSTEN/TABLE1 (ARTNR, LIEFNR) VALUES('B', '2')
INSERT INTO CARSTEN/TABLE1 (ARTNR, LIEFNR) VALUES('C', '1')

Ein Select liefert nun folgende Daten:

select * from carsten/table1
ARTNR LIEFNR
----- ------
000001 A 1
000002 A 2
000003 A 3
000004 B 1
000005 B 2
000006 C 1

Dazu habe ich mir überlegt eine Funktion zu schreiben, an die ich die Artikel Nr. übergebe, die dann einen select macht und die Lieferanten einliest cancatiniert und zurückgibt. Das aufrufende Select sollte so aussehen: select artnr, lieferenten(artnr) from carsten/table1 order by artnr

Geht das überhaupt: In eine CREATE FUNKTION ein SELECT einzubauen?

Das Ergebnis sollte dann so aussehen:
A 1 2 3
B 1 2
C 1


Viele Grüße Carsten

[Fuerchau]

Insoweit kein Problem.
Definiere eine Variable, die nur lang genug ist, z.B. varchar(256) und mach in einer Schleife dann einen

set myvar = trim(myvar) concat ' ' conact mylinr;

Beachte bei der Größe nur die maximale Anzahl möglicher Lieferanten.
gib myvar dann als Return-Wert zurück.

[cheffe1008]

Hallo,

Danke für Ihre Antwort.

Ich habe mir jetzt mal das IBM Buch "SQL Programming" zu Gemüte geführt.

Dort drin gibt kein einziges Beispiel was einen SELECT in einer Funktion absetzt. In Proceduren dagegen funktioniert das wohl mit Cursor und Fetch.

Ich habe mir folgendes vorgestellt, weis aber nicht ob ich da völlig auf dem Holzweg bin:

create function schulz/lieferant                       
( inval char (5 ) )                                    
RETURNS CHAR(500 )                                     
LANGUAGE SQL                                           
BEGIN                                                  
DECLARE ERGEBNIS NUMERIC(20, 5);                       
DECLARE myvar char(10);                                
select liefnr from schulz/table1 where artnr = inval;  
set myvar = trim(myvar) concat ' ' concat liefnr;      
RETURN myvar;                                          
END 

Erstellen lässt sie die Funktion jedenfalls nicht, denn es kommt der Fehler: Variable LIEFNR nicht definiert oder nicht verwendbar.

Aber ich vermute fast, das das so überhaupt nicht geht, wie ich mir das vorstelle.

Viele Grüße

Carsten

[B.Hauser]

Hallo Carsten,

Der Aufbau des Routine-Body ist für Stored Procedures und User Defined Functions identisch. Der einzige Unterschied ist, dass bei Funktionen der Rückgabe-Wert mit RETURN ausgegeben werden muss. Wenn Du also bei Stored Procedures Cursor, Schleifen und Fetches brauchst, benötigst Du die ebenso bei User Defined Functions

Das folgende Beispiel sollte Dein gewünschtes Ergebnis ermitteln und ausgeben:

CREATE FUNCTION MySchema/MyFkt (
    PARARTNR CHAR(5) ) 
    RETURNS VARCHAR(1024)
    LANGUAGE SQL 
    SPECIFIC MySchema/MyFkt
    NOT DETERMINISTIC 
    READS SQL DATA 
    CALLED ON NULL INPUT
    NO EXTERNAL ACTION 
    DISALLOW PARALLEL 
    FENCED 
Begin
   Declare ReturnVal  Varchar(1024) Default ' ';

   FOR CsrC1 AS C1 CURSOR 
       FOR SELECT LiefNr 
              FROM table1
              Where ArtNr = ParArtNr 
           DO Set ReturnVal = ReturnVal concat ' ' concat CsrC1.LiefNr; 
   END FOR; 

   Return LTrim(ReturnVal);
End; 

Übrigens die FOR-Schleife kombiniert Cursor-Definition, Schleife und Fetch.

Ansonsten würde ich Dir die beiden folgenden Redbooks empfehlen:
Stored Procedures, Triggers and User Defined Functions on DB2 Universal Database for iSeries
Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone


Birgitta

[cheffe1008]

Hallo zusammen,

super vielen Dank.
Habe die Function erzeugt und angepasst.
Das funktioniert.

Vielen Dank auch für die Buchtipps. Sowas ist immer wertvoll.

Grüße

Carsten