Hi Skylar,
I had to change your suggestion a little bit, but this one is working:
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(*) as count from backups where
(days(current_date) - days(backup_date) >= 30) and state='ACTIVE_VERSION' group
by node_name) b on a.node_name=b.node_name
Thank you VERY much for your help, I really appreciate it!
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: donderdag 9 april 2020 20:21
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query
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
********************************************************
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
********************************************************
