create view LD0V0ASearchAvregAll as 
SELECT  A.PNR, A.NAMN, C20.AKTPNR as AKTPNR, A.AVREGDAT, a.AVREGORS, q01.KLARTEXT as AVREGORSTXT, 0 as PAGAENDEUTREDNING, 1 as PAGAENDEINSATS,0 as PAGAENDEVERKSTALLIGHET
FROM LD0V0A A
left join LD0V0C21 C21 
ON A.PNR = C21.PNR
JOIN LD0VLC20 C20 
ON (c20.AKTPNR = A.PNR) 
and C20.TOMDAT = '99999999'
left join ld0v0q01 q01 
on q01.KODTYP = 'AVREGORSAK' 
and q01.kod = a.AVREGORS

union all

SELECT  A.PNR, A.NAMN, C20.AKTPNR as AKTPNR, A.AVREGDAT, A.AVREGORS, q01.KLARTEXT as AVREGORSTXT, 0 as PAGAENDEUTREDNING, 1 as PAGAENDEINSATS, 0 as PAGAENDEVERKSTALLIGHET
FROM LD0V0A A
left join LD0V0C21 C21 
ON A.PNR = C21.PNR
JOIN LD0VLC20 C20 
ON (C20.INSNR= C21.INSNR) 
and C20.TOMDAT = '99999999'
left join ld0v0q01 q01 
on q01.KODTYP = 'AVREGORSAK' 
and q01.kod = a.AVREGORS


union all

SELECT  A.PNR, A.NAMN, C.AKTPNR as AKTPNR, A.AVREGDAT, A.AVREGORS, q01.KLARTEXT as AVREGORSTXT, 1-C.TMARK as PAGAENDEUTREDNING,  0 as PAGAENDEINSATS, C.TMARK as PAGAENDEVERKSTALLIGHET
FROM LD0V0A A
left JOIN LD0V0C01 C01 
ON A.PNR = C01.PNR
JOIN LD0VLC C 
on C.AEVERKNR = C01.AEVERKNR
and C.STOPDAT = '99999999' 
left join ld0v0q01 q01 
on q01.KODTYP = 'AVREGORSAK' 
and q01.kod = a.AVREGORS

union all

SELECT  A.PNR, A.NAMN, C.AKTPNR as AKTPNR, A.AVREGDAT, A.AVREGORS, q01.KLARTEXT as AVREGORSTXT, 1-C.TMARK as PAGAENDEUTREDNING,  0 as PAGAENDEINSATS, C.TMARK as PAGAENDEVERKSTALLIGHET
FROM LD0V0A A
left JOIN LD0V0C01 C01 
ON A.PNR = C01.PNR
JOIN LD0VLC C 
on C.AKTPNR = A.PNR
and C.STOPDAT = '99999999' 
left join ld0v0q01 q01 
on q01.KODTYP = 'AVREGORSAK' 
and q01.kod = a.AVREGORS
;