collapse

* Benutzer Info

 
 
Willkommen Gast. Bitte einloggen oder registrieren. Haben Sie Ihre Aktivierungs E-Mail übersehen?

* Wer ist Online

  • Punkt Gäste: 55
  • Punkt Versteckte: 1
  • Punkt Mitglieder: 2
  • Punkt Benutzer Online:

* Forenstatistik

  • stats Mitglieder insgesamt: 13976
  • stats Beiträge insgesamt: 66571
  • stats Themen insgesamt: 8973
  • stats Kategorien insgesamt: 5
  • stats Boards insgesamt: 17
  • stats Am meisten online: 415

Autor Thema: Syntaxfehler (fehlender Operator)  (Gelesen 85 mal)

Offline eviblabla

  • Newbie
  • Beiträge: 1
Syntaxfehler (fehlender Operator)
« am: Juli 04, 2018, 10:15:56 »
SELECT week, ROUND(VBZ_Woche.wert-VBZ_Basis.wert,3) AS korrektur, IIF(SUM(potential_ist) < (VBZ_Woche.wert-VBZ_Basis.wert)*-1, ROUND(SUM(potential_ist),3)*-1, ROUND(VBZ_Woche.wert-VBZ_Basis.wert,3)) AS p_ist_sum,
ROUND(SUM(potential_vsx),3)*-1 AS p_vsx_sum, ROUND(SUM(potential_vs),3) AS p_vs_sum FROM (((SELECT gueltig, hg, jahr, woche, k_ID, ende, potential as potential_ist, 0 as potential_vs, 0 as potential_vsx, IIf(IsNull(ende), Null, CWeek(ende)) As week
FROM Import WHERE hg = 4 Union
SELECT gueltig, hg, jahr, woche, k_ID, ende, 0 as potential_ist, potential as potential_vs, 0 as potential_vsx, IIf(Import.hg <= 3, IIf(IsNull(ziel), Null, CWeek(ziel)), IIf(IsNull(ende), Null, CWeek(ende))) As week
FROM Import Union
SELECT gueltig, hg, jahr, woche, k_ID, ende, 0 as potential_ist, 0 as potential_vs, potential as potential_vsx, IIf(Import.hg <= 3 And IIf(IsNull(ziel), Null, CWeek(ziel)) > 23, IIf(IsNull(ziel), Null, CWeek(ziel)), IIf(IIf(IsNull(ende), Null, CWeek(ende)) <= 23, IIf(IsNull(ende), Null, CWeek(en
de)), Null)) As week FROM Import) AS tbl1
INNER JOIN Kostenstelle ON tbl1.k_ID = Kostenstelle.k_ID)
LEFT JOIN VBZ_Basis ON tbl1.k_ID = VBZ_Basis.k_ID)
LEFT JOIN VBZ_Woche ON tbl1.week = VBZ_Woche.woche
WHERE tbl1.gueltig <> 0 AND tbl1.hg <> 9 AND tbl1.jahr = 2018 AND tbl1.woche = 23 AND Kostenstelle.name = '3105' AND week >= 1 AND VBZ_Woche.jahr = 2018 AND VBZ_Woche.woche = week AND (Year(tbl1.ende) = 2018 OR isNull(tbl1.ende) OR (Year(tbl1.ende) = 2018-1 AND Month(tbl1.ende) = 12 AND CWeek(tbl1.ende) = 1) OR (Year(tbl1.ende) = 2018+1 AND Month(tbl1.ende) = 1 AND CWeek(tbl1.ende) >= 51))
GROUP BY week, VBZ_Basis.wert, VBZ_Woche.wert;


An der Stelle: 'IIF(import.hg <=3 AND IIF(IsNull(ziel),Null,CWeek(ziel)) > 23, IIF(IsNull(ziel),Null,CWeek(ziel)),IIF(IIF(IsNull(ende), Null, CWeek(ende)) <= 23, IIF(IsNull(ende), Null, CWeek(ende)),Null))'
 

