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 +, 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 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 +, 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 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 +, 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 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 +, 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
