查询结果为空默认赋值

引自http://www.itpub.net/thread-1596303-1-1.html

由于两张表进行并联查询，如果一张表为空，查询出的结果为空。但是有特殊情况，比如需要为空的也有值，就需要用MAX，比如

select case
         when instr(b.type, a.c_lemma_item) > 0 then
          1
         else
          0
       end hasright,
       a.c_lemma_item,
       a.vc_item_name
  from (select c_lemma_item, vc_item_name
          from TDICTIONARY
         where l_dictionary_no = 60628
           and c_lemma_item <> '!') a,
       (select NVL(MAX(VC_INVESTCONTRACT_TYPE), '!') type
          from TOPINVESTCONTRACTTYPE
         where l_role_id = -1
           and l_operator_no = 302) b
 order by a.c_lemma_item