Re: SQL joins ?

In article <4ctju7$tc_at_gate.bton-health.sthames.nhs.uk>, peter_at_gate.bton-health.sthames.nhs.uk (Peter Gross) writes:
|> I am *very* rusty with my SQL, can someone help me with the following
|> problem displaying _both_ the free space and the total space available
|> by o/s file and tablespace.
|>
|> I am using the following script that provides with the right output, but
|> not in the format I would like :-(
|>
|> #-------------------
|>
|> column status format a6 heading ''
|> column file_id format 99 heading 'ID'
|> column bytes format 999999999 heading 'BYTES'
|> column tablespace_name format a11 heading 'TABLE-SPACE'
|> column file_name format a22 heading 'FILE-NAME'
|>
|>
|> select file_id, file_name, tablespace_name, bytes
|> , 'Total' status
|> from dba_data_files
|> union
|> select file_id, ' ' file_name
|> , tablespace_name, sum(bytes), 'Free' status
|> from dba_free_space
|> group by file_id, tablespace_name
|> order by 3 asc, 1 asc, 5 desc
|> /
|>
|> #-------------------
|>
|> This gives the following output:
|>
|> ID FILE-NAME TABLE-SPACE BYTES STATUS
|> --- ---------------------- ----------- ---------- ------
|> 1 /usr4/business_1.dbs BUSINESS 52428800 Total
|> 1 BUSINESS 39792640 Free
|>
|>
|> What I would like is:
|>
|> ID FILE-NAME TABLE-SPACE TOTAL FREE
|> --- ---------------------- ----------- ---------- ----------
|> 1 /usr4/business_1.dbs BUSINESS 52428800 39792640
|>
|>
|>
|> Any ideas on how I should go about this folks ?
|>
|> Thanks in advance,
|> Peter Gross.
|>
|> --
|> The ideas, suggestions, jokes, cautions, complaints, comments, scripts, insults,
|> critiscms, observations, diagrams, sarcastic remarks, stories, programs, and
|> thoughts shown in the text above are mine and mine alone, and are NOT those of
|> my employer or anyone else for that matter (unless I say otherwise ;-)

I don't know if this is the best way, but it works ! So you can try it.

create table free_temp as select file_id, sum(bytes) "FREE"

                          from dba_free_space
                          group by file_id;

column file_id format 99 heading 'ID';
column file_name format a40 heading 'FILE-NAME'; column tablespace_name format a11 heading 'TABLE-SPACE';

select d.file_id, d.file_name, d.tablespace_name, d.bytes "Total", f.FREE "Free" from dba_data_files d, free_temp f
where d.file_id = f.file_id;

drop table free_temp;

Good luck !

Jean-Marc Received on Wed Jan 10 1996 - 00:00:00 CET