You'll want to do a join across both tables on the node name. Something like
this:
SELECT f.node_name,f.filespace_name,o.physical_mb -
FROM filespaces f -
INNER JOIN occupancy o ON f.node_name=o.node_name -
WHERE -
(days(f.backup_end) < (days(current_date)-30)) -
ORDER BY o.physical_mb DESC
On Thu, Mar 13, 2014 at 05:16:37PM +0100, Loon, EJ van (SPLXM) - KLM wrote:
> Dear TSM-ers,
>
> I'm trying to generate a SQL statement to create a list of filespaces
> which are not backed up for more than 30 days, sorted on their occupancy
> size. This is what I've got so far:
>
>
>
> select node_name, filespace_name, physical_mb from occupancy where
> filespace_name in (select filespace_name from filespaces where
> (days(filespaces.backup_end) < (days(current_date)-30))) order by
> physical_mb desc
>
>
>
> It doesn't work however, because filespace names are not unique. As soon
> as a different node is found with the same filespace_name it's listed
> too and that's not what I'm aiming for. I guess nested SQL is not the
> way to go, but I don't know the solution.
>
> Thanks you very much for your help in advance!!!
>
> Kind regards,
>
> Eric van Loon
>
> AF/KLM Storage Engineering
>
>
>
> ********************************************************
> For information, services and offers, please visit our web site:
> http://www.klm.com. This e-mail and any attachment may contain confidential
> and privileged material intended for the addressee only. If you are not the
> addressee, you are notified that no part of the e-mail or any attachment may
> be disclosed, copied or distributed, and that any other action related to
> this e-mail or attachment is strictly prohibited, and may be unlawful. If you
> have received this e-mail by error, please notify the sender immediately by
> return e-mail, and delete this message.
>
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its
> employees shall not be liable for the incorrect or incomplete transmission of
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch
> Airlines) is registered in Amstelveen, The Netherlands, with registered
> number 33014286
> ********************************************************
--
-- Skylar Thompson (skylar2 AT u.washington DOT edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine
|
