Re: select statement - please help

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Sun, 08 Feb 2004 15:42:36 +0100
Message-ID: <0aic20heaeiu4blh2vjuc2ovuadfnt6h9e@4ax.com>

On 8 Feb 2004 05:48:30 -0800, aiyoo2_at_aol.com (roshan) wrote:

>Hi,
>I am working with 2 tables that have to do with unix filesystems and
>the free space in them for a given a date.
>
>Table A Table B
>-------- -------
>file_sys_name intvl
>interval_index date
>free_space
>total_space
>
>The join from A to B is done by A.interval_index=B.intvl
>
>Given a start and end date, I am trying to calculate the percent
>growth
>for each file_sys_name.
>
>End Result Needed :
>
>file_sys_name %free_at_start_date %free_at_end_date %change
>------------- ------------------- --------------- -------
>/usr 50 40 -10
>/var 27 23 -4
>
>
>I am using oracle 8.05, can anybody suggest a single query that will
>work?
>I tried this(below), but I am getting an error. (I am a newbie in SQL)
>
>SELECT A.file_sys_name,
> (SELECT C.free_space/C.total_space*100 FROM A C, B
> WHERE C.interval_index=B.intvl
> AND B.date='start_date'
> AND A.file_sys_name=C.file_sys_name) AS %free_at_start_date,
>
> (SELECT C.free_space/C.total_space*100 FROM A C, B
> WHERE C.interval_index=B.intvl
> AND B.date='end_date'
> AND A.file_sys_name=C.file_sys_name) AS %free_at_end_date
>
>FROM A
>
>** note : I haven't done the growth column which will be the 2nd
>indented select minus the 1st indented select. (Is there an easy way
>to do that)
>
>thanks in advance for any help,
>Roshan

</rant> Hmm, the eternal problem: people exposed to sql server, who seem to think their sql will run without alteration against [a in this case desupported version of] Oracle, and who assume they don't need to verify whether this syntax actually works in their SQL reference manual, because that's what you have this group for: to do your homework </end rant>

SELECT A.file_sys_name,
"%free_at_start_date",
"%free_at_end_date"
FROM A,
  (SELECT C.free_space/C.total_space*100 "%free_at_start_date"    FROM A C, B

     WHERE C.interval_index=B.intvl
     AND B.date='start_date'
     AND A.file_sys_name=C.file_sys_name)

,
  (SELECT C.free_space/C.total_space*100 "%free_at_end_date"

     FROM A C, B
     WHERE C.interval_index=B.intvl
     AND B.date='end_date'
     AND A.file_sys_name=C.file_sys_name) 

--
Sybrand Bakker, Senior Oracle DBA

Received on Sun Feb 08 2004 - 08:42:36 CST