CREATE OR REPLACE VIEW viewprodukte AS 
 SELECT tp.id, 
        CASE
            WHEN tp.service::text <> 'SMS'::text AND tp.service::text <> 'MT_SMS'::text AND tp.service::text <> 'MMS'::text AND tp.service::text <> 'MT_MMS'::text AND tp.service::text <> 'A_SMS'::text AND tp.service::text <> 'A_MT_SMS'::text AND tp.service::text <> 'CH_SMS'::text THEN tp.produktnr::text
            ELSE 
            CASE
                WHEN tp.service::text = 'SMS'::text OR tp.service::text = 'MT_SMS'::text OR tp.service::text = 'MMS'::text OR tp.service::text = 'MT_MMS'::text OR tp.service::text = 'A_SMS'::text OR tp.service::text = 'A_MT_SMS'::text OR tp.service::text = 'CH_SMS'::text THEN "substring"(tp.produktnr::text, 1, "position"(tp.produktnr::text, '_'::text) - 1)
                ELSE split_part(tp.produktnr::text, '_'::text, 2)
            END
        END AS nummer, 
        CASE
            WHEN tp.service::text = 'SMS'::text OR tp.service::text = 'MT_SMS'::text OR tp.service::text = 'MMS'::text OR tp.service::text = 'MT_MMS'::text OR tp.service::text = 'CH_SMS'::text THEN 
            CASE
                WHEN tp.service::text ~~ 'MT%'::text THEN btrim("substring"(split_part(tp.produktnr::text, '_'::text, 3), 1, "position"(split_part(tp.produktnr::text, '_'::text, 3), ' '::text)), ' '::text)
                ELSE btrim("substring"(tp.produktnr::text, "position"(tp.produktnr::text, '_'::text) + 1, "position"(tp.produktnr::text, ' '::text) - ("position"(tp.produktnr::text, '_'::text) + 1)), ' '::text)
            END
            WHEN tp.service::text = 'A_SMS'::text THEN btrim(split_part(tp.produktnr::text, '_'::text, 2), ' '::text)
            WHEN tp.service::text = 'A_MT_SMS'::text THEN btrim(split_part(tp.produktnr::text, '_'::text, 3), ' '::text)
            ELSE ''::text
        END AS beschreibung, 
        CASE
            WHEN tp.service::text = 'SMS'::text OR tp.service::text = 'MT_SMS'::text OR tp.service::text = 'MMS'::text OR tp.service::text = 'MT_MMS'::text OR tp.service::text = 'A_SMS'::text OR tp.service::text = 'A_MT_SMS'::text OR tp.service::text = 'CH_SMS'::text THEN 
            CASE
                WHEN tp.service::text = 'A_SMS'::text OR tp.service::text = 'A_MT_SMS'::text THEN ''::text
                ELSE btrim("substring"(tp.produktnr::text, "position"(tp.produktnr::text, ' '::text), length(tp.produktnr::text)), ' '::text)
            END
            ELSE 'DTAG'::text
        END AS provider, 
        CASE
            WHEN tp.service::text ~~ '%MT%'::text THEN 'MT'::text
            ELSE 'MO'::text
        END AS richtung, 
        CASE
            WHEN strpos(tp.service::text, 'MT_'::text) > 0 THEN "replace"(tp.service::text, 'MT_'::text, ''::text)::character varying
            ELSE tp.service
        END AS service, tp.service AS service_echt, tk.kunde, tp.kdnr, tp.einkauf, tp.sharing, tp.produktnr, tp.gruppe, tp.anwendung, tp.kuerzel, tp.rechnung, tp.zahlung, tp.zusatz, tp.verwendung, tp.bemerkung, tp.telekom, tp.kosten, tp.datum, tp.kunde AS reserviert, tp.darstellung, tp.channel, tp.durchwahl, tp.verantwortlicher
   FROM tblprodukte tp
   JOIN tblkunden tk ON tk.kdnr = tp.kdnr
  ORDER BY tp.produktnr;

ALTER TABLE viewprodukte OWNER TO postgres;
