Sql-Abfrage

[tarkusch]

Hallo,

ich will ermitteln wie oft eine Kundennummern in gewissen Dateien vorkommen.

Ich habe mir eine Datei gemacht, wo ich die Kundennummern erfasst habe

FILE1(dzt. 171 Sätze):
TEILDATEI	FELDNAMEN	FELDLAENGE
ZP1		P1KDNR	5
ZW2		W2KDNR	5A

Im 2. File hätte ich dann die Ergebnisse geschrieben.

FILE2
STKDNR	STKDFILE	STKDFELD	STKDANZ

Die Kundennummern sind unterschiedlich in der Länge und Art definiert.
In einem RPG Programm laufe ich alle Kundennummer durch,
wobei ich den Sqlstring von FILE1 wie folgt zusammensetze:
(Alle Kundennummern die Alphanumerisch definiert wurden habe ich hinten mit einem A versehen)

IF %SCAN('A':FELDLAENGE) = *ZEROS;             
  SQLFill =                                    
  'SELECT COUNT(*) +                           
  FROM ' + TEILDATEI +                         
  ' WHERE ' + FELDNAME +                       
  ' = ' + %CHAR(KDNNR);                        
ELSE;                                          
SQLFill =                                      
  'SELECT COUNT(*) +                           
  FROM ' + %TRIM(TEILDATEI) +                  
  ' WHERE ' + %TRIM(FELDNAME) +                
  ' = ' + HK + %CHAR(KDNNR) + ' ' + HK;        
ENDIF;

Leider braucht das Programm sehr sehr lange.
Kann ich das auch als ein Sqlstatement abbilden oder ist das eh nur in einem Programm zu lösen?
Falls ja wie laufe ich die Kundennummern von 1 bis x durch?

Dank im Voraus

Tarkusch

[Fuerchau]

Dies hat nichts mit der Art des SQL's zu tun sondern mit einem fehlenden Index.
Deine Suche ergibt einen Tablescan.
Zusätzlich machst du das auch noch 171 Mal.

Lege einen Index für deine Suchdatei an und mach genau einen SQL.

select kdnr, count(*) from mytable a
where exists (select * from kdnrtable b where a.kdnr = b.kdnr)
group by kdnr

Innerhalb des subselects kannst du auch Anpassungen vornehmen:

select kdnr, count(*) from mytable a
where exists (select * from kdnrtable b where dec(a.kdnr, 7, 0) = b.kdnr)
group by kdnr

[tarkusch]

Hallo,

ich weiss nicht wie ich zu dem Index kommen soll.
In der Datei FILE1 habe ich nur die Teildateien mit den Feld- und Länge-Definitionen(die Sourcen sind manuell durchforstet worden).

Die Feldnamen, Längen und Art sind leider immer unterschiedlich.

Wie bilde ich eigentlich, wenn die Feldarten unterschiedlich sind, im Subselect ab?

Dank im Voraus

Tarki

[Fuerchau]

Der Index ist für deine Suchdatei mit den enthaltenen Kunden-Nr'n erforderlich.

Wenn du das Ergebnis als dynamischen SQL zusammenbaust, wie oben, kannst du die Ergebnisfelder ja auch casten:

select char(kdnr), count(*) from mytable a
where exists (select * from kdnrtable b where dec(a.kdnr, 7, 0) = b.kdnr)
group by char(kdnr)

Beim Fetch gibst du dann halt ein Zeichen- und ein Dezimalfeld an.

Wichtig ist natürlich, dass ein Zeichenfeld mit Kunden-Nr. auch immer numerisch gefüllt ist, sonst hast du Probleme beim cast dec(...).

[tarkusch]

Ich stehe da völlig auf der Leitung.

Den Tabelscan(FILE1P) habe ich mir ja manuell erstellt
Um den Index zu erhalten durchlaufe ich ja die die Kundendatei von 1 bis x und schreibe Sie dann in die File2P.


Mir ist klar das hier selbstverständlich nur (sehr gute) Hilfestellungen gegeben werden.

Wie soll ich sonst den Index für die Suchdatei ermitteln?

