Code: Select all
CREATE OR REPLACE FORCE VIEW AM_SPORLEVII.V_SPORLEV_SAMLET
(
ID,
STRKAFS,
NAVN,
HOVEDSTRAEKNING,
STRAEKNING,
AFSNIT,
KPMSPORTYPENAVN,
BANENR,
KPMSTRK,
KPMSTRK_NAVN,
SPORNUMMER,
FRA_KM,
TIL_KM,
SPOR_FRA_KM,
SPOR_TIL_KM,
LAENGDE,
SKN_SKNSTRENGID,
SKN_SKINNESTRENG,
SKN_SKNTYPEID,
SKN_SKINNETYPE,
SKN_SKNIBRUGTILSTID,
SKN_IBRUGTILSTAND,
SKN_SKNSTAALKVALID,
SKN_STAALKVALITET,
SKN_FABRIKATID,
SKN_FABRIKAT,
SKN_FAB_AAR,
SKN_IBRUGDATO,
SKN_BEMAERKNING,
SKN_REDIGER_BRUGER,
SKN_REDIGER_DATO,
SKN_SLETTEMARKERING,
SVL_OVBTYPEID,
SVL_OVBTYPE,
SVL_BEF_FAB_AAR,
SVL_SVELLER_FABRIKATID,
SVL_SVELLEFABRIKAT,
SVL_FAB_AAR,
SVL_IBRUGDATO,
SVL_BEMAERKNING,
SVL_SLETTEMARKERING,
SVL_REDIGER_BRUGER,
SVL_REDIGER_DATO,
BLST_BLSTTYPEID,
BLST_BLSTTYPE,
BLST_IBRUGDATO,
BLST_BEMAERKNING,
BLST_SLETTEMARKERING,
BLST_REDIGER_BRUGER,
BLST_REDIGER_DATO,
UBLST_UBLSTTYPEID,
UBLST_UBLSTTYPE,
UBLST_IBRUGDATO,
UBLST_BEMAERKNING,
UBLST_SLETTEMARKERING,
UBLST_REDIGER_BRUGER,
UBLST_REDIGER_DATO,
VALID,
STATUS,
REDIGER_BRUGER,
REDIGER_DATO
)
AS
SELECT ss.ID,
ss.STRKAFS,
afs.navn,
TRIM (TO_CHAR (afs.hvdstrk, '000')) hovedstraekning,
TRIM (TO_CHAR (afs.delstrk, '000')) straekning,
TRIM (TO_CHAR (afs.afsnit, '000')) afsnit,
(SELECT MIN (st.NAVN)
FROM am_spor.v_spor_data spor, am_spor_enorm.sportype st
WHERE spor.sportypeid = st.id(+)
AND ( ss.STRKAFS = spor.STRKAFS
AND UPPER (ss.spornummer) = UPPER (spor.spornummer)
AND spor.fra_km <= ss.fra_km
AND spor.til_km >= ss.fra_km))
KPMSPORTYPENAVN,
afs.banenr,
ss.kpmstrk,
(SELECT kp.kpm || ' (' || kp.kpm_forkortelse || ')'
FROM am_strk.strkgis_kpm kp
WHERE kp.kpm = ss.kpmstrk)
kpmstrk_navn,
ss.SPORNUMMER,
ss.FRA_KM,
ss.TIL_KM,
ss.SPOR_FRA_KM,
ss.SPOR_TIL_KM,
ABS (ss.til_km - ss.fra_km) * 1000 laengde,
sk.sknstrengid SKN_SKNSTRENGID,
(SELECT l.sknstreng
FROM am_sporlevii.VLST_sknstreng l
WHERE sk.sknstrengid = l.ID)
AS skn_skinnestreng,
sk.skntypeid SKN_SKNTYPEID,
(SELECT l.skntype
FROM am_sporlevii.vLST_SKNTYPE l
WHERE sk.skntypeid = l.ID)
AS skn_skinnetype,
sk.sknibrugtilstid SKN_SKNIBRUGTILSTID,
(SELECT i.sknibrugtilst
FROM am_sporlevii.vLST_SKNIBRUGTILST i
WHERE sk.sknibrugtilstid = i.ID)
AS skn_ibrugtilstand,
sk.sknstaalkvalid SKN_SKNSTAALKVALID,
(SELECT K.sknstaalkval
FROM am_sporlevii.vLST_SKNSTAALKVAL K
WHERE sk.sknstaalkvalid = K.ID)
AS skn_staalkvalitet,
sk.fabrikatid SKN_FABRIKATID,
(SELECT f.fabrikat
FROM am_sporlevii.vLST_FABRIKAT f
WHERE sk.fabrikatid = f.ID)
AS skn_fabrikat,
sk.fab_aar SKN_FAB_AAR,
sk.ibrugdato SKN_IBRUGDATO,
sk.bemaerkning SKN_BEMAERKNING,
sk.rediger_bruger SKN_REDIGER_BRUGER,
sk.rediger_dato SKN_REDIGER_DATO,
TO_NUMBER (NULL) SKN_SLETTEMARKERING,
sv.ovbtypeid SVL_OVBTYPEID,
(SELECT l.ovbtype
FROM am_sporlevii.vLST_ovbtype l
WHERE sv.ovbtypeid = l.ID)
AS svl_ovbtype,
sv.bef_fab_aar SVL_BEF_FAB_AAR,
sv.sveller_fabrikatid SVL_SVELLER_FABRIKATID,
(SELECT f.sveller_fabrikat
FROM am_sporlevii.vLST_sveller_fabrikat f
WHERE sv.sveller_fabrikatid = f.ID)
AS svl_svellefabrikat,
sv.fab_aar SVL_FAB_AAR,
sv.ibrugdato SVL_IBRUGDATO,
sv.bemaerkning SVL_BEMAERKNING,
TO_NUMBER (NULL) SVL_SLETTEMARKERING,
sv.rediger_bruger SVL_REDIGER_BRUGER,
sv.rediger_dato SVL_REDIGER_DATO,
bl.blsttypeid BLST_BLSTTYPEID,
(SELECT l.blsttype
FROM am_sporlevii.vLST_blsttype l
WHERE bl.blsttypeid = l.ID)
AS blst_blsttype,
bl.ibrugdato BLST_IBRUGDATO,
bl.bemaerkning BLST_BEMAERKNING,
TO_NUMBER (NULL) BLST_SLETTEMARKERING,
bl.rediger_bruger BLST_REDIGER_BRUGER,
bl.rediger_dato BLST_REDIGER_DATO,
ubl.ublsttypeid UBLST_UBLSTTYPEID,
(SELECT l.ublsttype
FROM am_sporlevii.vLST_ublsttype l
WHERE ubl.ublsttypeid = l.ID)
AS ublst_ublsttype,
ubl.ibrugdato UBLST_IBRUGDATO,
ubl.bemaerkning UBLST_BEMAERKNING,
TO_NUMBER (NULL) UBLST_SLETTEMARKERING,
ubl.rediger_bruger UBLST_REDIGER_BRUGER,
ubl.rediger_dato UBLST_REDIGER_DATO,
CASE
WHEN ( sk.VALID = 0
OR sv.VALID = 0
OR bl.valid = 0
OR ubl.valid = 0)
THEN
0
ELSE
-1
END
VALID,
CASE
WHEN ss.skinner_id IS NULL
AND ss.sveller_id IS NULL
AND ss.ballast_id IS NULL
AND ss.uballast_id IS NULL
THEN
'Ingen data'
WHEN ss.skinner_id IS NOT NULL AND sk.VALID = 0
THEN
'Skinner: ' || sk.status
WHEN ss.sveller_id IS NOT NULL AND sv.VALID = 0
THEN
'Sveller: ' || sv.status
WHEN ss.ballast_id IS NOT NULL AND bl.VALID = 0
THEN
'Ballast: ' || bl.status
WHEN ss.uballast_id IS NOT NULL AND ubl.VALID = 0
THEN
'Underballast: ' || ubl.status
END
STATUS,
ss.REDIGER_BRUGER,
ss.REDIGER_DATO
FROM am_strk.btrafsnit afs,
am_sporlevII.sporlev_samlet ss,
am_sporlevII.skinner sk,
am_sporlevII.sveller sv,
am_sporlevII.ballast bl,
am_sporlevII.uballast ubl
WHERE ss.strkafs = afs.strkafs(+)
AND (ss.skinner_id = sk.id(+))
AND (ss.sveller_id = sv.id(+))
AND (ss.ballast_id = bl.id(+))
AND (ss.uballast_id = ubl.id(+));