C11/System/OPTIMA/IQD/Belege/Kontenrahmen_kombiniert.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\belege\Kontenrahmen_kombiniert.imr
TITLE,Kontenrahmen_kombiniert.imr
BEGIN SQL
select distinct (rtrim(T1."ACCT_NR")) as c1,
	   CASE WHEN (T2."Make" IS NULL) THEN ('00') ELSE ((ucase(T2."Make"))) END as c2,
	   CASE WHEN ((((rtrim(T1."ACCT_NR"))) LIKE '2%') and (T3."SITE" IS NULL)) THEN ('01') ELSE ((od_right('00' || (od_left(T3."SITE",2)),2))) END as c3,
	   CASE WHEN ((T3."DEPARTMENT" IS NULL) or ((rtrim(T3."DEPARTMENT")) = '')) THEN ('00') ELSE (T3."DEPARTMENT") END as c4,
	   CASE WHEN ((T3."DESTINATION" = '00') and (T3."PRICE_CODE" = '#')) THEN ('99') WHEN ((T3."DESTINATION" = '00') and (T3."PRICE_CODE" <> '')) THEN (T3."PRICE_CODE") WHEN (((T3."DESTINATION" = '00') and (T3."PRICE_CODE" = '')) and (T4."CUSTOMER_GROUP" <> '')) THEN (T4."CUSTOMER_GROUP") WHEN ((rtrim(T3."DESTINATION")) = '') THEN ('00') WHEN (T3."DESTINATION" = 'FZ') THEN ('11') ELSE (T3."DESTINATION") END as c5,
	   (upper((truncate(CASE WHEN ((T5."MODEL_LINE" IS NOT NULL) and (T5."MODEL_LINE" <> '')) THEN (T5."MODEL_LINE") WHEN ((T3."MODEL_LINE" IS NOT NULL) and (T3."MODEL_LINE" <> '')) THEN (T3."MODEL_LINE") WHEN ((T3."PRODUCT_GROUP" IS NOT NULL) and (T3."PRODUCT_GROUP" <> '')) THEN (T3."PRODUCT_GROUP") WHEN ((T3."REPAIR_GROUP" IS NOT NULL) and (T3."REPAIR_GROUP" <> '')) THEN (T3."REPAIR_GROUP") ELSE ('00') END)))) as c6,
	   CASE WHEN ((T5."MODEL_LINE" IS NOT NULL) and (T5."MODEL_LINE" <> '')) THEN ('NW') WHEN ((T3."MODEL_LINE" IS NOT NULL) and (T3."MODEL_LINE" <> '')) THEN ('NW') WHEN ((T3."PRODUCT_GROUP" IS NOT NULL) and (T3."PRODUCT_GROUP" <> '')) THEN ('TZ') WHEN ((T3."REPAIR_GROUP" IS NOT NULL) and (T3."REPAIR_GROUP" <> '')) THEN ('SC') ELSE ('') END as c7
from "OPTIMA"."import"."ACCOUNT_INFO" T1,
	(("OPTIMA"."import"."ACCT_DOC_KEY" T3 left outer join "OPTIMA"."data"."GC_Marken" T2 on T2."Strategic_Area" = T3."STRATEGIC_AREA") left outer join "OPTIMA"."import"."vPP5Q" T5 on (T3."MODEL_LINE" = T5."MODEL_LINE") and (T3."MAKE" = T5."MAKE_CD")),
	(("OPTIMA"."import"."ACCT_DOC_DATA" T6 left outer join "OPTIMA"."import"."ACCT_DOC_SALESCLAS" T7 on T6."REFERENCE_IDENT" = T7."REFERENCE_IDENT") left outer join "OPTIMA"."import"."CUSTOMER" T4 on T4."CUSTOMER_NUMBER" = T7."CUSTOMER_NUMBER")
where (T1."ACCT_NR" = T3."ACCT_NO") and (T3."UNIQUE_IDENT" = T6."REFERENCE_IDENT")
and (T3."BOOKKEEP_DATE" >= TIMESTAMP '2020-01-01 00:00:00.000')
order by c1 asc,c3 asc,c4 asc,c2 asc

END SQL
COLUMN,0,Konto_Nr
COLUMN,1,Marke
COLUMN,2,Standort
COLUMN,3,Kostenstelle
COLUMN,4,Absatzkanal
COLUMN,5,Kostenträger
COLUMN,6,Kostenträger_Quelle