Home » SQL & PL/SQL » SQL & PL/SQL » Problem with SQL (oracle)
Problem with SQL (oracle) [message #418549] Mon, 17 August 2009 04:46 Go to next message
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 Smile


Re: Problem with SQL (oracle) [message #418551 is a reply to message #418549] Mon, 17 August 2009 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
COALESCE

Post a working Test case: create table and insert statements along with the result you want with these data.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Problem with SQL (oracle) [message #418552 is a reply to message #418549] Mon, 17 August 2009 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use nvl round each set of date subtractions.
Re: Problem with SQL (oracle) [message #418556 is a reply to message #418552] Mon, 17 August 2009 05:18 Go to previous messageGo to next message
dappi
Messages: 3
Registered: August 2009
Junior Member
Sorry Michel didnt't really understand? In wrong section?

@cookiemonster

You mean like this: nvl(round(fb.ordered - fb.work_tested))
Re: Problem with SQL (oracle) [message #418557 is a reply to message #418556] Mon, 17 August 2009 05:33 Go to previous messageGo to next message
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)
would become
NVL(fb.ordered - fb.estimated,0)
Re: Problem with SQL (oracle) [message #418558 is a reply to message #418557] Mon, 17 August 2009 05:40 Go to previous messageGo to next message
dappi
Messages: 3
Registered: August 2009
Junior Member
Ah, yes this works. Thanks a lot.
Re: Problem with SQL (oracle) [message #418566 is a reply to message #418556] Mon, 17 August 2009 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dappi wrote on Mon, 17 August 2009 12:18
Sorry Michel didnt't really understand? In wrong section?

What don't you understand?
COALESCE is a function that generalizes NVL.

Regards
Michel

Re: Problem with SQL (oracle) [message #418576 is a reply to message #418549] Mon, 17 August 2009 07:48 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
To be honest I don't really see how coalesce helps here either.
Re: Problem with SQL (oracle) [message #418586 is a reply to message #418576] Mon, 17 August 2009 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Mon, 17 August 2009 14:48
To be honest I don't really see how coalesce helps here either.


NVL(col,'value')=COALESCE(col,'value')

NVL is just a particular case of COALESCE.

Regards
Michel

Re: Problem with SQL (oracle) [message #418589 is a reply to message #418586] Mon, 17 August 2009 09:18 Go to previous messageGo to next message
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 #418594 is a reply to message #418589] Mon, 17 August 2009 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, and it is in the standard, not NVL.

Regards
Michel
Re: Problem with SQL (oracle) [message #418606 is a reply to message #418594] Mon, 17 August 2009 11:37 Go to previous messageGo to next message
Frank
Messages: 7880
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 Smile
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)
Re: Problem with SQL (oracle) [message #418609 is a reply to message #418606] Mon, 17 August 2009 12:23 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so what makes it "the standard" in you opinion?

It has been defined in ISO SQL. Why? Maybe because NVL existed in Oracle and not other rdbms. Wink 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.)
Now COALESCE exists in Oracle, Sybase, DB2, MS SQL Server, Postgres, MySQL (note that Ingres still hs not it).

Regards
Michel
Previous Topic: Index tablespace issue
Next Topic: exception with select query
Goto Forum:
  


Current Time: Sat Dec 03 22:13:01 CST 2016

Total time taken to generate the page: 0.14456 seconds