// Datei wo die Kundennummern vorkommen             
FFILE1P    UF A E           K DISK                  
// Auswertungsdatei                                 
FFILE2P    UF A E           K DISK                  
FKUNDENP   IF   E           K DISK                  
    //start main                                    
      Exec SQL                                      
        set option commit=*none;                    
                                                    
      For I = 1 to 99998;                           
        QKDNUM = I;                                 
        CHAIN I KUNDENP;                            
        IF %FOUND(KUNDENP);                         
          // Workdatei abarbeiten                   
          SETLL *START FILE1P;                    
          READ FILE1P;                            
          DOU %EOF(FILE1P);                       
            READ FILE1P;                          
          ENDDO;                                    
        ELSE;                                       
          QKDINF = 'KUNDENNUMMER NICHT VERGEBEN';   
          WRITE FILE2;                              
        ENDIF;                                      
      ENDFOR;                                                                   
                                                                                
      *INLR = *ON;                                                              
      RETURN;                                                                   
    //##########################################################################
    // Vorkommen ermitteln                                                      
    //##########################################################################
    BEGSR CountField;                                                           
      IDX    = *ZEROS;                                                          
      SQLCOD = *ZEROS;                                                          
      Count  = *ZEROS;                                                          
      IF %SCAN('A':FELDLAENGE) = *ZEROS;                                        
        SQLFill =                                                               
        'SELECT COUNT(*) +                                                      
        FROM ' + %TRIM(TEILDATEI) +                                             
        ' WHERE ' + %TRIM(FELDNAME) +                                           
        ' = ' + %CHAR(KDNNR);                                                   
      ELSE;                                                                     
      SQLFill =                                                                 
        'SELECT COUNT(*) +                                                      
        FROM ' + %TRIM(TEILDATEI) +                                             
        ' WHERE ' + %TRIM(FELDNAME) +                                           
        ' = ' + HK + %CHAR(KDNNR) + ' ' + HK;                                   
      ENDIF;                                                                    
                                                                                
      Exec SQL                                   
        Prepare DynSQL from :SQLFill;            
                                                 
      Exec SQL                                   
        Declare CsrC1 Cursor For DynSQL;         
                                                 
      Exec SQL                                   
        Open CsrC1;                              
                                                 
      IF SQLCOD <> 0;                            
        EXSR WriteRecord;                        
        Exec SQL                                 
          Close CsrC1;                           
        LeaveSr;                                 
      ENDIF;                                     
                                                 
      Exec SQL                                   
        Fetch next from CsrC1 into :Count;       
                                                 
    DoW SQLCOD = *ZEROS;                         
      IDX += 1;                                  
      If IDX = 1 and SqlCod <> *ZEROS;           
        If sqlcod = 100 and SQLSTT = '02000';      
        Else;                                         
        // Fehler                                   
        Exsr WriteRecord;                           
        EndIf;                                        
      EndIf;              
                                    
       IF SQLCOD = *ZEROS;      
        INFOTEXT = 'OK!';      
        IF  COUNT > *ZEROS;    
          Exsr WriteRecord;    
        ENDIF;                 
      ENDIF;                   
      LEAVE;                   
    ENDDO;                     
                               
    Exec SQL                   
      Close CsrC1;             
                               
    ENDSR;                                                                
    //##########################################################################            
    // WriteRecord                                                                          
    //##########################################################################            
    BEGSR WriteRecord;                                                                      
      Clear FILE2;                                                                          
      QKDNUM = I;                                                                           
      QKDSRC = SOURCE;                                                                      
      QKDFIL = TEILDATEI;                                                                   
      QKDTXT = TEXT;                                                                        
      QKDNAM = FELDNAME;                                                                    
      QKDLNG = FELDLAENGE  ;                                                                
      QKDANZ = Count;                                                                       
      QKDINF = *BLANKS;                                                                     
      QKDCOD = SQLCOD;                                                                      
      QKDINF = 'OK!';                                                                       
      IF SQLCOD = -514;                                                                     
        QKDINF = 'ERROR:FELDNAME';                                                          
        INFOTEXT = 'ERROR:FELDNAMEN';                                                       
      ELSE;                                                                                 
        IF SQLCOD = -802;                                                                   
          QKDINF = 'ERROR:KORRUPTE DATA';                                                   
          INFOTEXT = 'ERROR:KORRUPTE DATA';                                                 
        ELSE;                                                                               
          INFOTEXT = 'SQLCOD: ' + %CHAR(SQLCOD);                                            
        ENDIF;             
      ENDIF;               
      Write(E) FILE2;      
    ENDSR;                 

FILE1P
A**********************************************************************************************
A** DATEIEN DIE KUNDENNUMMER BEINHALTEN                                                      **
A**                                                                                          **
A**********************************************************************************************
A                                      UNIQUE                                                  
A          R FILE1                                                                           
A            SOURCE        20A         TEXT('SOURCE')                                          
A            TEILDATEI     10A         TEXT('TEILDATEI')                                       
A            TEXT          50A         TEXT('TEXT')                                            
A            FELDNAME      10A         TEXT('FELDNAME')                                        
A            FELDLAENGE    10A         TEXT('FELDLAENGENAME')                                  
A            INFOTEXT      20A         TEXT('INFOTEXT')                                        
A          K SOURCE                                                                            
A          K TEILDATEI                                                                         
A          K TEXT                                                                              
A          K FELDNAME                                                                          
A          K FELDLAENGE            

