1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
CREATE VIEW thetvdbview as
select
ev.eventid,
case when ep.compname = 'TATORT' then concat('Tatort',replace(substring(ep.extracol1,11),' und ',' & ')) else ev.title end title,
ev.scrsp,
case when ep.compname = 'TATORT' then Null else ep.season end season,
case when ep.compname = 'TATORT' then Null else ep.part end part,
case when ep.compname = 'TATORT' then Null else ep.number end number,
ep.partname shorttext
from
events ev, episodes ep
where
ev.episodecompname = ep.compname and
ev.episodecomppartname = ep.comppartname and
ev.episodelang = ep.lang and
ev.scrsp is null and
updflg in('A','T','C','P')
union
select
eventid,
title,
scrsp,
case when substring(shorttext,1,1)='S' and substring(shorttext,2,1) REGEXP ('[0-9]') then TRIM(LEADING '0' from replace(SUBSTRING_INDEX(shorttext, 'E', 1),'S','')) end season,
case when substring(shorttext,1,1)='S' and substring(shorttext,2,1) REGEXP ('[0-9]') then TRIM(LEADING '0' from SUBSTRING_INDEX(shorttext, 'E', -1)) end part,
null number,
case when substring(shorttext,1,1)='S' and substring(shorttext,2,1) REGEXP ('[0-9]') then Null else shorttext end shorttext
from
events
where
episodecompname is Null
and scrsp is null
and updflg in('A','T','C','P')
and category = 'Serie';
|
