CREATE VIEW eventsview as select sub.masterid as useid, cnt.eventid, cnt.channelid, cnt.source, GREATEST(cnt.updsp,sub.updsp,IFNULL(epi.updsp,0)) updsp, cnt.updflg, cnt.delflg, cnt.fileref, cnt.tableid, cnt.version, sub.title,
case
 when sub.shorttext is null then
  case when sub.genre is null then '' else concat(sub.genre,' (', sub.country, ' ',sub.year, ')') end
 else sub.shorttext
end shorttext,
cnt.starttime, cnt.duration, cnt.parentalrating, cnt.vps, replace(
concat(
  TRIM(LEADING '|' FROM
   concat(
    case when sub.genre is Null then '' else concat('|','Genre: ',sub.genre) end,
    case when sub.category is Null then '' else concat('|','Kategorie: ',sub.category) end,
    case when sub.country is Null then '' else concat('|','Land: ',sub.country) end,
    case when sub.year is Null then '' else concat(' ','   ',substring(sub.year,1,4)) end,
    case when sub.flags is Null then '' else concat('|',sub.flags) end,
    case when sub.flags is Null or sub.audio is Null then '' else ' ' end,
    case when sub.audio is Null then '' else concat('|','[', replace(sub.audio, ' ', '] ['), ']') end,
    case when sub.flags is Null and sub.audio is Null then '|' else '' end,
    case when sub.flags is Null or sub.audio is Null then '' else ' ' end,
    case when sub.fsk is Null then '' else concat('[FSK ',sub.fsk,']') end,
    case when epi.season is Null then '' else concat('|','Staffel: ',cast(epi.season as char)) end,
    case when epi.part is Null then '' else concat(' ',' Folge: ',cast(epi.part as char)) end
   )
  ),
   case when sub.tipp = '[TagesTipp]' then '||Tagestipp' else '' end,
   concat(
    case when sub.rating is null and sub.shortreview is null then '' else '||' end,
    case when sub.source = 'epgdata' then
       case when sub.rating like '%Allgemein *****%' then concat('|','Bewertung: Sehr empfehlenswert')
            when sub.rating like '%Allgemein ****%' then concat('|','Bewertung: Empfehlenswert')
            when sub.rating like '%Allgemein ***%' then concat('|','Bewertung: Eher durchschnittlich')
            when sub.rating like '%Allgemein **%' then concat('|','Bewertung: Eher nicht empfehlenswert')
            when sub.rating like '%Allgemein *%' then concat('|','Bewertung: Eher uninteressant')
       else ''
       end
    else
       case when sub.rating like '%Allgemein ****%' then concat('|','Bewertung: Einer der besten Filme aller Zeiten')
            when sub.rating like '%Allgemein ***%' then concat('|','Bewertung: Empfehlenswert')
            when sub.rating like '%Allgemein **%' then concat('|','Bewertung: Eher durchschnittlich')
            when sub.rating like '%Allgemein *%' then concat('|','Bewertung: Eher uninteressant')
       else ''
       end
    end,
    case when sub.rating is null or sub.shortreview is null then '' else ', ' end,
    case when sub.shortreview is Null then '' else sub.shortreview end,
    case when sub.topic is Null then '' else concat('||','Thema: ',sub.topic) end,
    case when sub.longdescription is Null then '' else concat('||',sub.longdescription) end,
    case when sub.info is Null then '' else concat('||','Info: ',sub.info) end,
    case when sub.moderator is Null then '' else concat('||','Moderator: ',sub.moderator) end,
    case when sub.producer is Null then '' else concat('|','Produzent: ',sub.producer) end,
    case when sub.guest is Null then '' else concat('|','Gäste: ',sub.guest) end,
    case when sub.other is Null then '' else concat('|','Sonstige: ',sub.other) end,
    case when sub.producer is Null and sub.fsk is Null then '' else '|' end,
    case when sub.actor is Null then '' else concat('|',replace(sub.actor,'), ', ')|')) end,
    case when sub.director is Null and sub.screenplay is Null and sub.camera is Null and sub.music is Null then '' else '|' end,
    case when sub.director is Null then '' else concat('|','Regie: ',sub.director) end,
    case when sub.screenplay is Null then '' else concat('|','Drehbuch: ',sub.screenplay) end,
    case when sub.camera is Null then '' else concat('|','Kamera: ',sub.camera) end,
    case when sub.music is Null then '' else concat('|','Musik: ',sub.music) end,
    case when sub.rating is Null then '' else
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    concat('|',replace(replace(sub.rating,' / ', '|'),' ',': '))
    ,'|Allgemein: *****','')
    ,'|Allgemein: ****','')
    ,'|Allgemein: ***','')
    ,'|Allgemein: **','')
    ,'|Allgemein: *','')
    ,'Thrill','Spannung')
    ,'Humor','Spaß')
    end,
    case when epi.episodename is Null then '' else concat('||','Serie: ',epi.episodename) end,
    case when epi.shortname is Null then '' else concat('|','Kurzname: ',epi.shortname) end,
    case when epi.partname is Null then '' else concat('|','Episode: ',epi.partname) end,
    case when epi.extracol1 is Null then '' else concat('|',epi.extracol1) end,
    case when epi.extracol2 is Null then '' else concat('|',epi.extracol2) end,
    case when epi.extracol3 is Null then '' else concat('|',epi.extracol3) end,
    case when cnt.source <> sub.source then concat('||','Quelle: ',upper(replace(cnt.source,'vdr','dvb')),'/',upper(sub.source)) else concat('||','Quelle: ',upper(replace(cnt.source,'vdr','dvb'))) end
  )
)
,'|', '
') as description
from
 events cnt
 inner join events sub on (case when cnt.useid = 0 then cnt.masterid else cnt.useid end = sub.masterid)
 left outer join episodes epi on (sub.episode = epi.compname and sub.episodepart = epi.comppartname and sub.episodelang = epi.lang);