DROP VIEW LD0V0A700_HUSHALL;

CREATE VIEW LD0V0A700_HUSHALL AS
SELECT 
ISNULL(K01.HUSHLNR,0) AS HUSHLNR,
ISNULL(A700.FLYKTNR,0) AS FLYKTNR, 
ISNULL(SUB.PNR,' ') AS PNR,
ISNULL(K01.PNR,' ') PNR_I_HUSHALL, 
ISNULL(A700.ANKKOMU,' ') AS ANKKOMU, 
ISNULL(A700.DOSSIERNR,' ') AS DOSSIERNR, 
ISNULL(A700.ANKFORL,' ') AS ANKFORL, 
ISNULL(A700.ANSUAT,' ') AS ANSUAT, 
ISNULL(A700.ANKSV,' ') AS ANKSV, 
ISNULL(A700.UATDAT,' ') AS UATDAT, 
ISNULL(A700.MOTDAT, ' ') AS MOTDAT,
ISNULL(A700.MOTKOMU,0) AS MOTKOMU, 
ISNULL(A700.INFDAT1,' ') AS INFDAT1, 
ISNULL(A700.UTFDAT1,' ') AS UTFDAT1, 
ISNULL(A700.UTFKOMU1,0) AS UTFKOMU1, 
ISNULL(A700.INFDAT2,' ') AS INFDAT2, 
ISNULL(A700.UTFDAT2,' ') AS UTFDAT2, 
ISNULL(A700.UTFKOMU2,0) AS UTFKOMU2, 
ISNULL(A700.AVAKTDAT,' ') AS AVAKTDAT, 
ISNULL(A700.SFIAVSLUT,' ') AS SFIAVSLUT, 
ISNULL(A700.ENDSTATSBIDR,0) AS ENDSTATSBIDR,
ISNULL(A700.AVAKTORSAK,0) AS AVAKTORSAK, 
ISNULL(A700.KLASSNING,0) AS KLASSNING, 
ISNULL(A700.GRUND,0) AS GRUND, 
ISNULL(A700.KATEGORI,0) AS KATEGORI, 
ISNULL(A700.FORSTABESOK,' ') AS FORSTABESOK, 
ISNULL(A700.ORGID,0) AS ORGID, 
ISNULL(A700.ORGENHNR,' ') AS ORGENHNR, 
ISNULL(A700.UPDDAT,' ') AS UPDDAT, 
ISNULL(A700.UPDSIGN,' ') AS UPDSIGN
FROM LD0P0A700 A700
LEFT OUTER JOIN LD0P0K01 K01 ON
A700.PNR=K01.PNR
JOIN 
  (SELECT K01.PNR,MAX(K01.HUSHLNR) AS HUSHLNR 
  FROM LD0P0K01 K01
  GROUP BY K01.PNR) SUB
ON SUB.HUSHLNR=K01.HUSHLNR
;