FILE2P
A**********************************************************************************************
A** STATISTIKAUSWERTUNG KUNDENNUMMER                                                         **
A**                                                                                          **
A**********************************************************************************************
A                                      UNIQUE                                                  
A          R FILE2                                                                             
A            QKDNUM         5  0       TEXT('Kundennummer')                                    
A                                      COLHDG('KdnNr')                                         
A            QKDSRC        20A         TEXT('SOURCE')                                          
A            QKDFIL        10A         TEXT('TEILDATEI')                                       
A            QKDTXT        50A         TEXT('TEXT')                                            
A            QKDNAM        10A         TEXT('FELDNAME')                                        
A            QKDLNG        10A         TEXT('FELDLAENGENAME')                                  
A            QKDANZ         9  0       TEXT('ANZAHL')                                          
A                                      COLHDG('ANZAHL')                                        
A            QKDINF        50A         TEXT('ANMERKUNG')                                       
A                                      COLHDG('ANMERKUNG')                                     
A            QKDCOD         5  0       TEXT('SQLCOD')                                          
A                                      COLHDG('SQLCOD')                                        
A          K QKDNUM                                                                            
A          K QKDFIL                                                                            
A          K QKDTXT                                                                            
A          K QKDNAM                                                                            
A          K QKDLNG

[Fuerchau]

Die Frage die sich mir stellt, was ist die eigentliche Aufgabenstellung?
Ich denke, dein Ansatz ist von Grundauf falsch.

[tarkusch]

Ich soll so Art Statistik erstellen die auflistet in welchen Dateien eine Kundennummer vorkommt.

[Fuerchau]

Ich nehme mal an, dass KUNDENP die zu ermittelnden Kunden-Nr'n enthält.
Deine Ausgabedatei hat auch ein entsprechendes Format.
Nun legst du einen Index über die Kdnr der KUNDENP an und generierst folgenden SQL (legt keinen Wert auf Vollständigkeit):

insert into ZFile (Dateiname, Feldname, Inhalt, Anzahl)
select 'DATEINAME', 'FELDNAME', char(KDNRFIELD), count(*) from QFile A
where exists (select * from KUNDENP P where A.KDNRFIELD = P.KDNR)
group by 'DATEINAME', 'FELDNAME', char(KDNRFIELD)

Ist das KDNRFIELD Alpha, solltest du wissen, ob immer ein numerischer Wert drin steht. Dann kannst du das einfach casten, z.B.:

dec(trim(A.KDNRFIELD), 7, 0)

[tarkusch]

Herzlichen dank für die Mühe und Geduld.

Darf ich nur eine abschließende Frage stellen:

Ich begreife aber immer noch nicht wie die Verknüpfung
A.KDNRFIELD = P.KDNR funktionieren soll.

Bei mir in der Datei stehen die Daten wie folgt:

SOURCE              TEILDATEI TEXT                    FELDNAME FELDLAENGE 
QRPGLESRC/TESTSRC   ZB1AUS	 Bestelldatei(allgemein) B1VAUG#  5          
QRPGLESRC/TESTSRC   ZB1AUS	 Bestelldatei(allgemein) B1VEXI   17A

Bei KDNRFIELD(FELDLAENGE) steht nur die wirkliche Angabe der Feldlaenge.

[Fuerchau]

Betrachte deine erste FOR-Schleife mit Zugriff auf KUNDENP.
Hier prüfst du doch auf die zu prüfende KDNR.
Die KUNDENP ist also deine Prüfdatei für die EXISTS-Klausel.
Du greifst also mit dem variablen Kundenfeld per EXISTS auf die KDNR der KUNDENP zu und erhältst somit nur die Kunden, die in KUNDENP vorhanden sind.

Deine FOR-Schleife ist doch vollkommen unnötig.
Die nicht vergebenen KDNR'n sind doch für diese Aufgabe nicht relevant.

[tarkusch]

Ich habe es vielleicht nicht genau beschrieben.
In der Datei File1p habe ich die Datei/Feld/ und Längeangaben erfasst und manuell eingetragen.
Dabei habe ich das Problem das die Kundennummer manchmal Numerisch und Alpha angeben wurde.

Ich soll eine Auswertung erstellen(FILE2P) erstellen Informationen gibt wie oft eine Kundennummer in unserem System vorkommt(
Kundennummer 273 kommt in
file1p.Datei, file1p.Feld x 10 mal vor
file1p.Datei, file1p.Feld y 5 mal vor etc.

Ich begreife nicht wie ich da einen Index erstellen soll, da die Datei File1p nur Definitionen beinhaltet.

lg

[Pikachu]

Sammel doch alle Kundennummern mitsamt Anzahl in jeder Teildatei auf einmal ein. Etwa so für jeden Eintrag in FILE1P:

INSERT INTO FILE2P
SELECT KDNR,"Teildatei","Feld",COUNT(*) FROM Teildatei
WHERE Feld = KDNR GROUP BY KDNR ORDER BY 1, 2, 3