View Full Version : Sql-Abfrage
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
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
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
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(...).
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
Die Frage die sich mir stellt, was ist die eigentliche Aufgabenstellung?
Ich denke, dein Ansatz ist von Grundauf falsch.
Ich soll so Art Statistik erstellen die auflistet in welchen Dateien eine Kundennummer vorkommt.
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)
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.
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.