GAPS/Portal/System/IQD/Plan/NW_Plan_STK.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Plan_2006_Inso
DATASOURCENAME,D:\GAPS\Portal\System\IQD\Plan\NW_Plan_STK.imr
TITLE,NW_Plan_STK.imr
BEGIN SQL
select T1."Vertragshändler" as c1,
	   T1."Betrieb" as c2,
	   T1."Kostenstelle" as c3,
	   T1."Version" as c4,
	   T1."Jahr" as c5,
	   'verkaufte Stückzahlen' as c6,
	   T1."Verdichtung 10" as c7,
	   T1."Verdichtung 11" as c8,
	   T1."Verdichtung 12" as c9,
	   T1."Verdichtung 13" as c10,
	   T1."Menge 1" as c11,
	   T1."Menge 2" as c12,
	   T1."Menge 3" as c13,
	   T1."Menge 4" as c14,
	   T1."Menge 5" as c15,
	   T1."Menge 6" as c16,
	   T1."Menge 7" as c17,
	   T1."Menge 8" as c18,
	   T1."Menge 9" as c19,
	   T1."Menge 10" as c20,
	   T1."Menge 11" as c21,
	   T1."Menge 12" as c22,
	   T1."Wert 1" as c23,
	   T1."Wert 2" as c24,
	   T1."Wert 3" as c25,
	   T1."Wert 4" as c26,
	   T1."Wert 5" as c27,
	   T1."Wert 6" as c28,
	   T1."Wert 7" as c29,
	   T1."Wert 8" as c30,
	   T1."Wert 9" as c31,
	   T1."Wert 10" as c32,
	   T1."Wert 11" as c33,
	   T1."Wert 12" as c34,
	   CASE WHEN (T1."Verdichtung 13" = 'VE Behördenfahrzeuge') THEN ('NW Behörden') ELSE ('Neuwagen') END as c35,
	   T1."Menge 1" as c36,
	   T1."Menge 2" + T1."Menge 1" as c37,
	   T1."Menge 3" + (T1."Menge 2" + T1."Menge 1") as c38,
	   T1."Menge 4" + (T1."Menge 3" + (T1."Menge 2" + T1."Menge 1")) as c39,
	   T1."Menge 5" + (T1."Menge 4" + (T1."Menge 3" + (T1."Menge 2" + T1."Menge 1"))) as c40,
	   T1."Menge 6" + (T1."Menge 5" + (T1."Menge 4" + (T1."Menge 3" + (T1."Menge 2" + T1."Menge 1")))) as c41,
	   T1."Menge 7" + (T1."Menge 6" + (T1."Menge 5" + (T1."Menge 4" + (T1."Menge 3" + (T1."Menge 2" + T1."Menge 1"))))) as c42,
	   T1."Menge 8" + (T1."Menge 7" + (T1."Menge 6" + (T1."Menge 5" + (T1."Menge 4" + (T1."Menge 3" + (T1."Menge 2" + T1."Menge 1")))))) as c43,
	   T1."Menge 9" + (T1."Menge 8" + (T1."Menge 7" + (T1."Menge 6" + (T1."Menge 5" + (T1."Menge 4" + (T1."Menge 3" + (T1."Menge 2" + T1."Menge 1"))))))) as c44,
	   T1."Menge 10" + (T1."Menge 9" + (T1."Menge 8" + (T1."Menge 7" + (T1."Menge 6" + (T1."Menge 5" + (T1."Menge 4" + (T1."Menge 3" + (T1."Menge 2" + T1."Menge 1")))))))) as c45,
	   T1."Menge 11" + (T1."Menge 10" + (T1."Menge 9" + (T1."Menge 8" + (T1."Menge 7" + (T1."Menge 6" + (T1."Menge 5" + (T1."Menge 4" + (T1."Menge 3" + (T1."Menge 2" + T1."Menge 1"))))))))) as c46,
	   T1."Menge 12" + (T1."Menge 11" + (T1."Menge 10" + (T1."Menge 9" + (T1."Menge 8" + (T1."Menge 7" + (T1."Menge 6" + (T1."Menge 5" + (T1."Menge 4" + (T1."Menge 3" + (T1."Menge 2" + T1."Menge 1")))))))))) as c47,
	   'G.V.O.' as c48,
	   CASE WHEN (T1."Betrieb" = 'POS Mainzer Landstrasse') THEN ('01') WHEN (T1."Betrieb" = 'POS Nordwest-Stadt') THEN ('02') WHEN (T1."Betrieb" = 'POS Eschborn') THEN ('03') WHEN (T1."Betrieb" = 'POS Hanauer Landstrasse') THEN ('04') WHEN (T1."Betrieb" = 'POS Offenbach') THEN ('05') WHEN (T1."Betrieb" = 'POS Neu-Isenburg') THEN ('06') WHEN (T1."Betrieb" = 'POS Enkheim') THEN ('08') WHEN (T1."Betrieb" = 'POS Nürnberger Strasse') THEN ('09') WHEN (T1."Betrieb" = 'POS RSL') THEN ('07') ELSE null END as c49,
	   '1' as c50
