select
a.customer_id,
a.customer_name,
a.contact_man,
a.contact_phone,
a.contact_addr,
a.customer_level,
b.cust_type_name,
c.column_desc,
d.credit_name
from
ucs_customer a,
sys_cust_type b,
sys_tbl_col_value c,
ucs_credit d
where
a.customer_id =:f1
and a.customer_type = b.cust_type
and d.credit_value
in (select
f.credit_value
from
ucs_account f
where f.account_id = :f2)
and c.table_name = 'all'
and c.column_value = a.customer_level
and c.column_name = 'customer_level'按照正常的情况,正确的SQL是这样的,可是有时候在credit_value表中查不到对应的credit_value,这个时候就查不到数据记录有什么办法在当credit_value在ucs_credit查不到时,给credit_name一个默认的值,使能够查出数据记录呢?
a.customer_id,
a.customer_name,
a.contact_man,
a.contact_phone,
a.contact_addr,
a.customer_level,
b.cust_type_name,
c.column_desc,
d.credit_name
from
ucs_customer a,
sys_cust_type b,
sys_tbl_col_value c,
ucs_credit d
where
a.customer_id =:f1
and a.customer_type = b.cust_type
and d.credit_value
in (select
f.credit_value
from
ucs_account f
where f.account_id = :f2)
and c.table_name = 'all'
and c.column_value = a.customer_level
and c.column_name = 'customer_level'按照正常的情况,正确的SQL是这样的,可是有时候在credit_value表中查不到对应的credit_value,这个时候就查不到数据记录有什么办法在当credit_value在ucs_credit查不到时,给credit_name一个默认的值,使能够查出数据记录呢?
2. 给credit_name一个默认的值 ==> nvl(credit_name,default_value)
select nvl(credit_name,-1)credit_name from ucs_credit where credit_value = 1234;
查不出记录啊
NAME KM CJ
1 AA SX 10
2 AA YW 20
3 AA WY 30
4 BB SX 30
5 BB YW 20
6 BB 10
select nvl(km,'-1') km from test where name='BB' ;
KM
1 SX
2 YW
3 -1
且需要注意,nvl(字段,Value)时两个参数的类型必须是一致的!