C11/System/CARLO/IQD/NW/nw_ae.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\CARLO\IQD\nw\nw_ae.imr
TITLE,nw_ae.imr
BEGIN SQL
select '1' as c1,
	   CASE WHEN (T1."Lagerortcode" IN ('00HB','01F1')) THEN ('00') ELSE ((substring(T1."Lagerortcode" from 1 for 2))) END as c2,
	   T2."Code" as c3,
	   T2."Name" as c4,
	   T2."Name" as c5,
	   T3."Markencode" as c6,
	   T4."Markencode" as c7,
	   T1."Verk_ an Name" as c8,
	   CASE WHEN (T1."Fahrgestellnummer" IS NOT NULL) THEN (T1."Nr_" || ' - ' || T1."Fahrgestellnummer" || ' - ' || T1."Verk_ an Name") ELSE (T1."Nr_" || ' - ' || T1."Verk_ an Name") END as c9,
	   T3."Fahrzeugstatus" as c10,
	   T3."Statistikgruppe" as c11,
	   T3."Erstzulassung" as c12,
	   T3."Anzahl Vorbesitzer" as c13,
	   T3."Lagerbuchungsgruppe" as c14,
	   CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((substring(T3."Produktbuchungsgruppe" from 1 for 6)) IN ('F_MIET')) THEN ('Mietwagen') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWD%') THEN ('GW diffb.') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWR%') THEN ('GW regelb.') ELSE ('Neuwagen') END as c15,
	   T3."Produktbuchungsgruppe" as c16,
	   CASE WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((substring(T3."Produktbuchungsgruppe" from 1 for 6)) IN ('F_MIET')) THEN ('Mietwagen') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWD%') THEN ('GW diffb.') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWR%') THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('GW regelb.','GW diffb.')) THEN ('Gebrauchtwagen') WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((substring(T3."Produktbuchungsgruppe" from 1 for 6)) IN ('F_MIET')) THEN ('Mietwagen') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWD%') THEN ('GW diffb.') WHEN (T3."Produktbuchungsgruppe" LIKE '%GWR%') THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('Vorführwagen','Neuwagen','Mietwagen')) THEN ('Neuwagen') ELSE null END as c17,
	   '' as c18,
	   T3."Produktbuchungsgruppe" as c19,
	   CASE WHEN (T1."Fahrgestellnummer" = '') THEN (T1."Nr_" || ' - ' || (CASE WHEN (T1."Lagerortcode" IN ('00HB','01F1')) THEN ('00') ELSE ((substring(T1."Lagerortcode" from 1 for 2))) END) || ' - ' || T2."Name" || ' / ' || T1."Verk_ an Name" || ' - ' || (asciiz(extract(YEAR FROM T1."Auftragsdatum"),4) || '-' || asciiz(extract(MONTH FROM T1."Auftragsdatum"),2) || '-' || asciiz(extract(DAY FROM T1."Auftragsdatum"),2))) ELSE (T1."Nr_" || ' - ' || T1."Fahrgestellnummer" || ' - ' || (CASE WHEN (T1."Lagerortcode" IN ('00HB','01F1')) THEN ('00') ELSE ((substring(T1."Lagerortcode" from 1 for 2))) END) || ' - ' || T3."Produktbuchungsgruppe" || ' - ' || T2."Name" || ' / ' || T1."Verk_ an Name" || ' - ' || (asciiz(extract(YEAR FROM T1."Auftragsdatum"),4) || '-' || asciiz(extract(MONTH FROM T1."Auftragsdatum"),2) || '-' || asciiz(extract(DAY FROM T1."Auftragsdatum"),2))) END as c20,
	   1 as c21,
	   T1."Belegart" as c22,
	   T1."Nr_" as c23,
	   T1."Verk_ an Deb_-Nr_" as c24,
	   T1."Rech_ an Deb_-Nr_" as c25,
	   T1."Rech_ an Name" as c26,
	   T1."Rech_ an Name 2" as c27,
	   T1."Auftragsdatum" as c28,
	   T1."Buchungsdatum" as c29,
	   T1."Lieferdatum" as c30,
	   T1."Lagerortcode" as c31,
	   T1."Kostenstellencode" as c32,
	   T1."Markencode" as c33,
	   T1."Debitorenbuchungsgruppe" as c34,
	   T1."VK-Preise inkl_ MWSt" as c35,
	   T1."Mengenrabatt zulassen" as c36,
	   T1."Verkäufercode" as c37,
	   T1."Auftragsgruppe" as c38,
	   T1."Rechnung" as c39,
	   T1."Buchungsnr_" as c40,
	   T1."Letzte Buchungsnr_" as c41,
	   T1."Sammelrechnung" as c42,
	   T1."Geschäftsbuchungsgruppe" as c43,
	   T1."Verk_ an Name" as c44,
	   T1."Verk_ an Name 2" as c45,
	   T1."Belegdatum" as c46,
	   T1."Reservieren" as c47,
	   T1."Fahrgestellnummer" as c48,
	   T1."Amtliches Kennzeichen" as c49,
	   T1."Modell" as c50,
	   T1."Auftragsart" as c51,
	   T1."Kundenbestätigungdatum" as c52,
	   T1."Bestellart" as c53,
	   T1."Erstes Lieferdatum" as c54,
	   T1."Letztes Lieferdatum" as c55,
	   T1."Hauptlieferdatum" as c56,
	   T1."Fahrzeug VK-Preisdatum" as c57,
	   T1."Finanzierung_Leasing" as c58,
	   T3."Modell" as c59,
	   T3."Modell" as c60,
	   CASE WHEN ((CASE WHEN (T1."Fahrgestellnummer" = '') THEN (T1."Nr_" || ' - ' || (CASE WHEN (T1."Lagerortcode" IN ('00HB','01F1')) THEN ('00') ELSE ((substring(T1."Lagerortcode" from 1 for 2))) END) || ' - ' || T2."Name" || ' / ' || T1."Verk_ an Name" || ' - ' || (asciiz(extract(YEAR FROM T1."Auftragsdatum"),4) || '-' || asciiz(extract(MONTH FROM T1."Auftragsdatum"),2) || '-' || asciiz(extract(DAY FROM T1."Auftragsdatum"),2))) ELSE (T1."Nr_" || ' - ' || T1."Fahrgestellnummer" || ' - ' || (CASE WHEN (T1."Lagerortcode" IN ('00HB','01F1')) THEN ('00') ELSE ((substring(T1."Lagerortcode" from 1 for 2))) END) || ' - ' || T3."Produktbuchungsgruppe" || ' - ' || T2."Name" || ' / ' || T1."Verk_ an Name" || ' - ' || (asciiz(extract(YEAR FROM T1."Auftragsdatum"),4) || '-' || asciiz(extract(MONTH FROM T1."Auftragsdatum"),2) || '-' || asciiz(extract(DAY FROM T1."Auftragsdatum"),2))) END) IS NULL) THEN ((CASE WHEN (T1."Fahrgestellnummer" IS NOT NULL) THEN (T1."Nr_" || ' - ' || T1."Fahrgestellnummer" || ' - ' || T1."Verk_ an Name") ELSE (T1."Nr_" || ' - ' || T1."Verk_ an Name") END)) ELSE ((CASE WHEN (T1."Fahrgestellnummer" = '') THEN (T1."Nr_" || ' - ' || (CASE WHEN (T1."Lagerortcode" IN ('00HB','01F1')) THEN ('00') ELSE ((substring(T1."Lagerortcode" from 1 for 2))) END) || ' - ' || T2."Name" || ' / ' || T1."Verk_ an Name" || ' - ' || (asciiz(extract(YEAR FROM T1."Auftragsdatum"),4) || '-' || asciiz(extract(MONTH FROM T1."Auftragsdatum"),2) || '-' || asciiz(extract(DAY FROM T1."Auftragsdatum"),2))) ELSE (T1."Nr_" || ' - ' || T1."Fahrgestellnummer" || ' - ' || (CASE WHEN (T1."Lagerortcode" IN ('00HB','01F1')) THEN ('00') ELSE ((substring(T1."Lagerortcode" from 1 for 2))) END) || ' - ' || T3."Produktbuchungsgruppe" || ' - ' || T2."Name" || ' / ' || T1."Verk_ an Name" || ' - ' || (asciiz(extract(YEAR FROM T1."Auftragsdatum"),4) || '-' || asciiz(extract(MONTH FROM T1."Auftragsdatum"),2) || '-' || asciiz(extract(DAY FROM T1."Auftragsdatum"),2))) END)) END as c61,
	   T4."Beschreibung" as c62,
	   T4."Zeilennr_" as c63,
	   CASE WHEN (T4."Beschreibung" IS NOT NULL) THEN ((upper((substring(T4."Beschreibung" from 1 for 4))))) ELSE ('NV') END as c64,
	   T4."Markencode" as c65,
	   1 as c66,
	   ((substring((upper(T4."Beschreibung")) from 1 for 4))) as c67
