I forgot that GROUP BY depended on having an entry in the result table.
Unfortunately I don't have a TSM server with a reasonably-sized backups
table to test on (production ones are 1+ billion entries), so I'm kind of
in thought experiment territory right now, but what if you did an outer
join from the nodes table against a sub-query on the backups table? That
would let you replace the count for nodes without an entry in the sub-query
with 0 with CASE:
select
a.node_name,
case when b.count is null
then 0
else b.count
end as count
from nodes a
left join (select node_name,count(*) from backups where
(days(current_date) - days(a.backup_date) >= 30)
and a.state='ACTIVE_VERSION'
group by node_name) b on a.node_name=b.node_name
On Thu, Apr 09, 2020 at 10:53:09AM +0000, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi Skylar,
>
> Sorry, but this one doesn't work either, it returns the same results as all
> others. I don't think the NULL result is the issue here, it seems to be the
> way the results are returned as soon as you select multiple columns. In the
> following example ,when I select just one, the result is 0:
>
> select count(*) from backups where node_name='RAC_098-ORC' and
> days(current_date) - days(backup_date) >= 3000
>
> Unnamed[1]
> ------------
> 0
>
> But as soon as you select multiple columns, the result is not 0, but "no
> match found":
>
> select node_name, count(*) from backups where node_name='RAC_098-ORC' and
> days(current_date) - days(backup_date) >= 3000 group by node_name
> ANR2034E SELECT: No match found using this criteria.
> ANS8001I Return code 11.
>
> Thanks again 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: woensdag 8 april 2020 16:03
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: SQL query
>
> 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
> ********************************************************
> 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
