[NEWSboard IBMi Forum]
  1. #1
    Registriert seit
    Oct 2015
    Beiträge
    109

    Question SQL Function in Result ausführen

    Hallo zusammen!

    ich habe eine SQL Function Split, die einen String in mehrere Zeilen spaltet:

    select * from table(split( 'AA;BB;CC' , ';')) a

    Aus String 'AA;BB;CC'
    wird also:
    AA
    BB
    CC

    Jetzt habe ich einen zweiten select:
    select id, Text from Beispiel.
    Beispiel Ergebnis:
    1, 'AAA;BBB;CCC'
    2, 'BG;AS;KG'
    3, 'LC;EK;AL'

    ich möchte meine Function pro Text aufrufen, so dass ich am Ende Folgendes erhalte:
    1, AAA
    1, BBB
    1, CCC
    2, BG
    2, AS
    2, KG
    3, LC
    3, EK
    3, AL

    Kann mir jemand weiterhelfen?

    Ein Ansatz war:

    begin
    create or replace table qtemp.Ergebnis (ID char(7), Text char(80));
    for Zeile as select id, Text from Beispiel
    do
    select ID, a.*
    into qtemp.Ergebnis
    from table(split( Text , ';')) a;
    end for;
    end

    Das kann allerdings nicht ausgeführt werden.

    Vielen Dank!

  2. #2
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Du kannst natürlich eine "Create Function .... returnung Table" definieren, der du entsprechende Argumente übergibst und mehrere Zeilen zurückkommen.
    Mit dieser Funktion kannst du dann joinen, wobei dann aben auch Schlüsselbeziehungen benötigt werden.

    Wobei ich mich frage, warum du eine Textspalte dieser Art erstellt hast an Stelle eine Tabelle zu verwenden in der die Texte sowieso als Zeilen zur Verfügung stehen.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  3. #3
    Registriert seit
    Oct 2015
    Beiträge
    109
    Zitat Zitat von Fuerchau Beitrag anzeigen
    Du kannst natürlich eine "Create Function .... returnung Table" definieren, der du entsprechende Argumente übergibst und mehrere Zeilen zurückkommen.
    Mit dieser Funktion kannst du dann joinen, wobei dann aben auch Schlüsselbeziehungen benötigt werden.
    Eben diese Verknüpfung möchte mir nicht gelingen... :/

    Zitat Zitat von Fuerchau Beitrag anzeigen
    Wobei ich mich frage, warum du eine Textspalte dieser Art erstellt hast an Stelle eine Tabelle zu verwenden in der die Texte sowieso als Zeilen zur Verfügung stehen.
    Wie so oft ist die Spalte einfach schon gegeben und ich hab keinen Einfluss drauf - ich muss jetzt entknoten :P

  4. #4
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Die Table-Funktion benötigt als weiteren Parameter den Key, den es dann ebenso auch wieder als Ergebnisfeld zurückgibt.

    Das Hauptproblem könnte sein, dass eine Jointabelle nur 1x aufgerufen wird und dann alle Sätze mt dem Ergebnis verknüpft werden.
    Alternativ könnte es mit einer "Lateral Table (select from TableFunction(P1, ....Pn)) on ..." funktionieren, da diese tatsächlich je Zeile aufgerufen wird.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  5. #5
    Registriert seit
    Oct 2015
    Beiträge
    109
    Zitat Zitat von Fuerchau Beitrag anzeigen
    Alternativ könnte es mit einer "Lateral Table (select from TableFunction(P1, ....Pn)) on ..." funktionieren, da diese tatsächlich je Zeile aufgerufen wird.
    Das hat mich schon weiter gebracht!

    Code:
    select id, Trennung from Beispiel, lateral (select item as Trennung from Table(split( Text, ';')) a) a
    funktioniert!

    Leider kann ich mit dem Ergebnis nicht richtig weiter arbeiten.
    Sowohl group by als auch n insert in eine temporäre Tabelle scheitern mit:
    SQL0802 Fehler bei Datenumsetzung oder beim Zuordnen von Daten

  6. #6
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Wenn man das unter Debug ausführt, gibt es ggf. weitere Hinweise im Joblog.
    Welche Feldtypen gibst du aus der Tablefunction zurück?

    Group by ist u.U. auch ein Problem. Hier könnte eine "derived Table" funktionieren:

    select * from (
    Your Select
    ) a
    group by abc ...
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  7. #7
    Registriert seit
    Oct 2015
    Beiträge
    109
    Zitat Zitat von Fuerchau Beitrag anzeigen
    Welche Feldtypen gibst du aus der Tablefunction zurück?
    Eine Tabelle mit Varchar:

    Code:
    RETURNS TABLE (ITEM VARCHAR(8000))
    Zitat Zitat von Fuerchau Beitrag anzeigen
    Group by ist u.U. auch ein Problem. Hier könnte eine "derived Table" funktionieren:

    select * from (
    Your Select
    ) a
    group by abc ...
    Das war auch mein erster Versuch, leider vergeblich.

    Zitat Zitat von Fuerchau Beitrag anzeigen
    Wenn man das unter Debug ausführt, gibt es ggf. weitere Hinweise im Joblog.
    Die detaillierteste Beschreibung die ich finden konnte ist:

    Nachricht . . . : Auswahlfehler für Feld ITEM.

    Ursache . . . . . : Die Abfrage wurde nicht ausgeführt, da ein
    Vergleichsoperand auf Daten aus Feld ITEM (in Satz 0 der Datei Beispiel in
    Bibliothek TSTDTA, die nicht gleich *N sind,) zugreift. Die Daten aus Feld
    ITEM sind nicht notwendigerweise falsch, da es sich bei dem Operanden
    möglicherweise um einen Ausdruck handelt, der neben Feld ITEM auch andere
    Felder und Konstanten einbezieht. Ferner ist es möglich, dass für den
    Operanden eine Umsetzung erforderlich ist, die nicht erfolgreich ausgeführt
    werden konnte.
    Um die Fehlerursache bestimmen zu können und zu überprüfen, ob weitere
    Fehler bei der Verarbeitung anderer Felder oder Sätze aufgetreten sind, den
    Befehl DSPJOBLOG (Jobprotokoll anzeigen) verwenden oder F10 (Nachrichten im
    Jobprotokill anzeigen) drücken.

  8. #8
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Dies könnte (mal wieder) ein CCSID-Problem sein, wenn der Job wieder auf CCSID 65535 steht.
    Gib mal definiert "varchar(nn) ccsid 273" oder besser noch NVARCHAR zurück, wobei 8K ja schon recht groß sind und sich bei NVarchar verdoppeln.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  9. #9
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Hmmmm, also bei meiner Funktion klappt sowohl das Aufdröseln als auch das einfügen in eine Temporäre Tabelle als auch das wieder zusammensetzen.

    Hier ist meine Funktion (vielleicht hilfts ja):
    Code:
    Create Or Replace Function YourSchema.SplitStringIntoRows
                     (PARSTRING Varchar(1024),
                      PARSEP Varchar(1) Default ';')
       Returns Table(SPLITELEM Varchar(256))
       Language Sql
       Specific YOURSCHEMA.SPLITSTRR
       Not Deterministic
       Modifies Sql Data
       Called On Null Input
       Set Option Commit = *NONE,
                  Dbgview = *Source
                  
       Begin
          Declare POSSEP    Integer Default 1;
          Declare PrvPosSep Integer Default 0;
       
          Set ParString = Trim(ParString);        
          
       RepLoop: Repeat Set PrvPosSep = Case When PosSep <> 1 
                                         Then PosSep + 1
                                         Else 1 End;
                    If Length(Trim(ParString)) = 0 or ParString = ParSep
                       Then Leave RepLoop;
                    End If;                        
                    Set PosSep = Locate(ParSep, ParString, PrvPosSep);
                    If PosSep = 0 
                       Then Pipe(Trim(Substr(ParString, PrvPosSep)));
                            Leave RepLoop;
                    Else Pipe(Trim(Substr(ParString, PrvPosSep, PosSep - PrvPosSep)));     
                    End If;
             Until PosSep = 0 End Repeat;     
          Return;   
      End;
    ... und die folgenden Statements können problemlos ausgeführt werden:
    Code:
    Declare Global Temporary Table mytable
     ( MyId  Integer,
      MyText  VarChar(4096),
      MyElem  VarChar(256));
      
    Insert into mytable  
    With Data (Id, Text) as (Values(1, 'AB;CD;E;;FG;H'), 
                                   (2, 'A; B; C'), 
                                   (3, 'XXX;YY;ZZZZZ;AA'), 
                                   (4, ('AAA;D;DD;;BB;DD;EE')))  
    Select * 
       from Data, 
            Lateral(Select * from Table(SplitStringIntoRows(Text, ';')) x) c
       Where SplitElem like '%A%';
    
    With Data (Id, Text) as (Values(1, 'AB;CD;E;;FG;H'), 
                                   (2, 'A; B; C'), 
                                   (3, 'XXX;YY;ZZZZZ;AA'), 
                                   (4, ('AAA;D;DD;;BB;DD;EE')))  
    Select Id, ListAgg(SplitElem, ';') 
       from Data, 
            Lateral(Select * from Table(SplitStringIntoRows(Text, ';')) x) c
       Group By Id;
    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  10. #10
    Registriert seit
    Oct 2015
    Beiträge
    109
    Zitat Zitat von Fuerchau Beitrag anzeigen
    Dies könnte (mal wieder) ein CCSID-Problem sein, wenn der Job wieder auf CCSID 65535 steht.
    Gib mal definiert "varchar(nn) ccsid 273" oder besser noch NVARCHAR zurück, wobei 8K ja schon recht groß sind und sich bei NVarchar verdoppeln.
    Das hat leider auch nicht geholfen.

    Zitat Zitat von B.Hauser Beitrag anzeigen
    Hmmmm, also bei meiner Funktion klappt sowohl das Aufdröseln als auch das einfügen in eine Temporäre Tabelle als auch das wieder zusammensetzen.

    Hier ist meine Funktion (vielleicht hilfts ja):
    Code:
    Create Or Replace Function YourSchema.SplitStringIntoRows
                     (PARSTRING Varchar(1024),
                      PARSEP Varchar(1) Default ';')
       Returns Table(SPLITELEM Varchar(256))
       Language Sql
       Specific YOURSCHEMA.SPLITSTRR
       Not Deterministic
       Modifies Sql Data
       Called On Null Input
       Set Option Commit = *NONE,
                  Dbgview = *Source
                  
       Begin
          Declare POSSEP    Integer Default 1;
          Declare PrvPosSep Integer Default 0;
       
          Set ParString = Trim(ParString);        
          
       RepLoop: Repeat Set PrvPosSep = Case When PosSep <> 1 
                                         Then PosSep + 1
                                         Else 1 End;
                    If Length(Trim(ParString)) = 0 or ParString = ParSep
                       Then Leave RepLoop;
                    End If;                        
                    Set PosSep = Locate(ParSep, ParString, PrvPosSep);
                    If PosSep = 0 
                       Then Pipe(Trim(Substr(ParString, PrvPosSep)));
                            Leave RepLoop;
                    Else Pipe(Trim(Substr(ParString, PrvPosSep, PosSep - PrvPosSep)));     
                    End If;
             Until PosSep = 0 End Repeat;     
          Return;   
      End;
    ... und die folgenden Statements können problemlos ausgeführt werden:
    Code:
    Declare Global Temporary Table mytable
     ( MyId  Integer,
      MyText  VarChar(4096),
      MyElem  VarChar(256));
      
    Insert into mytable  
    With Data (Id, Text) as (Values(1, 'AB;CD;E;;FG;H'), 
                                   (2, 'A; B; C'), 
                                   (3, 'XXX;YY;ZZZZZ;AA'), 
                                   (4, ('AAA;D;DD;;BB;DD;EE')))  
    Select * 
       from Data, 
            Lateral(Select * from Table(SplitStringIntoRows(Text, ';')) x) c
       Where SplitElem like '%A%';
    
    With Data (Id, Text) as (Values(1, 'AB;CD;E;;FG;H'), 
                                   (2, 'A; B; C'), 
                                   (3, 'XXX;YY;ZZZZZ;AA'), 
                                   (4, ('AAA;D;DD;;BB;DD;EE')))  
    Select Id, ListAgg(SplitElem, ';') 
       from Data, 
            Lateral(Select * from Table(SplitStringIntoRows(Text, ';')) x) c
       Group By Id;
    Birgitta
    Mit diesem Split klappt es! Ich versuche noch heraus zu finden,
    weshalb meine Function da Probleme bereitet.

    Mein Problem ist aber behoben, vielen Dank!

Similar Threads

  1. SQL create function
    By KingofKning in forum NEWSboard Programmierung
    Antworten: 5
    Letzter Beitrag: 09-10-15, 09:12
  2. Panel Function 20
    By KingofKning in forum IBM i Hauptforum
    Antworten: 2
    Letzter Beitrag: 15-07-15, 19:24
  3. SQL User Defined Function mit V5R1
    By Atomik in forum IBM i Hauptforum
    Antworten: 3
    Letzter Beitrag: 09-10-02, 10:57
  4. Remote Function Call -> SAP
    By areichelt in forum NEWSboard SAP
    Antworten: 2
    Letzter Beitrag: 24-02-02, 17:44
  5. Intersystem Communication Function
    By delphix in forum IBM i Hauptforum
    Antworten: 1
    Letzter Beitrag: 14-02-02, 17:14

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •