| Author |
 Topic  |
|
|
JezLisle
Posting Yak Master
United Kingdom
132 Posts |
 Posted - 09/15/2008 : 05:44:34
|
I have this query which returns the max number in the table, i'm looking to find out how I can change the return value if Null then how can I replace it with 0 (Zero)?
SELECT MAX([Weeks Waiting]) AS 'Max'
FROM jez.CYPPhyPaediatric
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 09/15/2008 : 05:50:30
|
use ISNULL(MAX(WeeksWaiting),0) or
COALESCE(MAX(WeeksWaiting),0) |
 |
|
|
abacusdotcom
Posting Yak Master
Nigeria
130 Posts |
Posted - 09/15/2008 : 06:20:57
|
Visa, why do you include MAX, what happens when MAX is not used with isnull or coalesce commands?
I sign for fame not for shame but all the same, I sign my name. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 09/15/2008 : 06:32:16
|
quote:
Originally posted by abacusdotcom
Visa, why do you include MAX, what happens when MAX is not used with isnull or coalesce commands?
I sign for fame not for shame but all the same, I sign my name.
i didnt include it. it was in the code op posted. i just copy pasted from it  |
|
|
|
JezLisle
Posting Yak Master
United Kingdom
132 Posts |
Posted - 09/15/2008 : 07:13:49
|
| Excellent, Thanks for help... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/24/2008 : 17:00:41
|
SELECT CAST(MAX([Weeks waiting]) AS VARCHAR(11)) + ' weeks'
from (
select max([Weeks waiting]) AS [Weeks waiting]
from jez.CYPPhyPaediatric
union all
select 0
) AS d
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
miked1978
Starting Member
25 Posts |
Posted - 09/25/2008 : 09:09:13
|
Sorry to hijack this thread but your title fits my problem.
I'm getting some null value records eliminated because there are some null values in my Ahead and Behind columns below. How would I replace the nulls with 0 in the following code?
Select Mondate, Ship, Hull, Div, sum (Ahead) as Ahead, sum (Behind) as Behind, sum (SVc) As SVc, sum (CVc) as CVc
into tbAhdBhdstep3
from tbAhdBhdstep2
Group by Mondate, Ship, Hull, Div
Order by Hull, Div
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 09/25/2008 : 09:11:57
|
quote:
Originally posted by miked1978
Sorry to hijack this thread but your title fits my problem.
I'm getting some null value records eliminated because there are some null values in my Ahead and Behind columns below. How would I replace the nulls with 0 in the following code?
Select Mondate, Ship, Hull, Div, sum (Ahead) as Ahead, sum (Behind) as Behind, sum (SVc) As SVc, sum (CVc) as CVc
into tbAhdBhdstep3
from tbAhdBhdstep2
Group by Mondate, Ship, Hull, Div
Order by Hull, Div
not sure what you're asking for
SUM() will ignore NULL values and return sum of non null values alone. Even if you're trying to convert NULL values to 0 you will get same result. didnt understand the purpose behind changing them to 0. |
|
|
|
miked1978
Starting Member
25 Posts |
Posted - 09/25/2008 : 09:19:46
|
quote:
Originally posted by visakh16
quote:
Originally posted by miked1978
Sorry to hijack this thread but your title fits my problem.
I'm getting some null value records eliminated because there are some null values in my Ahead and Behind columns below. How would I replace the nulls with 0 in the following code?
Select Mondate, Ship, Hull, Div, sum (Ahead) as Ahead, sum (Behind) as Behind, sum (SVc) As SVc, sum (CVc) as CVc
into tbAhdBhdstep3
from tbAhdBhdstep2
Group by Mondate, Ship, Hull, Div
Order by Hull, Div
not sure what you're asking for
SUM() will ignore NULL values and return sum of non null values alone. Even if you're trying to convert NULL values to 0 you will get same result. didnt understand the purpose behind changing them to 0.
What would I use other then sum?
There are several records being eliminated becuase the Ahead column contains nulls. I need to show those nulls as zero if possible. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 09/25/2008 : 09:22:43
|
| whats your requirement? to show sum or to show individual values of Ahead & Behind? |
|
|
|
miked1978
Starting Member
25 Posts |
Posted - 09/25/2008 : 09:30:28
|
quote:
Originally posted by visakh16
whats your requirement? to show sum or to show individual values of Ahead & Behind?
Well its to show sum. If I don't I get too many records. Kinda tricky huh?
I think I may just do an update before this chunk of code and update all null values to zero. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 09/25/2008 : 09:31:56
|
quote:
Originally posted by miked1978
quote:
Originally posted by visakh16
whats your requirement? to show sum or to show individual values of Ahead & Behind?
Well its to show sum. If I don't I get too many records. Kinda tricky huh?
I think I may just do an update before this chunk of code and update all null values to zero.
sorry your explanation doesnt make much sense. can you illustrate what you're telling with some sample data? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
miked1978
Starting Member
25 Posts |
Posted - 09/25/2008 : 10:33:57
|
quote:
Originally posted by visakh16
quote:
Originally posted by miked1978
quote:
Originally posted by visakh16
whats your requirement? to show sum or to show individual values of Ahead & Behind?
Well its to show sum. If I don't I get too many records. Kinda tricky huh?
I think I may just do an update before this chunk of code and update all null values to zero.
sorry your explanation doesnt make much sense. can you illustrate what you're telling with some sample data?
Maybe this will help. Here is the code where I'm actually setting the Ahead and Behind values. Maybe if I can create an If statement setting nulls to zero?
update tbAhdBhdstep2
set Ahead = SVc
where Svc > 0
update tbAhdBhdstep2
set Behind = SVc
where Svc < 0 |
Edited by - miked1978 on 09/25/2008 10:34:16 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 09/25/2008 : 10:47:01
|
| i told you to post some sample data to illustrate what you're telling. |
|
|
| |
Topic |
|
Replace Null with 0
