migration "iff" "then"

Question

hallo zusammen,

im zuge meiner migration bin ich auf ein weiteres problem gestoßen.

es geht darum dass ich mit einer abfrage nicht vorhandene datensätze erstelle. es geht hier um verträge die über jahre laufen...
folgendes iff statement möchte ich dabei nun in eine afrage im sql server integrieren.

meine frage ist: kann ich case statement mit AND oder OR verbinden und daraus dann mehrere bedingungen in einer haben?

access:

 Iff([tbl_vertrag_ausnahme.vertrag_id] Is Null,
  IIf(([abfr_vertrag_recur_koord.wert_recur_format_id] Is Null)
  Or ([abfr_vertrag_recur_koord.vertrag_freq] Is Null)
  Or ([abfr_vertrag_recur_koord.instanz_id] Is Null)
  ,[abfr_vertrag_recur_koord.vertrag_von],
  DateAdd([abfr_vertrag_recur_koord.wert_recur_format_id],
  [abfr_vertrag_recur_koord.instanz_id]*[abfr_vertrag_recur_koord.vertrag_freq],
  [abfr_vertrag_recur_koord.vertrag_von])),IIf([instanz_aktiv],Null,[instanz_datum])) AS vertrag_datum_recur

im sql server habe ich das in unzähligen varianten z.b. so versucht:

CASE

WHEN vrw.tbl_vertrag_exc.vertrag_exc_id IS NULL,

WHEN vrw.abfr_vertrag_recur_koord.fk_w_recur_format_id IS NULL OR
WHEN vrw.abfr_vertrag_recur_koord.vertrag_freq IS NULL OR
WHEN vrw.abfr_vertrag_recur_koord.instanz_id IS NULL

THEN vrw.abfr_vertrag_recur_koord.vertrag_von

ELSE

'wert'

END AS test

 

muss ich für jede bedingung (case when then else end as) verwenden?

 

grüße

hannes

 

 

 

 

Answer 1

hallo hannes,

muss ich für jede bedingung (case when then else end as) verwenden?

Dein Denkfehler liegt in der Trennung von SQL und Bool'scher Logik. Es muss in etwa so lauten:

CASE
  WHEN vrw.tbl_vertrag_exc.vertrag_exc_id IS NULL
    THEN value,
  WHEN vrw.abfr_vertrag_recur_koord.fk_w_recur_format_id IS NULL OR
       vrw.abfr_vertrag_recur_koord.vertrag_freq IS NULL OR
       vrw.abfr_vertrag_recur_koord.instanz_id IS NULL
    THEN vrw.abfr_vertrag_recur_koord.vertrag_von
  ELSE 'wert'
END AS test

Siehe

http://msdn.microsoft.com/de-de/library/ms181765.aspx

btw, verwende Tabellen-Aliasse, macht das ganze lesbarer:

http://msdn.microsoft.com/de-de/library/ms176104%28SQL.90%29.aspx

E.g.

SELECT  T1.*,
        T2.*
FROM    Tabelle1 T1
        INNER JOIN Tabelle2 T2 ON T1.ID = T2.idTabelle1
ORDER BY T2.ID ;

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 2

Hallo Hannes,

IIF bildet man im SQL Server über CASE - Ausdrücke ab - beachte dass es dort zwei Varianten gibt.
In einigen Fällen wie obigem Null-Test kann man auch COALESCE oder ISNULL verwenden.

DATEADD erwartet wiederum eine Bereichsangabe, die jedoch keine Variable sein darf!
Weswegen man ebenfalls CASE einsetzen müsste.

Der obige Ausdruck (mit einigen Wagnissen) aus dem Stegreif übersetzt - Tippfehler vorbehalten:

  
SELECT CASE WHEN tbl_vertrag_ausnahme.vertrag_id IS NULL
   THEN 
    CASE WHEN abfr_vertrag_recur_koord.wert_recur_format_id IS NULL
      OR abfr_vertrag_recur_koord.vertrag_freq IS NULL
      OR abfr_vertrag_recur_koord.instanz_id IS NULL
      THEN abfr_vertrag_recur_koord.vertrag_von
      ELSE
        -- kann durch eine benutzerdefiniert Funktion vereinfacht werden 
        CASE abfr_vertrag_recur_koord.wert_recur_format_id
          WHEN 'd' THEN DATEADD(dd, abfr_vertrag_recur_koord.instanz_id * abfr_vertrag_recur_koord.vertrag_freq, bfr_vertrag_recur_koord.vertrag_von)
          WHEN 'm' THEN DATEADD(mm, abfr_vertrag_recur_koord.instanz_id * abfr_vertrag_recur_koord.vertrag_freq, bfr_vertrag_recur_koord.vertrag_von)
          -- ggf. weitere Formate
          ELSE DATEADD(yyyy, abfr_vertrag_recur_koord.instanz_id * abfr_vertrag_recur_koord.vertrag_freq, bfr_vertrag_recur_koord.vertrag_von)
        END
    END
  ELSE
    -- muss ein Ausdruck, implizite boolsche Ausdrücke gibt es nicht 
    -- hier ISNULL (um Null auszuschliessen, falls möglich) und umgedrehter Bedingung
    CASE WHEN ISNULL(instanz_aktiv, 0) = 0 
      THEN instanz_datum
      ELSE NULL END
    END
  AS vertrag_datum_recur

Für das (etwas umständliche) CASE um das DATEADD auszuführen, solltest Du Dir eine benutzerdefinierte Skalar Funktion schreiben.

