How to Calculate column in Storeprocedure?

Question

0.00/5 (No votes)

See more:

i have store procedure following,

SQL

 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 have 5000
p.reciveamt have NULL
p.dueamt have NULL
i have datalist, whic have

C#

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

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

Posted 7-Mar-15 1:24am

Karmesh_Madhavi

Updated 7-Mar-15 1:47am

King Fisher

v2

Add a Solution

Comments

King Fisher 7-Mar-15 7:25am

Whats your Question?

Karmesh_Madhavi 7-Mar-15 7:40am

i want to calculate like( p.dueamt=c.total-p.reciveamt)
i try C.total-p.reciveamt as dueamt

but it get NULL. i want 0

King Fisher 7-Mar-15 7:49am

can you show your Table Structure with Some Dummy Data ? So that we can help you

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2015-03-07T03:23:00

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:

SQL

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