Ah, I think the problem is that comparing anything with NULL will be NULL
(except comparing NULL with NULL, which is true). Try this:
select b.node_name, count(*)
from backups a
right join nodes b on a.node_name=b.node_name and b.node_name like '%-ORC'
where
(a.backup_date is null or ((days(current_date) - days(a.backup_date) >=
30)))
and (a.state is null or a.state='ACTIVE_VERSION')
group by b.node_name
Note that I also changed the "group by" and projection to use node_name
from the nodes table since that's guaranteed to be set, rather than backups
which would only be set for nodes with entries in the backups table.
On Wed, Apr 08, 2020 at 08:26:42AM +0000, Loon, Eric van (ITOP NS) - KLM wrote:
> 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
> ********************************************************
--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine
