null question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

null question

Author

Topic

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-10-21 : 09:11:17

I have a quick question how would i write
Case when cost='NULL' THEN replace NULL with 0 END.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 09:15:01

1 Case when cost is NULL THEN 0 ELSE cost END
2 ISNULL(cost,0)
3 Coalesce(cost,0)

Madhivanan

Failing to plan is Planning to fail

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-10-21 : 09:25:10

ok lets change that a bit here is what i am looking at
How i have it setup is the case you just made for me is as follows, case when cost is NULL THEN 0 Else cost END AS cost, (Next is) CASE WHEN sellprice<>0 THEN(sellprice-cost) End as grosspro
Sell Price -->100
Cost-->0
GrossPro-->NULL

The problem is that with the second statement the gross pro should show 100 dollars not null. see before i was getting cost saying NULL so thats why i wanted to know how to change the NULL to 0 thinking it would then take the sell price - 0 and show me sell price under grosspro.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 09:32:21

CASE WHEN sellprice<>0 THEN(sellprice-Coalesce(cost,0)) End as grosspro

Madhivanan

Failing to plan is Planning to fail

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-10-21 : 09:39:19

that worked now the last part of this question is how would i remove anything that has a grosspro of 0 from my list. I dont want it deleted i just dont want it shown in this report. Case when Grosspro =0 hide?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 09:50:19

Post the full query you used

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 09:54:44

quote:
Originally posted by midpenntech

that worked now the last part of this question is how would i remove anything that has a grosspro of 0 from my list. I dont want it deleted i just dont want it shown in this report. Case when Grosspro =0 hide?

Is grosspro result of aggregated calculation?

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-10-21 : 10:36:54

yes

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-10-21 : 10:37:10

SELECT eqdailyinv.eqpsldinv, eqdailyinv.eqpstatus, eqdailyinv.eqpsldamt AS Sellprice, eqdailyinv.kequipnum, eqdailyinv.kmfg, eqdailyinv.kmodel,
eqdailyinv.eqpsldcust, eqdailyinv.eqpslddate, eqdailyinv.eqpphybr, eqdailyinv.custins, custmast.custname, repmast.custname AS repname,
GPCOSTBYINV.cost, CASE WHEN eqpsldamt <> 0 THEN (eqpsldamt - COALESCE (cost, 0)) END AS Grosspro
FROM eqdailyinv LEFT OUTER JOIN
GPCOSTBYINV ON eqdailyinv.eqpsldcust = GPCOSTBYINV.kcustnum AND eqdailyinv.kequipnum = GPCOSTBYINV.kequipnum AND
eqdailyinv.eqpsldinv = GPCOSTBYINV.arinvno LEFT OUTER JOIN
custmast ON eqdailyinv.eqpsldcust = custmast.kcustnum LEFT OUTER JOIN
repmast ON eqdailyinv.custins = repmast.custslsmn
WHERE (eqdailyinv.eqpslddate BETWEEN @fromdate AND @thrudate) AND (custmast.custsnum = '000') AND (eqdailyinv.eqpsldamt <> 0)
ORDER BY eqdailyinv.kequipnum

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 02:56:21

select * from
(
SELECT eqdailyinv.eqpsldinv, eqdailyinv.eqpstatus, eqdailyinv.eqpsldamt AS Sellprice, eqdailyinv.kequipnum, eqdailyinv.kmfg, eqdailyinv.kmodel,
eqdailyinv.eqpsldcust, eqdailyinv.eqpslddate, eqdailyinv.eqpphybr, eqdailyinv.custins, custmast.custname, repmast.custname AS repname,
GPCOSTBYINV.cost, CASE WHEN eqpsldamt <> 0 THEN (eqpsldamt - COALESCE (cost, 0)) END AS Grosspro
FROM eqdailyinv LEFT OUTER JOIN
GPCOSTBYINV ON eqdailyinv.eqpsldcust = GPCOSTBYINV.kcustnum AND eqdailyinv.kequipnum = GPCOSTBYINV.kequipnum AND
eqdailyinv.eqpsldinv = GPCOSTBYINV.arinvno LEFT OUTER JOIN
custmast ON eqdailyinv.eqpsldcust = custmast.kcustnum LEFT OUTER JOIN
repmast ON eqdailyinv.custins = repmast.custslsmn
WHERE (eqdailyinv.eqpslddate BETWEEN @fromdate AND @thrudate) AND (custmast.custsnum = '000') AND (eqdailyinv.eqpsldamt <> 0)
) as t
where Grosspro<>0
ORDER BY eqdailyinv.kequipnum

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 03:00:10

or repeat case in where also. I personally prefer method Madhi posted

SELECT eqdailyinv.eqpsldinv, eqdailyinv.eqpstatus, eqdailyinv.eqpsldamt AS Sellprice, eqdailyinv.kequipnum, eqdailyinv.kmfg, eqdailyinv.kmodel, 
eqdailyinv.eqpsldcust, eqdailyinv.eqpslddate, eqdailyinv.eqpphybr, eqdailyinv.custins, custmast.custname, repmast.custname AS repname, 
GPCOSTBYINV.cost, CASE WHEN eqpsldamt <> 0 THEN (eqpsldamt - COALESCE (cost, 0)) END AS Grosspro
FROM eqdailyinv LEFT OUTER JOIN
GPCOSTBYINV ON eqdailyinv.eqpsldcust = GPCOSTBYINV.kcustnum AND eqdailyinv.kequipnum = GPCOSTBYINV.kequipnum AND 
eqdailyinv.eqpsldinv = GPCOSTBYINV.arinvno LEFT OUTER JOIN
custmast ON eqdailyinv.eqpsldcust = custmast.kcustnum LEFT OUTER JOIN
repmast ON eqdailyinv.custins = repmast.custslsmn
WHERE (eqdailyinv.eqpslddate BETWEEN @fromdate AND @thrudate) AND (custmast.custsnum = '000') AND (eqdailyinv.eqpsldamt <> 0)
AND CASE WHEN eqpsldamt <> 0 THEN (eqpsldamt - COALESCE (cost, 0)) END >0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources