return calculated null as 0

Question

Post reply

return calculated null as 0

terry.sanders

Newbie

Points: 1
More actions
July 10, 2010 at 8:25 am

#97232

If I have no results for CNTWO2 in July I would like to show a 0, as it is I don't even show a row with July. This select is part of a sub page in Actuate 8.
SELECT count(nvl(wonum,0))CNTWO2, to_char(reportdate,'YYYYMM')year,substr(reportdate,4,8)Month, nvl(estlabhrs,0)
FROM WORKORDER
where STATUS not LIKE '%CAN'
AND ISTASK = '0'
AND WORKTYPE = 'PROJECT'
and reportdate between to_date('01-APR-2010','DD-MON-YYYY') and to_date('30-JUL-2010','DD-MON-YYYY')
and parent is null
--and 1=1
group by to_char(reportdate,'YYYYMM'), substr(reportdate,4,8), estlabhrs
order by to_char(reportdate,'YYYYMM'), substr(reportdate,4,8), estlabhrs;

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Based on the limited information we have so far I recommend using a calendar table as the left part of an outer join.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

Right there with Lutz - It would be very helpful to provide more detail. A calendar table appears to be a suitable solution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 3

A few more things to notice:

I'm not sure how the functions to_char and to_date are written, but I guess both can be replaced by the built-in function CONVERT(). The to_date function isn't even necessary since SQL Server will perform an implicit conversion.

Furthermore, if the STATUS column is the first column in an index, this index will not be used due to the "not LIKE '%CAN'".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 4

lmu92 (7/11/2010)
A few more things to notice:
I'm not sure how the functions to_char and to_date are written, but I guess both can be replaced by the built-in function CONVERT(). The to_date function isn't even necessary since SQL Server will perform an implicit conversion.
Furthermore, if the STATUS column is the first column in an index, this index will not be used due to the "not LIKE '%CAN'".

It's Oracle.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 5

Jeff Moden (7/11/2010)
lmu92 (7/11/2010)
A few more things to notice:
I'm not sure how the functions to_char and to_date are written, but I guess both can be replaced by the built-in function CONVERT(). The to_date function isn't even necessary since SQL Server will perform an implicit conversion.
Furthermore, if the STATUS column is the first column in an index, this index will not be used due to the "not LIKE '%CAN'".
It's Oracle.

Ooops! My fault (never had / been forced to use it, fortunately)...

So, the correct answer would then be: use a c.u.r.s.o.r.? ;-):-D;-)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 6

LutzM (7/11/2010)
Ooops! My fault (never had / been forced to use it, fortunately)...
So, the correct answer would then be: use a c.u.r.s.o.r.? ;-):-D;-)

Absolutely NOT! 😉 Good setbased code is also faster and easier to read even in Oracle. It's just a bit more difficult to do in Oracle because Oracle won't allow you to overlay variables in a single query, temp tables don't operate quite the same way, and the UPDATE/DELETE statements don't support FROM for the normal types of joins that you can do in SQL Server (although it does have MERGE).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)