Hi Skylar,
I tried your query, but it also returns just one node with a number > 0, all
other nodes (which have 0 files) are not listed.
Thanks for your help!
Kind regards,
Eric van Loon
Air France/KLM Storage & Backup
-----Original Message-----
From: ADSM: Dist Stor Manager <ADSM-L@VM.MARIST.EDU> On Behalf Of Skylar
Thompson
Sent: dinsdag 7 april 2020 23:42
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query
I think what you're looking for is an outer join:
select a.node_name, count(*)
from backups a
right join nodes b on a.node_name=b.node_name and a.node_name like '%-ORC'
where
((days(current_date) - days(backup_date) >= 30))
and a.state='ACTIVE_VERSION'
and b.contact like '%Oracle%'
group by a.node_name
On Tue, Apr 07, 2020 at 09:08:58AM +0000, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi guys,
>
> It must be something very easy, but I can't seem find the solution myself...
> This is the query I use to list the total amount of Oracle backup files older
> than 30 days:
>
> select count(*) as OBSOLETE_BACKUPS from backups a,nodes b where
> ((days(current_date) - days(backup_date) >= 30)) and a.node_name like '%-ORC'
> and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and b.contact like
> '%Oracle%'
>
> I also use this statement to list the files per node:
>
> select a.node_name, count(*) from backups a,nodes b where
> ((days(current_date) - days(backup_date) >= 30)) and a.node_name like
> '%-ORC' and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and
> b.contact like '%Oracle%' group by a.node_name
>
> The statement works fine, but it only shows the nodes with an amount
> of files > 0. I'm looking for the same command which shows all %-ORC nodes.
> So when the amount is 0, it should display the node_name along with the value
> 0. I can't figure out how to accomplish it. :( Thanks for any help in advance!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Storage & Backup
> ********************************************************
> 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 (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine
********************************************************
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
********************************************************
