Ansicht ABIPruefungsfaecherSchriftlich

Abfrage

SQL

SELECT
    SA."Mandant" || '.' ||  SA."Schueler" || '.' || S."Klasse" || '.' || S."Zeitraum" || '1' AS "ID",  
    SA."Mandant",     
    SA."Schueler",
    S."Klasse",
    S."Zeitraum",   
    S."Vorname",
    S."Vorname2",
    S."Namenszusatz",
    S."Nachname",          
    S."Geschlecht",
    S."Geburtsdatum",
    K."Notenart",
    SA."Pruefungsfach1" AS "Fach",
    1 AS "PF",
    SA."Jahrgang",
    SA."PruefungSchriftlich1Pruefer1",
    SA."PruefungSchriftlich1Pruefer2",
    SA."PruefungSchriftlich1",
    SAD."Lehrer",
    SAD."Unterrichtsart",
    SAD."Fachstatus",
    SAD."Summe2"    
  FROM "SchuelerABI" SA
  LEFT JOIN "SchuelerAnsicht" S
  ON  S."Mandant" = SA."Mandant"
  AND S.ID = SA."Schueler"
  AND S."Gewechselt" <> 'J'
  LEFT JOIN "Klassen" K
  ON  K."Mandant" = S."Mandant" 
  AND K."ID" = S."Klasse"
  LEFT JOIN "SchuelerABIDetails" SAD
  ON  SAD."Mandant" = SA."Mandant" 
  AND SAD."Schueler" = SA."Schueler"
  AND SAD."Fach" = SA."Pruefungsfach1"      
  UNION 
  SELECT
    SA."Mandant" || '.' ||  SA."Schueler" || '.' || S."Klasse" || '.' || S."Zeitraum" || '2' AS "ID",
    SA."Mandant", 
    SA."Schueler",
    S."Klasse",
    S."Zeitraum",   
    S."Vorname",
    S."Vorname2",
    S."Namenszusatz",
    S."Nachname",          
    S."Geschlecht",
    S."Geburtsdatum",
    K."Notenart",
    SA."Pruefungsfach2" AS "Fach",
    2 AS "PF",
    SA."Jahrgang",
    SA."PruefungSchriftlich2Pruefer1",
    SA."PruefungSchriftlich2Pruefer2",
    SA."PruefungSchriftlich2",
    SAD."Lehrer",
    SAD."Unterrichtsart",
    SAD."Fachstatus",
    SAD."Summe2"        
  FROM "SchuelerABI" SA
  LEFT JOIN "SchuelerAnsicht" S
  ON  S."Mandant" = SA."Mandant"
  AND S.ID = SA."Schueler"
  AND S."Gewechselt" <> 'J'
  LEFT JOIN "Klassen" K
  ON  K."Mandant" = S."Mandant" 
  AND K."ID" = S."Klasse"
  LEFT JOIN "SchuelerABIDetails" SAD
  ON  SAD."Mandant" = SA."Mandant" 
  AND SAD."Schueler" = SA."Schueler"
  AND SAD."Fach" = SA."Pruefungsfach2"     
  UNION 
  SELECT
    SA."Mandant" || '.' ||  SA."Schueler" || '.' || S."Klasse" || '.' || S."Zeitraum" || '3' AS "ID",
    SA."Mandant", 
    SA."Schueler",
    S."Klasse",
    S."Zeitraum",   
    S."Vorname",
    S."Vorname2",
    S."Namenszusatz",
    S."Nachname",          
    S."Geschlecht",
    S."Geburtsdatum",
    K."Notenart",
    SA."Pruefungsfach3" AS "Fach",
    3 AS "PF",
    SA."Jahrgang",
    SA."PruefungSchriftlich3Pruefer1",
    SA."PruefungSchriftlich3Pruefer2",
    SA."PruefungSchriftlich3",
    SAD."Lehrer",
    SAD."Unterrichtsart",
    SAD."Fachstatus",
    SAD."Summe2"        
  FROM "SchuelerABI" SA
  LEFT JOIN "SchuelerAnsicht" S
  ON  S."Mandant" = SA."Mandant"
  AND S.ID = SA."Schueler"
  AND S."Gewechselt" <> 'J'
  LEFT JOIN "Klassen" K
  ON  K."Mandant" = S."Mandant" 
  AND K."ID" = S."Klasse"
  LEFT JOIN "SchuelerABIDetails" SAD
  ON  SAD."Mandant" = SA."Mandant" 
  AND SAD."Schueler" = SA."Schueler"
  AND SAD."Fach" = SA."Pruefungsfach3"    
  UNION
  SELECT
    SA."Mandant" || '.' ||  SA."Schueler" || '.' || S."Klasse" || '.' || S."Zeitraum" || '4' AS "ID",
    SA."Mandant", 
    SA."Schueler",
    S."Klasse",
    S."Zeitraum",   
    S."Vorname",
    S."Vorname2",
    S."Namenszusatz",
    S."Nachname",          
    S."Geschlecht",
    S."Geburtsdatum",
    K."Notenart",
    SA."Pruefungsfach4" AS "Fach",
    4 AS "PF",
    SA."Jahrgang",
    SA."PruefungSchriftlich4Pruefer1",
    SA."PruefungSchriftlich4Pruefer2",
    SA."PruefungSchriftlich4",
    SAD."Lehrer",
    SAD."Unterrichtsart",
    SAD."Fachstatus",
    SAD."Summe2"        
  FROM "SchuelerABI" SA
  LEFT JOIN "SchuelerAnsicht" S
  ON  S."Mandant" = SA."Mandant"
  AND S.ID = SA."Schueler"
  AND S."Gewechselt" <> 'J'
  LEFT JOIN "Klassen" K
  ON  K."Mandant" = S."Mandant" 
  AND K."ID" = S."Klasse"
  LEFT JOIN "SchuelerABIDetails" SAD
  ON  SAD."Mandant" = SA."Mandant" 
  AND SAD."Schueler" = SA."Schueler"
  AND SAD."Fach" = SA."Pruefungsfach4"