Home » SQL & PL/SQL » SQL & PL/SQL » if or case within a select (10g)
if or case within a select [message #363019] Thu, 04 December 2008 15:52 Go to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

i got this sql :

select ts_name "TABLESPACE_NAME", status "Status", tssize "Size(MB)", used "Used(MB)", free "Free(MB)", percentagefree "%Free", (free * 90/100)/maxspaceused "Days to fill 90%" from tablespace_rep order by 7;


the last column if you notice is a computed column. if its value is null i want to print the value of the column "free" in its stead.

or

if maxspaceused is null or < 1, then i want to simply print the column "free". this will work as well.

how do it do this?
Re: if or case within a select [message #363020 is a reply to message #363019] Thu, 04 December 2008 16:09 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
Here is one way:
(CASE
   WHEN (free * 90/100)/maxspaceused IS NULL
     OR (free * 90/100)/maxspaceused < 0 THEN free
 ELSE (free * 90/100)/maxspaceused
 END) "Days to fill 90%"

Thanks,
Jim
Re: if or case within a select [message #363050 is a reply to message #363019] Thu, 04 December 2008 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: if or case within a select [message #363238 is a reply to message #363019] Fri, 05 December 2008 08:57 Go to previous message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

jim, thanks a lot - the case worked.

michel, sorry for repeated errors in my posts. i'll do it more carefully next time.
Previous Topic: Exception handling
Next Topic: how to create a view of table in diff schemas (merged a 3rd time)
Goto Forum:
  


Current Time: Mon Dec 05 12:39:55 CST 2016

Total time taken to generate the page: 0.08743 seconds