GAPS/Portal/System/IQD/Belege/GVO_GuV_4_Autonom.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GVO_Fibu_Beleg
DATASOURCENAME,D:\GAPS\Portal\System\IQD\Belege\GVO_GuV_4_Autonom.imr
TITLE,GVO_GuV_4_Autonom.imr
BEGIN SQL
select T1."Betrieb" as c1,
	   T1."Hauptbetrieb" as c2,
	   T1."Rechtseinheit" as c3,
	   T1."Feld1" as c4,
	   T1."Feld2" as c5,
	   T1."Feld3" as c6,
	   T1."Feld4" as c7,
	   T1."Feld5" as c8,
	   T1."Feld6" as c9,
	   T1."Feld7" as c10,
	   T1."Feld8" as c11,
	   T1."Betrag" as c12,
	   T1."Feld9" as c13,
	   T1."Kst" as c14,
	   T1."Kto" as c15,
	   T1."Name" as c16,
	   T1."Verdichtung 1" as c17,
	   T1."Verdichtung 2" as c18,
	   T1."Verdichtung 3" as c19,
	   T1."Verdichtung 4" as c20,
	   T1."Verdichtung 5" as c21,
	   T1."Verdichtung 6" as c22,
	   T1."Verdichtung 7" as c23,
	   T1."Verdichtung 8" as c24,
	   T1."Verdichtung 9" as c25,
	   T1."Verdichtung 10" as c26,
	   T1."Verdichtung 11" as c27,
	   T1."Verdichtung 12" as c28,
	   T1."Verdichtung 13" as c29,
	   T1."Verdichtung 14" as c30,
	   CASE WHEN (T1."Betrieb" IS NULL) THEN ('POS Mainzer Landstrasse') ELSE (T1."Betrieb") END as c31,
	   'G.V.O.' as c32,
	   CASE WHEN (T1."Name" IS NOT NULL) THEN (T1."Kto" || ' - ' || T1."Name") ELSE (T1."Kto") END as c33,
	   T1."Kst" as c34,
	   (od_left(T1."Feld6",2)) || '.' || (substring(T1."Feld6" from 3 for 2)) || '.' || (od_right(T1."Feld6",2)) as c35,
	   CASE WHEN ((CASE WHEN (T1."Betrieb" IS NULL) THEN ('POS Mainzer Landstrasse') ELSE (T1."Betrieb") END) = 'POS Mainzer Landstrasse') THEN ('01') WHEN ((CASE WHEN (T1."Betrieb" IS NULL) THEN ('POS Mainzer Landstrasse') ELSE (T1."Betrieb") END) = 'POS Nordwest-Stadt') THEN ('02') WHEN ((CASE WHEN (T1."Betrieb" IS NULL) THEN ('POS Mainzer Landstrasse') ELSE (T1."Betrieb") END) = 'POS Eschborn') THEN ('03') WHEN ((CASE WHEN (T1."Betrieb" IS NULL) THEN ('POS Mainzer Landstrasse') ELSE (T1."Betrieb") END) = 'POS Hanauer Landstrasse') THEN ('04') WHEN ((CASE WHEN (T1."Betrieb" IS NULL) THEN ('POS Mainzer Landstrasse') ELSE (T1."Betrieb") END) = 'POS Offenbach') THEN ('05') WHEN ((CASE WHEN (T1."Betrieb" IS NULL) THEN ('POS Mainzer Landstrasse') ELSE (T1."Betrieb") END) = 'POS Neu-Isenburg') THEN ('06') WHEN ((CASE WHEN (T1."Betrieb" IS NULL) THEN ('POS Mainzer Landstrasse') ELSE (T1."Betrieb") END) = 'POS Enkheim') THEN ('08') WHEN ((CASE WHEN (T1."Betrieb" IS NULL) THEN ('POS Mainzer Landstrasse') ELSE (T1."Betrieb") END) = 'POS Nürnberger Strasse') THEN ('09') ELSE null END as c36,
	   (od_left(T1."Kst",1)) as c37,
	   CASE WHEN (((od_left(T1."Kst",1))) = '3') THEN ('6') WHEN (((od_left(T1."Kst",1))) = '4') THEN ('3') WHEN (((od_left(T1."Kst",1))) = '5') THEN ('4') WHEN (((od_left(T1."Kst",1))) = '6') THEN ('5') WHEN (((od_left(T1."Kst",1))) = '8') THEN ('9') ELSE (((od_left(T1."Kst",1)))) END as c38,
	   T1."Feld1" as c39,
	   ((od_left(T1."Feld6",2)) || '.' || (substring(T1."Feld6" from 3 for 2)) || '.' || (od_right(T1."Feld6",2))) as c40,
	   '' as c41,
	   (CASE WHEN (((od_left(T1."Kst",1))) = '3') THEN ('6') WHEN (((od_left(T1."Kst",1))) = '4') THEN ('3') WHEN (((od_left(T1."Kst",1))) = '5') THEN ('4') WHEN (((od_left(T1."Kst",1))) = '6') THEN ('5') WHEN (((od_left(T1."Kst",1))) = '8') THEN ('9') ELSE (((od_left(T1."Kst",1)))) END) as c42
from "s_089900_4_Tab" T1

END SQL
COLUMN,0,Betrieb_ori
COLUMN,1,Hauptbetrieb
COLUMN,2,Rechtseinheit_ori
COLUMN,3,Konto Nr
COLUMN,4,Betrieb Nr
COLUMN,5,Bezeichnung
COLUMN,6,Feld4
COLUMN,7,Belegnr
COLUMN,8,Feld6
COLUMN,9,Beleg
COLUMN,10,B-text
COLUMN,11,Betrag
COLUMN,12,Summe
COLUMN,13,Kst
COLUMN,14,Kto_ori
COLUMN,15,Name
COLUMN,16,Verdichtung 1
COLUMN,17,Verdichtung 2
COLUMN,18,Verdichtung 3
COLUMN,19,Verdichtung 4
COLUMN,20,Verdichtung 5
COLUMN,21,Verdichtung 6
COLUMN,22,Verdichtung 7
COLUMN,23,Verdichtung 8
COLUMN,24,Verdichtung 9
COLUMN,25,Verdichtung 10
COLUMN,26,Verdichtung 11
COLUMN,27,Verdichtung 12
COLUMN,28,Verdichtung 13
COLUMN,29,Verdichtung 14
COLUMN,30,Betrieb_alt
COLUMN,31,Rechtseinheit
COLUMN,32,Kto
COLUMN,33,Kostenstelle_alt
COLUMN,34,Datum
COLUMN,35,Betrieb
COLUMN,36,KST_1
COLUMN,37,KST_2
COLUMN,38,Acct Nr
COLUMN,39,Bookkeep Date
COLUMN,40,Marke
COLUMN,41,Kostenstelle