SQL Decode problem

whuppins

I'm writing a query where several columns are actually sub-queries whose final result is a number. In practice, many of these values will be zeroes, in which case I want to substitute in a null value for a "0" so the report looks a little cleaner (instead of having zeroes everywhere). This seems like it should be simple to do with DECODE:

DECODE(<big long sub-query>, 0, null)

The problem with this is that DECODE will return null if it doesn't find a match. You can provide a default value as an argument, but in my case, this would involve nesting the entire sub-query into the DECODE a second time:

DECODE(<big long sub-query>, 0, null, <big long sub-query>)

This seems tremendously inefficient. Is there a way to solve this without having to include the sub-query twice? I looked at CASE briefly but I still don't see how that would solve the root problem.

Thanks!

Deebaser

create a temp/hash table and do it on the back end



UPDATE #table_name
SET column_name = null
FROM #table_num
WHERE column_name = 0

SELECT * FROM #table_name

rinse and repeat for all the columns.

whuppins

I can't run any updates. Can you do a similar thing with variables?

This is Oracle PL/SQL, by the way.

Deebaser

They won't even let you make a temp table? That's fucking brutal.

Aside from exporting the data and doing an excel find/replace I got nothing. Sorry =(


(Out of curiosity. Is this for work or school?)

whuppins

It's for work. I've been asked to implement a solution we received from a colleague at another company. The solution involves creating a view that will be referenced by a third-party piece of software. Because the data is ultimately going to be displayed in a 'non-native' environment, we have some limitations on how much we can do.

Deebaser

ahhh that makes sense. Sorry my nerd skills are weaksauce. =(

CASE could work if you can shoehorn
(subquery) = 0
into the WHEN, but I dunno if that would even work as it would may require implicit conversion to get a null value.

whuppins

Actually, I think I may have stumbled upon a solution (based on an earlier thread in this forum). I think I can just change DECODE to REPLACE in my original example and it will work. Not really the intended use of REPLACE, but I think it'll get the job done! Testing underway...