ETL_DWH_LOCOSOFT/schema/GC/views/transform.offene_Auftraege_Teile.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE     VIEW [transform].[offene_Auftraege_Teile] AS

select "GC_Department"."Hauptbetrieb_ID"                                                     AS "Hauptbetrieb_ID",
       "GC_Department"."Hauptbetrieb_Name"                                                   AS "Hauptbetrieb_Name",
       "GC_Department"."Standort_ID"                                                         AS "Standort_ID",
       "GC_Department"."Standort_Name"                                                       AS "Standort_Name",
       "offene_Auftraege_Teile"."Serviceberater"                                             AS "Serviceberater",
       "offene_Auftraege_Teile"."Order Number"                                               AS "Order_Number",
       case when "GC_Marken"."Fabrikat" is null then 'Fremd' else "GC_Marken"."Fabrikat" end AS "Fabrikat",
       case
           when case when "GC_Marken"."Fabrikat" is null then 'Fremd' else "GC_Marken"."Fabrikat" end = 'Fremd' then 9
           else "GC_Marken"."Order_By" end                                                   AS "Fabrikat_Order_By",
       "offene_Auftraege_Teile"."Model"                                                      AS "Model",
       ''                                                                                    AS "Fahrzeug",
       ''                                                                                    AS "Kostenstelle",
       "offene_Auftraege_Teile"."Description_Makes"                                          AS "Marke",
       "offene_Auftraege_Teile"."Kunde"                                                      AS "Kunde",
       "offene_Auftraege_Teile"."Umsatzart"                                                  AS "Turnover_Type_Desc",
       0                                                                                     AS "Tage_offen",
       0                                                                                     AS "DG",
       0                                                                                     AS "Lohn",
       "offene_Auftraege_Teile"."Teile"                                                      AS "Teile",
       0                                                                                     AS "Sonst_",
       "offene_Auftraege_Teile"."Order Date"                                                 AS "Invoice_Date"
from (locosoft."offene_Auftraege_Teile" "offene_Auftraege_Teile" LEFT OUTER JOIN "data"."GC_Department" "GC_Department"
      on "offene_Auftraege_Teile"."Hauptbetrieb" = "GC_Department"."Hauptbetrieb" and
         "offene_Auftraege_Teile"."Standort" = "GC_Department"."Standort")
         LEFT OUTER JOIN "data"."GC_Marken" "GC_Marken"
                         on "offene_Auftraege_Teile"."Fabrikat" = "GC_Marken"."Description"
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO