November 6, 2009 at 10:42 pm
I am having a brain fart and can't figure this one out. I have 3 columns I am running a query on
SELECT PRICE,SERVICE_FEE,DOWN_PAYMENT, PRICE+SERVICE_FEE-DOWN_PAYMENT "AMOUNT OWED"
FROM ACCOUNTS;
The problem is down_payment has nulls so the rows with a null value here return a null value for the amount owed how do I get it to return a value if there is a null? I am a newbie so please help
November 6, 2009 at 11:02 pm
SELECT PRICE,
SERVICE_FEE,
isnull(DOWN_PAYMENT,0) [DOWN_PAYMENT],
(PRICE+SERVICE_FEE- isnull(DOWN_PAYMENT,0)) [AMOUNT OWED]
FROM ACCOUNTS;
IN SQL isnull(variable, new_value)
IN ORACLE nvl(variable, new_value)
November 6, 2009 at 11:24 pm
Thank you!
November 7, 2009 at 10:40 am
In either SQL or Oracle you can use COALESCE, which is the Standard SQL function instead of ISNULL and NVL which are non-standard.
November 7, 2009 at 7:22 pm
David Portas (11/7/2009)
In either SQL or Oracle you can use COALESCE, which is the Standard SQL function instead of ISNULL and NVL which are non-standard.
Why do you think that's so important? Because you might migrate the code someday? 😛
Writing ANSI Standard code is like using only the 4 basic math functions on a scientific calculator just because someone else might only have a basic 4 function calculator. The myth of code portability doesn't make it worth not using the powerful extensions available in every RDBMS.
And, you have to consider other things... COALESCE is actually slower than ISNULL and, if you're working with millions of rows, milliseconds per row DO count.
There are only 3 things that matter in SQL code... accuracy, performance, and readability. ANSI Standards have nothing to lend to any of the 3 and they WON'T make your code portable without some pretty high sacrifices in performance. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2009 at 2:09 am
David Portas (11/7/2009)
In either SQL or Oracle you can use COALESCE, which is the Standard SQL function instead of ISNULL and NVL which are non-standard.
I agree entirely with Jeff's sentiments. ISNULL and COALESCE and both 'standard' T-SQL functions. They do however, behave slightly differently:
DECLARE @NullVar INTEGER;
PRINT ISNULL(@NullVar, 0.5); -- 0
PRINT COALESCE(@NullVar, 0.5); -- 0.5
November 8, 2009 at 2:38 am
and with Coalesce you can evaluate n replacement values.
Select coalesce(T.col1, O.colx, T.col2, 'I just don''t know')
from mytable T
inner join myothertable O
on O.fk1 = T.pk
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 8, 2009 at 4:19 am
Jeff Moden (11/7/2009)
David Portas (11/7/2009)
In either SQL or Oracle you can use COALESCE, which is the Standard SQL function instead of ISNULL and NVL which are non-standard.Why do you think that's so important?
COALESCE surely ought to be understood by the majority of SQL developers in the world, including those who have to maintain and develop the code in future. So I would suggest that it is more "readable" (more readily understood) than ISNULL which will be known only to a minority of database professionals working with one Microsoft product.
At least at the enterprise level many people have to support more than one DBMS. Microsoft-only shops are the exception rather than the rule. So why increase the burden on those people by making them work with a mixture of non-standard quirks of each product even when there are equivalent standard features available? This directly impacts on maintainability - maintenance being one consideration you didn't mention but which is quite often the largest expenditure on IT systems and therefore a very important factor to take into account.
As far as I'm aware the differences in performance and behaviour between ISNULL and COALESCE occur only in examples which rely on implicit casting. Implicit casting is generally a Bad Thing and in my experience there is no difference between ISNULL and COALESCE if you follow the good practice of avoiding implicit casting. I'm interested if anyone knows of examples where I'm wrong though.
As a matter of detail I would also remind you that SQL is an international standard developed and managed by an ISO committee, not by ANSI. This is a global forum and the US perspective isn't the only one. 🙂
November 8, 2009 at 6:22 pm
Heh... that's funny... I normally end up having to explain what COALESCE is to most developers both in SQL Server and Oracle shops.
If folks at the "enterprise level" have to support more than 1 RDBMS (been there, done that), then they better know about the "quirks" of each system especially when it comes to accuracy and performance. Consider the differences in Java and C#... if you're going to support both, then you better know both even at the "quirk" level especially when it comes to performance. The same holds true for mixed RDBMS shops.
You mentioned that I didn't mention the cost of maintainability especially due to readability... using COALESCE or NVL or ISNULL or any other code, standard or not, isn't going to fix that. Neither will using CURRENT_TIMESTAMP over SYSDATE or GETDATE() because those aren't the things that make code readable. Well founded comments and properly formatted code make code readable. It turns out that the real reason why rework is IT's largest expenditure doesn't have a thing to do with whether or not ANSI or ISO standard code was used or not. Rather, it's the fact that it takes people a long time to analyze what the code is supposed to do rather than being able to quickly scan through some well written comments.
I will, however, take a CASE statement over IIF any day of the week but folks who write day in and day out in another language may take exception to that because THEY're the ones that have to read it, day in and day out. 😉
So far as the performance difference between ISNULL and COALESCE, I don't blame you for not knowing about it because you're convinced that the "standard" use of COALESCE is better and you've probably not used ISNULL to any extent. Assuming identical datatypes, I'll also agree that even across 4 million rows, the difference is small...
DROP TABLE JBMTest
GO
--===== Create and populate a 4,000,000 row test table.
-- Column "RowNum" has a range of 1 to 100,000 unique numbers
-- Column "SomeIntX" has a range of 1 to 50,000 non-unique numbers
-- Jeff Moden
SELECT TOP 4000000
SomeID = IDENTITY(INT,1,1),
SomeInt1 = ABS(CHECKSUM(NEWID()))%50000+1,
SomeInt2 = ABS(CHECKSUM(NEWID()))%50000+1
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
DECLARE @Bitbucket INT
PRINT '===== ISNULL vs COALESCE on first operand ====='
SET STATISTICS TIME ON
SELECT @Bitbucket = ISNULL(SomeInt1,SomeInt2)
FROM dbo.JBMTest
SELECT @Bitbucket = COALESCE(SomeInt1,SomeInt2)
FROM dbo.JBMTest
SET STATISTICS TIME OFF
UPDATE dbo.JBMTest
SET SomeInt1 = NULL
PRINT '===== ISNULL vs COALESCE on second operand ====='
SET STATISTICS TIME ON
SELECT @Bitbucket = ISNULL(SomeInt1,SomeInt2)
FROM dbo.JBMTest
SELECT @Bitbucket = COALESCE(SomeInt1,SomeInt2)
FROM dbo.JBMTest
SET STATISTICS TIME OFF
RESULTS FROM ABOVE...
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2703 ms, elapsed time = 2750 ms.
SQL Server Execution Times:
CPU time = 3234 ms, elapsed time = 3236 ms.
(4000000 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 2562 ms, elapsed time = 2597 ms.
SQL Server Execution Times:
CPU time = 3266 ms, elapsed time = 3404 ms.
...but I don't normally work with such "small" tables... every millisecond counts.
So far as international standards go, they're not necessarily written by people who have an appreciation for working on data or situations that don't happen to meet their standards. They shouldn't be called "standards" at all... they should be called "guidlines by people who haven't necessarily walked a mile in your current shoes". Heh... I'll also add that just because 100 million people are doing it in a standard way, it doesn't necessarily make it right.
So far as readability in mixed RDBMS shops goes and it light of what I first said in this post, it would be interesting to take a pole of the developers across many such shops and find out how many of them actually know what COALESCE is. My experience on this forum is that a lot more don't know what it is than do. Even if they do and they run across the stray NVL or ISNULL, it probably won't kill them to look it up because it IS a part of the language they're supposed to know especially if they're supposed to support both. I'll go one step further and say that (again, based on what I see on this forum) many developers don't know about NVL or ISNULL never mind COALESCE. Heh... consider the original reason why the OP started this thread. 😉
Last but not least, let's get back to my original premise... true portability is a myth and even if it weren't, passing up on the more powerful extensions of Oracle, SQL Server, or any other RDBMS isn't worth it when performance is at stake. If you think that someone may not know about something in code, then add some comments... that's what they're there for. Lord help the newbie that has to work on my code without comments. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply