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 or sub.title=sub.shorttext or (substring(sub.shorttext,1,1)='S' and substring(sub.shorttext,2,1) REGEXP ('[0-9]') ) then
  case when sub.genre is null then '' else concat(sub.genre,' (', sub.country, ' ',sub.year, ')') end
  else
  case when epi.season is null then
    sub.shorttext
  else
    concat('(S', lpad(format(epi.season, 0), 2, '0'), 'E', lpad(format(epi.part, 0), 2, '0'), ') ', epi.partname)
  end
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('|','Jahr: ',substring(sub.year,1,4)) end
   )
  ),
  concat(
    case when sub.shortdescription is Null then '' else concat('||',sub.shortdescription) end,
    case when sub.shortreview is Null then '' else concat('||','Kurzkritik: ',sub.shortreview) end,
    case when sub.tipp is Null and sub.rating is Null then '' else '||' end,
    case when sub.tipp is Null then '' else concat('|',sub.tipp) end,
    case when sub.rating is Null then '' else concat('|',sub.rating) end,
    case when sub.topic is Null then '' else concat('||','Thema: ',sub.topic) end,
    case when (INSTR(lower(sub.genre), 'nachrichten') > 0 or INSTR(lower(sub.genre), 'magazin') > 0 or INSTR(lower(sub.genre), 'diskussion') > 0 or INSTR(lower(sub.genre), 'gespräch') > 0 or INSTR(lower(sub.genre), 'show') > 0) or sub.genre in ('Ansprache','Aktuelle Berichte','Eishockey','Formel 1','Fußball','Fussball','Fun- u. Extremsport','Golf','Leichtathletik','Motor + Verkehr','Motor+Verkehr','Motorrad','Motorsport','NASCAR','Radsport','Reitsport','Sammelsendung','Tennis','Tischtennis','Wintersport') and cnt.source <> sub.source
	then concat('||', upper(cnt.source),' Event:||',cnt.longdescription) 
		else case when sub.longdescription is Null then '' else concat('||',sub.longdescription) end 
	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.guest is Null then '' else concat('|','Gäste: ',sub.guest) end,
    case when cnt.parentalrating is Null or cnt.parentalrating = 0 then '' else concat('||','Altersempfehlung: ab ',cnt.parentalrating) end,
    case when sub.actor is Null and sub.producer is Null and sub.other is Null then '' else '|' end,
    case when sub.actor is Null then '' else concat('|','Darsteller: ',sub.actor) end,
    case when sub.producer is Null then '' else concat('|','Produzent: ',sub.producer) end,
    case when sub.other is Null then '' else concat('|','Sonstige: ',sub.other) end,
    case when sub.director is Null and sub.screenplay is Null  and sub.camera is Null and sub.music is Null and sub.audio is Null and sub.flags 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.audio is Null then '' else concat('|','Audio: ',sub.audio) end,
    case when sub.flags is Null then '' else concat('|','Flags: ',sub.flags) 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 epi.season is Null then '' else concat('|','Staffel: ',cast(epi.season as char)) end,
    case when epi.part is Null then '' else concat('|','Staffelfolge: ',cast(epi.part as char)) end,
    case when epi.parts is Null then '' else concat('|','Staffelfolgen: ',cast(epi.parts as char)) end,
    case when epi.number is Null then '' else concat('|','Folge: ',cast(epi.number as char)) 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);