这个SELECT如何设置默认值，比如某项为空时让其显示为0；default 0可以吗？

SELECT INVH.customer AS customer,
      INVH.cause_code AS cause_code,
      INVH.stock_bill AS bill,
      INVH.trans_date AS clientage_date,
      INVH.bill_type  AS bill_type,
      ( SELECT SUM(amt)
        FROM inv_trans_detail INVD
        WHERE INVD.bill=INVH.stock_bill AND (INVH.bill_type='1111' OR INVH.bill_type='3333')) AS debtor default 0,
      ( SELECT SUM(amt)
        FROM inv_trans_detail INVD
        WHERE INVD.bill=INVH.stock_bill AND (INVH.bill_type='4444' OR INVH.bill_type='3333')) AS lender default 0,
      INVH.checkman AS checked
   FROM inv_trans_detail_head INVH

这个SELECT如何设置默认值，比如某项为空时让其显示为0；default 0可以吗？
如果lender为空则输出为0，上面的写法不行
我实在一个VIEW中用的上面的语句；
各位给参考一下，谢谢

6 个解决方案

#1

是SQL SERVER吗？
case lender when null then 0 else lender end
就可以了。
你想想你的语句该怎么改吧：）

#2

大家快来帮忙啊送高分！！呵呵

#3

我猜你所說的"某項為空"指的是為null吧??如果是，則可以用ISNULL來搞掟,否則請用CASE

SELECT INVH.customer AS customer,
    INVH.cause_code AS cause_code,
    INVH.stock_bill AS bill,
    INVH.trans_date AS clientage_date,
    INVH.bill_type  AS bill_type,
    ( SELECT ISNULL(SUM(amt),0)
      FROM inv_trans_detail INVD
      WHERE INVD.bill=INVH.stock_bill AND (INVH.bill_type='1111' OR INVH.bill_type='3333')) AS debtor ,
    ( SELECT ISNULL(SUM(amt),0)
      FROM inv_trans_detail INVD
      WHERE INVD.bill=INVH.stock_bill AND (INVH.bill_type='4444' OR INVH.bill_type='3333')) AS lender ,
    INVH.checkman AS checked
  FROM inv_trans_detail_head INVH

#4

哦忘了说了是Oracle阿
妹妹好多谢你啊不过我的这个语句怎么改呢？

#5

IN ORACLE
YOU MAY USE THIS STATEMENT
SELECT NVL(LENDER, 0) FROM DUAL;

#6

oK!!!搞定！！
多谢各位给分！！呵呵

#1