You're welcome! Glad that ended up working, despite not having a great way to test it here. :)
On Tue, Apr 14, 2020 at 12:07:33PM +0000, Loon, Eric van (ITOP NS) - KLM wrote: > 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 > ******************************************************** -- -- Skylar Thompson (skyl...@u.washington.edu) -- Genome Sciences Department, System Administrator -- Foege Building S046, (206)-685-7354 -- University of Washington School of Medicine