Für die Bedingung Beim letzten ELSE musst Du beachten, dass der SQL Server nur vollständige Ausdrücke akzeptiert,
da es eine "boolschen Ausdruck" wie in Access nicht gibt - das dort alles <> 0 als Wahr ansieht.
Ob meine Interpretation dort stimmt, hängt von den möglichen Werten und Datentyp von instanz_aktiv ab.

Gruß Elmar

 

Answer 3

hallo stefan,

habe verstanden :-)
bzgl access war ihr sehr verwöhnt was tabellennamen anging. ich hab sie ja kaum gesehen aber hier ist mir das auch schon sehr störend aufgefallen.

wer soll sich da nich auskennen :-) also werde ich auch das in angriff nehmen.

momentan sieht meine abfrage also wie folgt aus.

SELECT
  vrw.abfr_vertrag_recur_koord.vertrag_id,
  vrw.abfr_vertrag_recur_koord.instanz_id,
 
CASE
  WHEN vrw.tbl_vertrag_exc.vertrag_exc_id IS NULL
    THEN vrw.abfr_vertrag_recur_koord.vertrag_von
     WHEN vrw.tbl_vertrag_exc.instanz_aktiv IS NULL
      THEN vrw.tbl_vertrag_exc.instanz_datum
  WHEN vrw.abfr_vertrag_recur_koord.fk_w_recur_format_id IS NULL OR
       vrw.abfr_vertrag_recur_koord.vertrag_freq IS NULL OR
       vrw.abfr_vertrag_recur_koord.instanz_id IS NULL
    THEN vrw.abfr_vertrag_recur_koord.vertrag_von
  ELSE
       DATEADD(yyyy,vrw.abfr_vertrag_recur_koord.instanz_id*vrw.abfr_vertrag_recur_koord.vertrag_freq,
              vrw.abfr_vertrag_recur_koord.vertrag_von)
 
   
          
END AS vertrag_datum_recur,

    vrw.abfr_vertrag_recur_koord.vertrag_nr,
    vrw.tbl_vertrag_exc.instanz_aktiv,
    vrw.tbl_vertrag_exc.instanz_info,
    vrw.abfr_vertrag_recur_koord.vertrag_von,
    vrw.abfr_vertrag_recur_koord.vertrag_repeat,
    vrw.abfr_vertrag_recur_koord.vertrag_freq,
    vrw.abfr_vertrag_recur_koord.fk_w_recur_format_id,
    werte.tbl_w_recur_format.wert_recur_format_type

FROM
  vrw.abfr_vertrag_recur_koord
  LEFT OUTER JOIN werte.tbl_w_recur_format ON (vrw.abfr_vertrag_recur_koord.fk_w_recur_format_id = werte.tbl_w_recur_format.wert_recur_format_id)
  LEFT OUTER JOIN vrw.tbl_vertrag_exc ON (vrw.abfr_vertrag_recur_koord.vertrag_id = vrw.tbl_vertrag_exc.vertrag_exc_id)
  AND (vrw.abfr_vertrag_recur_koord.instanz_id = vrw.tbl_vertrag_exc.fk_instanz_id)

 

in access war es auch möglich mit diversen daten die manipuliert wurden, weiterzuarbeiten.

bei dieser abfrage geht es darum auf das AS ergebnis "vertrag_datum_recur" ein datediff anzuwenden.

das scheint beim sql server nicht möglich zu sein, oder doch?

vertrag_datum_recur - vertrag_del_frist AS vertrag_datum_frist

funktioniert auf jedenfall nicht.

 

müsste ich vertrag_datum_recur irgendwie "setzten" oder wie funktioniert sowas generell?

 

grüße hannes

 

Answer 4

servus Hannes,

in access war es auch möglich mit diversen daten die manipuliert wurden, weiterzuarbeiten.

Das geht so nicht. Liegt daran wie der SQL Server Abfragen bearbeitet.

Du hast zwei Möglichkeiten:

1. Copy'n'Paste, d.h. du wiederholst die Berechnungen an den Stellen wo du sie brauchst.

2. Du nutzt Subqueries zur Berechnung, etwa so:

a)

SELECT  Q.*,
        calcFeld * 12 AS jahresWert
FROM    ( SELECT    T.*,
                    T.feld1 + T.feld2 AS calcFeld
          FROM      tabelle T
        ) Q
WHERE   Q.calcFeld = 123 ;

b)

WITH    cte
          AS ( SELECT   T.*,
                        T.feld1 + T.feld2 AS calcFeld
               FROM     tabelle T
             )
    SELECT  Q.*,
            calcFeld * 12 AS jahresWert
    FROM    cte Q
    WHERE   Q.calcFeld = 123 ;

Welche Variante ist zu bevorzugen? Ich nutze meistens erstmal die CTE

http://technet.microsoft.com/en-us/library/ms175972.aspx

da leichter lesbar. Allerdings gilt hier immer: Performancetest im laufenden Betrieb sind notwendig, da in verschiedenen Szenarian mal die eine mal die andere schneller ist.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 5

Hallo Hannes,

doch das geht, abe rnur über einen Umweg über eine cte, in der Du die ganzen berechnetten Felder generierst.
Das Select geht dann über die cte.

Eine kleine Empfehlung noch von mir: verwende Alias für die Tabellen, das macht es lesbarer. Außerdem hast Du in manchen Statements Probleme, wenn Du den mehrteiligen nanemn verwendest.

Answer 6

danke euch beiden!

jetzt habe ich erstmal hausaufgaben zu machen :-)