[NEWSboard IBMi Forum]
  1. #1
    Registriert seit
    Dec 2009
    Beiträge
    314

    SQL dringende Hilfe gesucht.

    Hallo,

    ich bräuchte wieder einmal jemanden, der mir vielleicht auf die Sprünge hilft.
    Ich schlage mich seit 2 Wochen mit einem Problem herum, welches auf mieser Datenqualität entsteht. Die Daten muss ich nehmen wie sie sind.
    Daher habe ich mit einem Kollegen der SQL ziemlich gut beherrscht, dem aber AS400 Kenntnisse fehlen eine CTE verbrochen.

    [PHP]

    with
    contacts
    as
    (
    select distinct kuns.gesid CD_CL_NR
    from kun3sel kuns
    )
    ,
    kundedefid
    as
    (select distinct
    'kuns.cmandnr || kuns.kmandnr || kuns.kdnr' as KUNDE_DEF_ID_Orig,
    kuns.gesidc as CD_CL_NR
    from kun3sel kuns
    )
    ,
    DODStartClassBase
    as
    (
    SELECT * from
    (SELECT
    cs.gesid, KR1_STICHTAG as DefClassStartDate ,
    KR1_STICHTAG as DefClassStartSince,
    (case when dh.KR1_DEF_KLASSE='BO'
    then 0 else 1 end) as DefClassStart,
    (KR1_ANL_DATUM*100000+KR1_ANL_ZEIT) as EntryNoStart,
    ROW_NUMBER() OVER(PARTITION BY gesid ORDER BY dh.KR1_DEF_KLASSE,
    (KR1_ANL_DATUM*100000+KR1_ANL_ZEIT)) Ranking
    FROM kun3sel cs , leakr1 dh
    where cs.gesid = dh.partnernr
    and dh.KR1_STICHTAG <= 20210123
    ) y
    )
    ,
    DODStartClass as
    (
    select * from
    (Select CD_CL_NR, DefClassStartDate, DefClassStartSince,
    DefClassStart, EntryNoStart, b.DefClassStart, b.DefClassStart2,
    ROW_NUMBER() OVER(PARTITION BY CD_CL_NR ORDER BY Ranking) Rang,
    ROW_NUMBER() OVER(PARTITION BY CD_CL_NR ORDER BY Ranking) Rang
    from DODStartClassBase a
    left outer join DODStartClassBase b
    on a.CD_CL_NR=b.CD_CL_NR
    and a.Ranking+1=b.Ranking
    and DefClassStart<>DefClassStart
    ) a where a.Rang=1
    )
    ,
    toDef as
    (
    select * from
    (
    SELECT cs.CD_CL_NR, KR1_STICHTAG DefClassToDate,
    KR1_STICHTAG DefClassToSince,
    (case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) DefClass,
    (KR1_ANL_DATUM*100000+KR1_ANL_ZEIT) EntryNoTo,
    ROW_NUMBER() OVER(PARTITION BY cs.CD_CL_NR ORDER BY KR1_STICHTAG,
    (KR1_ANL_DATUM*100000+KR1_ANL_ZEIT)) Rang
    FROM contacts cs
    inner join LEAKR1.dh
    on cs.CD_CL_NR = dh.partnern1r
    where dh.KR1_STICHTAG<=refDate and
    (case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) = 1
    ) a
    ),
    FromDef
    as
    (
    select * from
    (
    SELECT cs.CD_CL_NR, KR1_STICHTAG DefClassFromDate,
    KR1_STICHTAG DefClassFromSince,
    (case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) DefClass,
    (KR1_ANL_DATUM*100000+KR1_ANL_ZEIT) EntryNoFrom,
    ROW_NUMBER() OVER(PARTITION BY cs.CD_CL_NR ORDER BY KR1_STICHTAG,
    (KR1_ANL_DATUM*100000+KR1_ANL_ZEIT)) Rang
    FROM contacts cs
    inner join LEAKR1 dh
    on cs.CD_CL_NR =dh.partnernr
    where dh.STICHTAG <= refDate and
    (case when dh.DEFKLASSE ='BO' then 0 else 1 end) = 0
    ) a
    )
    select distinct
    k.KUNDE_DEF_ID_Orig,
    (case when z.DefClassToDate is not null
    then k.KUNDE_DEF_ID_Orig || convert(varchar(8), z.DefClassToDate)
    else '' end) KUNDE_DEF_ID, z.DefClassToDate BeginDate,
    z.DefClassFromDate EndDate,
    (case when z.DefClassToDate is null
    then null else OverAllEvent end) OverAllEvent
    from(
    select CD_CL_NR, DefClassToDate, DefClassFromDate,
    ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
    ORDER BY DefClassToDate, EntryNoTo) OverAllEvent,
    ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
    ORDER BY DefClassToDate desc, EntryNoTo desc) RangActual
    from
    (
    select CD_CL_NR, DefClassToDate, DefClassFromDate, EntryNoTo, EntryNoFrom,
    ROW_NUMBER() OVER(PARTITIon BY CD_CL_NR, DefClassFromDate,
    EntryNoFrom ORDER BY DefClassToDate, EntryNoTo) RANG_FROM
    from
    (
    select c.CD_CL_NR, dod.DefClassStart, dod.DefClassStartSince,
    t.DefClassToDate,
    t.EntryNoTo,
    (case when
    f.DefClassFromDate is null then 99991231 else DefClassFromDate end),
    (case when
    f.EntryNoFrom is null then 99999999999999 else EntryNoFrom end),
    ROW_NUMBER() OVER(PARTITIon BY c.CD_CL_NR, t.DefClassToDate, EntryNoFrom
    ORDER BY
    (case when
    f.DefClassFromDate is null then 99991231 end),
    (case when
    f.EntryNoFrom is null then 99999999999999 end)) RANG_TO
    from contacts c
    left join DODStartClass dod on c.CD_CL_NR=dod.CD_CL_NR
    left join toDEF t on c.CD_CL_NR=t.CD_CL_NR
    and t.DefClassToDate>=dod.DefClassStartDate
    and t.EntryNoTo>=dod.EntryNoStart
    left join fromDef f on t.CD_CL_NR=f.CD_CL_NR
    and t.DefClassToDate
    select distinct
    k.KUNDE_DEF_ID_Orig,
    (case when z.DefClassToDate is not null
    then k.KUNDE_DEF_ID_Orig || convert(varchar(8), z.DefClassToDate)
    else '' end) KUNDE_DEF_ID, z.DefClassToDate BeginDate,
    z.DefClassFromDate EndDate,
    (case when z.DefClassToDate is null
    then null else OverAllEvent end) OverAllEvent
    from(
    select CD_CL_NR, DefClassToDate, DefClassFromDate,
    ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
    ORDER BY DefClassToDate, EntryNoTo) OverAllEvent,
    ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
    ORDER BY DefClassToDate desc, EntryNoTo desc) RangActual
    from
    (
    select CD_CL_NR, DefClassToDate, DefClassFromDate, EntryNoTo, EntryNoFrom,
    ROW_NUMBER() OVER(PARTITIon BY CD_CL_NR, DefClassFromDate,
    EntryNoFrom ORDER BY DefClassToDate, EntryNoTo) RANG_FROM
    from
    (
    select c.CD_CL_NR, dod.DefClassStart, dod.DefClassStartSince,
    t.DefClassToDate,
    t.EntryNoTo,
    (case when
    f.DefClassFromDate is null then 99991231 else DefClassFromDate end),
    (case when
    f.EntryNoFrom is null then 99999999999999 else EntryNoFrom end),
    ROW_NUMBER() OVER(PARTITIon BY c.CD_CL_NR, t.DefClassToDate, EntryNoFrom
    ORDER BY
    (case when
    f.DefClassFromDate is null then 99991231 end),
    (case when
    f.EntryNoFrom is null then 99999999999999 end)) RANG_TO
    from contacts c
    left join DODStartClass dod on c.CD_CL_NR=dod.CD_CL_NR
    left join toDEF t on c.CD_CL_NR=t.CD_CL_NR
    and t.DefClassToDate>=dod.DefClassStartDate
    and t.EntryNoTo>=dod.EntryNoStart
    left join fromDef f on t.CD_CL_NR=f.CD_CL_NR
    and t.DefClassToDate and t.EntryNoTo ) x where x.RANG_TO=1
    ) y where y.RANG_FROM=1
    ) z inner join kundedefid k on z.CD_CL_NR=k.CD_CL_NR
    where
    z.RangActual=1
    and
    (
    z.DefClassFromDate>=refDate
    or
    ((DefClassFromDate/10000)=(refDate/10000) and
    Mode(DefClassFromDate/100 , 100)=Mode(refDate/100 , 100))
    );




    Wenn ich diese dann auf der AS400 mit RUNSQLSTM aufrufe, bekomme ich folgende Fehlermeldung

    MSG ID WTK SATZ TEXT
    SQL0084 30 6 Position 4 SQL-Anweisung nicht zulässig.

    Satz 6 ist das with
    Sind auf 7.3 mit akutellem PTF Stand auf einer Power 7

    Leider finde ich nicht warum, aber da mangelt es bei mir 100% an den SQL Kenntnissen (was daraus resultiert, das meine Chefs eine gescheite Schulung ablehnen, den da müssten sie Geld in die Hand nehmen, mich würde es ja interessieren).

    Mein Kollege hat das am SQL Server durchgeführt und dort funktioniert die Version tadellos.
    Ich bin echt schon am verzweifeln
    Andreas
    Ein AS/400 Dinosaurier since 1989

  2. #2
    Registriert seit
    Nov 2020
    Beiträge
    315
    Also was mir mal auffällt ist, dass ihr da die Funktion CONVERT verwendet, die es in SQL nicht gibt. Hier einfach ein CAST verwenden.

    Des weiteren dürfte es hier was fehlen, denn nach einem AND ... gleich ein SELECT kann auch nicht beim SQL Server funktionieren.

    ... and t.DefClassToDate
    select distinct ...

    Wenn Schulungen nicht genehmigt werden, muss man ab einen gewissen Punkt Hilfe extern dazukaufen.
    Bei so einem langen SQL (ohne auch noch die entsprechenden Daten zu haben) ist es schon etwas sehr aufwändig da sich im Detail zu beschäftigen.

  3. #3
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    RUNSQLSTM/RUNSQL unterstützt natürlich keinen Select, wohin dann mit den Daten?
    Analysieren kannst du aber auch mit ACS SQL-Script ausführen.

    Ich bin nun nicht ganz im Detail durch gegangen, aber folgende Punkte ergeben sich:

    select distinct kuns.gesid CD_CL_NR
    from kun3sel kuns
    sollte ersetzt werden mit

    select kuns.gesid CD_CL_NR
    from kun3sel kuns
    group by CD_CL_NR

    Da du mit diesem Konstrukt joinst, ist keine Indexverwendung möglich.

    (case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) = 1
    Da würde doch
    dh.KR1_DEF_KLASSE <> 'BO'
    reichen?

    Spätestens wenn du mit den Distinct-Selects joinst, fängt das Rechnen und Suchen an.
    Zum Verständnis:

    Ein CTE-Ausdruck ist keine temporäre Tabelle!
    Der Join-Zugriff erfolgt je Zeile und berechnet den Inhalt erst mal komplett neu.

    Die OLAP-Funktionen werden dann kritisch wenn man tatsächlich dann auf Berechnungsergebnisse filtert. Wenn man also nur die 1. Zeile eines Ergebnisses benötigt kann man

    cross join lateral (
    select ...... from .... join .... where ....
    order by ....
    fetch first 1 rows only
    ) y

    verwenden. Lateral Joins werden auch wieder nur temporär für jeden Satz ausgeführt. Wenn Indizes vorhanden sind erfolgt auch nur 1 Zugriff.
    Ich verwende den auch häufiger für skalare subselects wenn ich mehr als 1 Wert aus derselben Abfrage benötige.

    Und sonst? Index, Index, Index, ...

    Per ACS-SQL-Scripts kannst du auch den Advisor für die Performanceanalyse ausführen.
    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

  4. #4
    Registriert seit
    Dec 2009
    Beiträge
    314
    Danke Baldur für die ausführliche Antwort, werde mich mit meinem Kollegen kurzschließen und wir werden das dann hoffentlich hinbringen.
    Schöne sonnige Grüße aus Wien und schönes Osterwochenende
    Andreas
    Ein AS/400 Dinosaurier since 1989

  5. #5
    Registriert seit
    Mar 2002
    Beiträge
    5.286
    ... zumindest zum testen ist es wesentlich übersichtlicher aus den CTEs Views zu machen, dann kann man die einzeln evaluieren. Letztlich muss man sich aber auch die Frage stellen, was erreicht werden soll. Bei einmal Auswertungen ist es meist sinnvoller Extrakte zu ziehen und mit denen weiter zu arbeiten. Alles in ein Statement zu ziehen ist schwer überschaubar - es soll ja nicht nur ein Resultat bringen, sondern soll auch stimmen. Für Mehrfach Ausführungen geht das dann auch mit SQL Scripten. Braucht man das dauernd, sollte man mal überlegen, was man am Datenaufbau optimieren kann - auch ohne die vorhandene Anwendung ändern zu müssen.

    D*B
    AS400 Freeware
    http://www.bender-dv.de
    Mit embedded SQL in RPG auf Datenbanken von ADABAS bis XBASE zugreifen
    http://sourceforge.net/projects/appserver4rpg/

  6. #6
    Registriert seit
    Dec 2009
    Beiträge
    314
    Hallo Dieter
    gebraucht wird das ganze für einen Batchlauf der Dienstag bis Samstag früh ca. 60 Files für Reporting an diverse Konzernstellen und auch an Externe Abnehmer gehen. Daher diese ganze Trickserei. Wenn es nur intern wäre, würde ich schon ein Lösung finden.
    Leider sind die Daten so wie sie sind und auch die Datenaubauten(Großteil noch alte DDSn) gesetzt. Daran kann ich nichts ändern und die Datenstruktur und Verflechtung sind durch die zu liefernd Files auch vorgegeben und daran lässt sich auch nichts mehr drehen.

    Aber danke für Deine Anregungen, welche ich natürlich gerne aufnehme und innerhalb des Entwicklungsteams weitergeben.
    Schönen Gruß und schönes Osterwochenende
    Andreas
    Ein AS/400 Dinosaurier since 1989

  7. #7
    Registriert seit
    Mar 2002
    Beiträge
    5.286
    ... das ist eigentlich ein typischer Fall dafür, aus den vorhandenen Daten erst per Extrakt eine Datenbasis zu bilden, die dann für die Reports die Grundlage bilden. (So eine Art BI für Arme und Kranke). Diese Datenbasis wird nach den Erfordernissen der Reports designed. Es wäre auch zu überlegen, die Datenbasis auf einer anderen Büchse zu erstellen (meinethalben auch SQL_Server) oder auch Anwender mit Query Tools selber auf diese Daten loszulassen.
    Mit dieser Vorgehensweise ist Prüfung auf Stimmigkeit und Änderbarkeit der Abfragen wesentlich effektiver durchführbar als bei überkpmplexen SQL Statements.

    D*B
    AS400 Freeware
    http://www.bender-dv.de
    Mit embedded SQL in RPG auf Datenbanken von ADABAS bis XBASE zugreifen
    http://sourceforge.net/projects/appserver4rpg/

  8. #8
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Und was für ein BI-System habt ihr am Wickel dass solche verquere Abfragen benötigt?
    Frag mal mich, ich kann da auch was anbieten (siehe Signatur).
    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
    Dec 2009
    Beiträge
    314
    Zitat Zitat von Fuerchau Beitrag anzeigen
    Und was für ein BI-System habt ihr am Wickel dass solche verquere Abfragen benötigt?
    Frag mal mich, ich kann da auch was anbieten (siehe Signatur).
    Wie es sich für ein kleines Unternehmen innerhalb eines Konzerns gehört, selber geschrieben auf SQL Server da es ja nichts kosten darf.
    Ironie pur bei mir schon ich mach das Affentheater schon 14 Jahre mit und will 8 und ein halbes Jahr vor dem Ruhestand nicht mehr neu irgendwo anders anfangen.
    LG
    Andreas
    Ein AS/400 Dinosaurier since 1989

  10. #10
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Die Frage ist dann, wer das nach dir dann pflegen will.
    Ein richtiges BI baut dir Dashboards mit Grids, Pivot, Chart, Drilldown, Filtern und Suchen, Berechnungen u.v.m.;-). Ein Datawarehouse mit incrementellem ETL, Archivierung von flüchtigen Daten. Dataenrichment mit webbasierten mehrsprachigen Formularerfassungen.
    Das kannst du nicht alles selber programmiert haben.
    Es sei denn, du nutzt z.B. DevExpress mit Dashboard;-), was allerdings nur ein Frontend ist.
    Und neu anfangen?
    Du glaubst gar nicht, wie oft ich im Leben schon neu anfangen musste.
    Von COBOL bei Nixdorf über COBOL auf der AS/400 über RPGIII (mit MI) bis ILERPG mit SQL.
    Windows 3.11 mit C++, Win98 mit VB6/C++ und COM (Component Object Model) über VBA (Excel/Access) bis heute C#.Net mit Windows 10.

    Und ich wette mit dir: Innerhalb von 2 Tagen hast du dein erstes Dashboard.
    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

Similar Threads

  1. Hilfe (F1) weg!?
    By Spezlerin in forum IBM i Hauptforum
    Antworten: 2
    Letzter Beitrag: 25-10-19, 11:04
  2. HILFE
    By ahingerl in forum IBM i Hauptforum
    Antworten: 6
    Letzter Beitrag: 27-03-15, 08:38
  3. Hilfe zu Qp0lGetAttr Api gesucht
    By Robi in forum IBM i Hauptforum
    Antworten: 1
    Letzter Beitrag: 06-06-03, 21:38
  4. Hilfe AS/400
    By HEMPel84 in forum IBM i Hauptforum
    Antworten: 2
    Letzter Beitrag: 18-09-02, 15:36
  5. Hilfe bei AS 400
    By Fireball in forum IBM i Hauptforum
    Antworten: 9
    Letzter Beitrag: 22-08-01, 21:01

Berechtigungen

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