from "NW_imp_all_O21" T1
where (((T1."Verdichtung 10" = 'VE Neuwagen') and ((CASE WHEN (T1."Betrieb" = 'POS Mainzer Landstrasse') THEN ('01') WHEN (T1."Betrieb" = 'POS Nordwest-Stadt') THEN ('02') WHEN (T1."Betrieb" = 'POS Eschborn') THEN ('03') WHEN (T1."Betrieb" = 'POS Hanauer Landstrasse') THEN ('04') WHEN (T1."Betrieb" = 'POS Offenbach') THEN ('05') WHEN (T1."Betrieb" = 'POS Neu-Isenburg') THEN ('06') WHEN (T1."Betrieb" = 'POS Enkheim') THEN ('08') WHEN (T1."Betrieb" = 'POS Nürnberger Strasse') THEN ('09') WHEN (T1."Betrieb" = 'POS RSL') THEN ('07') ELSE null END) IS NOT NULL)) and (T1."Version" IN (107,110)))

END SQL
COLUMN,0,Rechtseinheit_ori
COLUMN,1,Betrieb_alt
COLUMN,2,Kostenstelle_ori
COLUMN,3,Version
COLUMN,4,Jahr
COLUMN,5,Ebene1
COLUMN,6,Verdichtung 10_ori
COLUMN,7,Ebene3
COLUMN,8,Ebene4
COLUMN,9,Ebene5
COLUMN,10,Wert 1_alt
COLUMN,11,Wert 2_alt
COLUMN,12,Wert 3_alt
COLUMN,13,Wert 4_alt
COLUMN,14,Wert 5_alt
COLUMN,15,Wert 6_alt
COLUMN,16,Wert 7_alt
COLUMN,17,Wert 8_alt
COLUMN,18,Wert 9_alt
COLUMN,19,Wert 10_alt
COLUMN,20,Wert 11_alt
COLUMN,21,Wert 12_alt
COLUMN,22,Wert 1_ori
COLUMN,23,Wert 2_ori
COLUMN,24,Wert 3_ori
COLUMN,25,Wert 4_ori
COLUMN,26,Wert 5_ori
COLUMN,27,Wert 6_ori
COLUMN,28,Wert 7_ori
COLUMN,29,Wert 8_ori
COLUMN,30,Wert 9_ori
COLUMN,31,Wert 10_ori
COLUMN,32,Wert 11_ori
COLUMN,33,Wert 12_ori
COLUMN,34,Ebene2
COLUMN,35,Wert 1
COLUMN,36,Wert 2
COLUMN,37,Wert 3
COLUMN,38,Wert 4
COLUMN,39,Wert 5
COLUMN,40,Wert 6
COLUMN,41,Wert 7
COLUMN,42,Wert 8
COLUMN,43,Wert 9
COLUMN,44,Wert 10
COLUMN,45,Wert 11
COLUMN,46,Wert 12
COLUMN,47,Rechtseinheit
COLUMN,48,Betrieb
COLUMN,49,Kostenstelle