RE: SQL pat my own back

OK, I decided on outer joins with coalesce. Haven't tried it yet (keep
getting interrupted) but how does this look:

Exec SQL Insert into INSTLTRN
(Select :RptDate,
c.DCNBR,
s.CUSNR,
s.INVNRP,
s.INVDT,
c.INVTRNCD,
s.QTYSH,
Case S.UNITS
When 'U' Then Coalesce(i.QSUN2, 'UN')
Else Coalesce(i.QSUN1, 'CS')
End As QTYUOM,
0,
s.ITNSA/s.QTYSH,
s.ITNSA,
s.ITNBR,
Coalesce(i.ITDSC, 'Discontinued Item'),
Coalesce(i.PACKX, 1),
Coalesce(i.SIZEX, ' '),
Coalesce(i.BRAND, 'Unknown'),
Coalesce(v.VNDNM, 'Discontinued Vendor'),
Coalesce(i.VNITM, 'Unknown'),
Cast(Coalesce(i.ADUNQ, 0) As Char(1)) ConCat
Cast(Coalesce(i.VUPCD, 00000) As Char(5)) ConCat
Cast(Coalesce(i.IUPCD, 00000) As Char(5)),
0,
c.PRGCODE
From SAHISTW S
Join INSTLCTL c
On '1' = c.KEYID
Left Outer Join DMITMMST I
On s.ITNBR = i.ITNBR
Left Outer Join DMVDRMST V
On s.VNDNR = v.VNDNR
Where s.VNDNR = c.vndnr and
s.INVDT >= :BgnSlsDate and
s.INVDT <= :EndSlsDate and
s.QTYSH > 0);

Note that it's still an inner join to INSTLCTL as that control file just
absolutely, positively has to be there. And am I correct that if I want an
outer join I cannot do the join in the WHERE clause, correct?