学步园

NVL(expr1,expr2)

NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error. The implicit conversion is implemented as follows:

• If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

• If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

表一:
订单号         产品编号       物料编号       库存数
0001               1254               11212               0
0001               1254               11213               0
0001               1254               11257               0

表二:
订单号         产品编号       物料编号       库存数
0001               1254               11212               9
0001               1254               11213               50

我想要的结果是:
用一条查询语句得到如下结果

订单号         产品编号       物料编号       库存数
0001               1254               11212               9
0001               1254               11213               50
0001               1254               11257               0

Select   a.订单号,a.产品编号,a.物料编号,
              nvl(b.库存数,0)   as   库存数
  from   表一   a   Left   Join   表二   b   on
            a.订单号=b.订单号   and   a.产品编号=b.产品编号    
          and   a.物料编号=b.物料编号