| Problem with SQL (oracle) [message #418549] |
Mon, 17 August 2009 04:46  |
dappi
Messages: 3
Registered: August 2009
|
Junior Member |
|
|
Okey, this must be a simple thing but Im somewhat new so...
(nvl(fb.estimated,sysdate) - fb.started)+(fb.ordered - fb.estimated)+(fb.ordered - fb.work_tested) as WHOLETIME
I want sum up all those fields as WHOLETIME. All estimated, started etc. are date cols. But the problem is that some of those might be null's and then it gives null for the whole thing. What I would like to do is that it would ignore that field and sum up all the others.
For example:
nvl(fb.estimated,sysdate) - fb.started)+(fb.ordered - fb.estimated)+(fb.ordered - fb.work_tested) as WHOLETIME
if estimated col has date 2007-21-11 and started col has date 2008-12-12 -no problem there, but for example if there isnt any date in ordered field it gives null and the whole thing goes wrong. I think could solve this problem with IF clauses but is there easier way. Something like: nvl(fb.ordered,0) < it gives zero if there's no date.
Sorry for bad english, hope you understand and can help me 
|
|
|
|
|
|
|
|
|
|
| Re: Problem with SQL (oracle) [message #418557 is a reply to message #418556] |
Mon, 17 August 2009 05:33  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
NVL needs a second argument.
What Cookiemonster was suggesting is to replace each bracked dat calculation with an NVL that means it will be treated as 0 in the even of a null being present.
So (fb.ordered - fb.estimated) NVL(fb.ordered - fb.estimated,0) |
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem with SQL (oracle) [message #418589 is a reply to message #418586] |
Mon, 17 August 2009 09:18 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
|
And if I remember correctly, COALESCE is actually faster, as NVL will evaluate both terms, but COALESCE will only evaluate the second term if the first term is null.
|
|
|
|
|
|
| Re: Problem with SQL (oracle) [message #418606 is a reply to message #418594] |
Mon, 17 August 2009 11:37 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Michel Cadot wrote on Mon, 17 August 2009 17:16 | Yes, and it is in the standard, not NVL.
|
Wow.. that is rather bold
I know a large part of the community uses NVL, rather than COALESCE, so what makes it "the standard" in you opinion?
I do agree, by the way, that COALESCCE is a better way than NVL
(however, I would rather say that NVL is a special case of COALESCE.. COALESCE is overloaded, where the one with 2 arguments is NVL)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
If you searched in usenet forum archives for all databases but Oracle you will see in every one questions similar to "I am trying to find the equivalent in XXX to the Oracle sql function nvl()" but for Ingres that had the equivalent IFNULL one. (Later some of them introduced ISNULL.)