create or replace view view_lot as
SELECT
    mu.hinmokucd
    , mu.hinmokumei
    , mu.lotno
     , mu.seizobi
     , mu.shohikigen
     , mu.ukeharaino
     , mu.meisaicd as ukeharaimeisaicd
    , du.shiireno 
FROM
    dat_d_ukeharai du 
    left join dat_m_ukeharai mu 
        on mu.ukeharaino = du.ukeharaino 
WHERE
    mu.meisaikb = '1' 
    and mu.lotno <> ' ' 
    and mu.nyukosokocd <> ' ' 
;