请教各位高手如何在SQL中给NULL赋值

[gw_sh]

请见下面两个表格以及SELECT语句, 要从两个表中导出每一个AGENT的COMMISSION VALUE(2%), 由于其中有一个AGENT没有任何CONSIGNMENT要求返回0值, 请问如何解决.万分感谢!!!


AGENT_ID AGENT_NAME
--------- --------------------
1 Bob Smith
2 Jane Doe
3 Sally Atkinson
4 Dave Kennedy

CONSIGNMENT_NUMBER CONTAINER_ID AGENT_ID VESSEL_NAME VALUE
------------------ ------------ --------- -------------------- ---------
1 1 1 SpeedDelivery 50000
2 1 1 SpeedDelivery 500
3 1 1 SlowDelivery 50000
4 2 1 SpeedDelivery 150000
5 2 2 SpeedDelivery 1500
6 2 2 SlowDelivery 150000
7 1 1 AnyDelivery 50000
8 1 1 SpeedDelivery 500
9 1 1 SlowDelivery 50000
10 3 3 SpeedDelivery 50000
11 4 3 SpeedDelivery 500
12 5 3 SlowDelivery 50000

12 rows selected.

SQL> SELECT agent.agent_id, agent.agent_name, SUM(consignment.value)*0.02 "Commission (2%)"
2 FROM agent, consignment
3 WHERE agent.agent_id = consignment.agent_id (+)
4 GROUP BY agent.agent_id, agent.agent_name;

AGENT_ID AGENT_NAME Commission (2%)
--------- -------------------- ---------------
1 Bob Smith 7020
2 Jane Doe 3030
3 Sally Atkinson 2010
4 Dave Kennedy

[xbin999]

use NVL()
SELECT agent.agent_id, agent.agent_name, SUM(nvl(consignment.value, 0))*0.02 "Commission (2%)"
FROM agent, consignment
WHERE agent.agent_id = consignment.agent_id (+)
GROUP BY agent.agent_id, agent.agent_name;

[yuxuan]

1.use NVL()
SELECT agent.agent_id, agent.agent_name, SUM(nvl(consignment.value, 0))*0.02 "Commission (2%)"
FROM agent, consignment
WHERE agent.agent_id = consignment.agent_id (+)
GROUP BY agent.agent_id, agent.agent_name;

2.use decode
use NVL()
SELECT agent.agent_id, agent.agent_name, SUM(decode(consignment.value, null,0,consignment.value))*0.02 "Commission (2%)"
FROM agent, consignment
WHERE agent.agent_id = consignment.agent_id (+)
GROUP BY agent.agent_id, agent.agent_name;

[huangcaibing]

nvl(ddd,0)
或decode(ddd,null,0,ddd)

[tgc99]

nvl或decode

[gw_sh]

又有个问题:
SQL> SELECT agent.agent_id, agent.agent_name, TO_CHAR(SUM(decode(consignment.value, null,0,consignme
nt.value))*0.02, '$999,999,999.99') "Commission (2%)"
2 FROM agent, consignment
3 WHERE agent.agent_id = consignment.agent_id (+)
4 GROUP BY agent.agent_id, agent.agent_name;

AGENT_ID AGENT_NAME Commission (2%)
--------- -------------------- ----------------
1 Bob Smith $7,020.00
2 Jane Doe $3,030.00
3 Sally Atkinson $2,010.00
4 Dave Kennedy $.00

我如何可以把最后一行格式化成$0.00?

[xbin999]

'$999,999,990.99'

[gw_sh]

多谢各位

[armu80830]

nvl函数
判断的话用 is (not) null