如何计算Storeprocedure中的列？ [英] How to Calculate column in Storeprocedure?

问题描述

我有以下商店程序，

i have store procedure following,

 SELECT DISTINCT 
 C.cid
, C.custclientid
, C.client
, C.company
, C.product
, C.producttakendate
, C.total 
, P.reciveamt
, P.dueamt
, ( SELECT TOP 1 rcvdate FROM paymentdata AS PD1 WHERE PD1.pclientid=PD.pclientid AND  PD1.productname=PD.productname ORDER BY rcvdate DESC) AS rcvdate
, ( SELECT TOP 1 nxtdate FROM paymentdata AS PD1 WHERE PD1.pclientid=PD.pclientid AND  PD1.productname=PD.productname ORDER BY rcvdate DESC) AS nxtdate
, ( SELECT TOP 1 notepayment FROM paymentdata AS PD1 WHERE PD1.pclientid=PD.pclientid AND   PD1.productname=PD.productname ORDER BY rcvdate DESC) AS notepayment
FROM
    customermaintenance C 
        LEFT OUTER JOIN payment P 
            ON C.custclientid = P.pclientid
        LEFT OUTER JOIN paymentdata PD
            ON P.pclientid = PD.pclientid

where C.custclientid=1 and C.product='Software'

c.total有5000

p.reciveamt有NULL

p.dueamt有NULL

i有datalist，有什么需要

c.total have 5000
p.reciveamt have NULL
p.dueamt have NULL
i have datalist, whic have

<asp:Label ID="nextamountLabel" runat="server" Text='<%# Eval("dueamt") %>' />

i想要输出dueamt = 5000然后我在这个程序中如何计算？

i want to out put dueamt=5000 then how i calculate in this procedure?

推荐答案

该标准规定任何涉及NULL的操作都将产生NULL。由于NULL不为零 - 它是不可用数据。因此，如果您故意在特殊情况下将NULL视为零，则必须手动执行。最简单的方法是使用 COALESCE（表达式，0）。



在你的情况下它会像这样一样：

The standard states that any operation involving NULL will yield NULL. As NULL is not zero - it is unavailable data. So if you deliberatelly want to treat NULL as zero in a special case, you have to do it manually. The simplest approach is using COALESCE(expression, 0).

In your case it will looke like this one:

COALESCE(c.total,0)-COALESCE(p.reciveamt,0)

这篇关于如何计算Storeprocedure中的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！