How to add default constatint in query dynamically in sql

Question

I am writing one query in that I am doing sum of the two column. some time it is happening that "amount" column is null so It returns null to me.

But I want if my amount column is null then I want it to be zero.

Note :-- I cannot add default constraint to the table in which amount column is present Bcoz first time when I calculate the sum data is not present in that table.

so I want to add default constraint to that column dynamically

How to achieve that.

Here's My Query.

select 
sum((ism.selling_price * som.quantity) + (((tm.tax_amount*(ism.selling_price * som.quantity))/100))) as Amnt,
(sum((ism.selling_price * som.quantity) + (((tm.tax_amount*(ism.selling_price * som.quantity))/100))) - sum(amount)) as total,
sm.sell_order_no, 
r.retailer_id,
r.business_name 
from RS_Sell_Order_Master as SM 
left outer join RS_Sell_Order_Mapping as SOM on SOM.sell_order_no = sm.sell_order_no 
left outer join RS_Receipt_Master as RM on rm.sales_order = sm.sell_order_no 
left outer join RS_Relationship_Master as R on r.retailer_id = sm.retailer_id
left outer join RS_Inventory_Master as Im on im.product_id = som.product_id
left outer join RS_Inventory_Selling_Master as Ism on ism.product_code = im.product_code
left outer join RS_Sells_Invoice_Info_Master as SIIM on siim.selling_product_id = ism.selling_product_id
left outer join RS_Tax_Master as tm on tm.tax_id = siim.tax_id
where r.retailer_id = 24
group by sm.sell_order_no,r.retailer_id,r.business_name
having (sum((ism.selling_price * som.quantity) + (((tm.tax_amount*(ism.selling_price * som.quantity))/100))) - sum(amount)) > 0

I have reverted your edit; please do not change the entire nature of a question, as that renders any answers and comments incorrect. If you have a second question: ask a second question — Marc Gravell
– Marc Gravell, Commented May 14, 2014 at 8:10
but can you please tell me how to use sub query for this please — Hardik Parmar
– Hardik Parmar, Commented May 14, 2014 at 8:12
That is not a clear question; to use a sub-query, you simply write a sub-query — Marc Gravell
– Marc Gravell, Commented May 14, 2014 at 8:24

Marc Gravell · Accepted Answer · 2014-05-14 06:53:46Z

1

isnull, i.e.:

...sum(isnull(amount,0))...

other useful related functions here are nullif and coalesce

answered May 14, 2014 at 6:53

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Didier Caron Over a year ago

yes, you can read about it at in this post on MSDN: msdn.microsoft.com/en-us/library/ms190349.aspx

Hardik Parmar Over a year ago

thnk you very much for your valuable time Mr. Mark. I appricaite your efforts. It works

Hardik Parmar Over a year ago

hey can you help me for the same query but for sub query error

cracker · Accepted Answer · 2014-05-14 10:31:15Z

1

This will give you 0 if is null then.

ISNULL(SUM(COLUMN),0)

answered May 14, 2014 at 10:31

cracker

4,9043 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

How to add default constatint in query dynamically in sql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related