Re: select statement - please help

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

I'm a bit concerned that you think that 50% - 40% is a net change of 10%. Seems to me you used up 20% of available space between the two dates. Be careful that your result is not misinterpreted.

But the problem with your SQL is not a SQL problem ... it is a design problem with your tables. I think a redesign would solve the problem. I'd suggest one table for the drive and one for the date and freespace.

Well that an upgrading to a version of Oracle supported during the past decade. ;-)

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)