Offline Lachtaube

  • Access Guru
  • ****
  • Beiträge: 1248
Re: Syntaxfehler (fehlender Operator)
« Antwort #1 am: Juli 04, 2018, 13:21:19 »
So eine Abfrage wirkt abschreckend. Hier noch einmal in lesbaren Zustand.SELECT week,
       ROUND( VBZ_Woche.wert - VBZ_Basis.wert, 3 ) AS korrektur,
       IIF( SUM( potential_ist ) < ( VBZ_Woche.wert - VBZ_Basis.wert ) * -1,
            ROUND( SUM( potential_ist ), 3 ) * -1,
            ROUND( VBZ_Woche.wert - VBZ_Basis.wert, 3 ) ) AS p_ist_sum,
       ROUND( SUM( potential_vsx ), 3 ) * -1 AS p_vsx_sum,
       ROUND( SUM( potential_vs ), 3 ) AS p_vs_sum
FROM   ( ( ( SELECT gueltig,
                    hg,
                    jahr,
                    woche,
                    k_ID,
                    ende,
                    potential as potential_ist,
                    0 as potential_vs,
                    0 as potential_vsx,
                    IIf( IsNull( ende ), Null, CWeek( ende ) ) As week
             FROM   Import
             WHERE  hg = 4
             Union
             SELECT gueltig,
                    hg,
                    jahr,
                    woche,
                    k_ID,
                    ende,
                    0 as potential_ist,
                    potential as potential_vs,
                    0 as potential_vsx,
                    IIf( Import.hg <= 3,
                         IIf( IsNull( ziel ), Null, CWeek( ziel ) ),
                         IIf( IsNull( ende ), Null, CWeek( ende ) ) ) As week
             FROM   Import
             Union
             SELECT gueltig,
                    hg,
                    jahr,
                    woche,
                    k_ID,
                    ende,
                    0 as potential_ist,
                    0 as potential_vs,
                    potential as potential_vsx,
                    IIf( Import.hg <= 3 And
                         IIf( IsNull( ziel ), Null, CWeek( ziel ) ) > 23,
                         IIf( IsNull( ziel ), Null, CWeek( ziel ) ),
                         IIf( IIf( IsNull( ende ), Null, CWeek( ende ) ) <= 23,
                              IIf( IsNull( ende ), Null, CWeek( ende ) ), Null) ) As week
             FROM   Import) AS tbl1
             INNER JOIN Kostenstelle
                     ON tbl1.k_ID = Kostenstelle.k_ID)
             LEFT JOIN VBZ_Basis
                    ON tbl1.k_ID = VBZ_Basis.k_ID)
             LEFT JOIN VBZ_Woche
                    ON tbl1.week = VBZ_Woche.woche
             WHERE  tbl1.gueltig <> 0 AND
                    tbl1.hg <> 9 AND
                    tbl1.jahr = 2018 AND
                    tbl1.woche = 23 AND
                    Kostenstelle.name = '3105' AND
                    week >= 1 AND
                    VBZ_Woche.jahr = 2018 AND
                    VBZ_Woche.woche = week AND
                    ( Year( tbl1.ende ) = 2018 OR
                      IsNull( tbl1.ende ) OR
                      ( Year( tbl1.ende ) = 2018 - 1 AND
                        Month( tbl1.ende ) = 12 AND
                        CWeek( tbl1.ende ) = 1 ) OR
                      ( Year( tbl1.ende ) = 2018 + 1 AND
                        Month( tbl1.ende ) = 1 AND
                        CWeek( tbl1.ende ) >= 51 ) )
GROUP BY  week, VBZ_Basis.wert, VBZ_Woche.wert;


Ich würde die Einzelabfrage prüfen und CWeek Null-tauglich machen, um die vielen IIfs loszuwerden.
Grüße von der (⌒▽⌒)
 
Folgende Mitglieder bedankten sich: eviblabla