from ((("CARLO"."import"."Verkaufskopf" T1 left outer join "CARLO"."import"."Verkaeufer_Einkaeufer" T2 on (T1."Verkäufercode" = T2."Code") and (T1."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Fahrzeug" T3 on (T1."Fahrgestellnummer" = T3."Fahrgestellnummer") and (T1."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Verkaufszeile" T4 on (T1."Nr_" = T4."Belegnr_") and (T1."Client_DB" = T4."Client_DB"))
where ((((T1."Belegart" = 1) and (T1."Nr_" LIKE '%FVAN%')) and (T1."Auftragsdatum" >= TIMESTAMP '2014-01-01 00:00:00.000')) and (T4."Zeilennr_" = 10000))
order by c23 asc,c48 asc

END SQL
COLUMN,0,Hauptbetrieb
COLUMN,1,Standort
COLUMN,2,Code_verkäufer_einkäufer
COLUMN,3,Name_verkäufer_einkäufer
COLUMN,4,Verkäufer
COLUMN,5,Markencode_FZG
COLUMN,6,Fabrikat
COLUMN,7,Kunde
COLUMN,8,FZG
COLUMN,9,Fahrzeugstatus
COLUMN,10,Statistikgruppe
COLUMN,11,Erstzulassung
COLUMN,12,Anzahl Vorbesitzer
COLUMN,13,Lagerbuchungsgruppe
COLUMN,14,Fahrzeugtyp
COLUMN,15,Produktbuchungsgruppe
COLUMN,16,Fahrzeugart
COLUMN,17,Kundenart
COLUMN,18,Fahrzeugtyp_1
COLUMN,19,FZG_Detail_1
COLUMN,20,Menge
COLUMN,21,Belegart
COLUMN,22,Nr
COLUMN,23,Verk An Deb -nr
COLUMN,24,Rech An Deb -nr
COLUMN,25,Rech An Name
COLUMN,26,Rech An Name 2
COLUMN,27,Auftragsdatum
COLUMN,28,Buchungsdatum
COLUMN,29,Lieferdatum
COLUMN,30,Lagerortcode
COLUMN,31,Kostenstellencode
COLUMN,32,Markencode
COLUMN,33,Debitorenbuchungsgruppe
COLUMN,34,Vk-preise Inkl Mwst
COLUMN,35,Mengenrabatt Zulassen
COLUMN,36,Verkäufercode
COLUMN,37,Auftragsgruppe
COLUMN,38,Rechnung
COLUMN,39,Buchungsnr
COLUMN,40,Letzte Buchungsnr
COLUMN,41,Sammelrechnung
COLUMN,42,Geschäftsbuchungsgruppe
COLUMN,43,Verk An Name
COLUMN,44,Verk An Name 2
COLUMN,45,Belegdatum
COLUMN,46,Reservieren
COLUMN,47,Fahrgestellnummer
COLUMN,48,Amtliches Kennzeichen
COLUMN,49,Modell
COLUMN,50,Auftragsart
COLUMN,51,Kundenbestätigungdatum
COLUMN,52,Bestellart
COLUMN,53,Erstes Lieferdatum
COLUMN,54,Letztes Lieferdatum
COLUMN,55,Hauptlieferdatum
COLUMN,56,Fahrzeug Vk-preisdatum
COLUMN,57,Finanzierung Leasing
COLUMN,58,Model_ori
COLUMN,59,Modell_FZG
COLUMN,60,FZG_Detail
COLUMN,61,Beschreibung
COLUMN,62,Zeilennr
COLUMN,63,Model
COLUMN,64,Markencode_Zeile
COLUMN,65,Auftragsbestand
COLUMN,66,Fahrzeugtyp_1_neu