Can't SUM() - a column function - without GroupBy. Remove the SUM() and the query is fine. Obviously doesn't do what you want but Lynn's response might.
ps. DB2 probably ignores it but you've repeated the FileSpace_ID comparison in the Where clause. ------> signature = 8 lines follows <-------- Neil Duffee, Joe Sysprog, uOttawa, Ont, Canada Telephone: 1 613 562 5800 x4585 mailto: Nduffee of uOttawa.ca http:/ /aix1.uOttawa.ca/ ~nduffee "How *do* you plan for something like that?" Guardian Bob, Reboot "For every action, there is an equal and opposite criticism." "Systems Programming: Guilty, until proven innocent" John Norgauer 2004 "Schrodinger's backup: The condition of any backup is unknown until a restore is attempted." John McKown 2015 -----Original Message----- From: Schaub, Steve <steve_...@bcb...com> Sent: January 15, 2020 7:43 AM Subject: Query help joining 2 tables SP 8.1.9 I'm trying to write a Powershell script that will return data on all filespaces that haven't been backed up in > x days. I've had the basic query from the Filespaces table working for years, but now I'm trying to include the total object count from the Occupancy table for each filespace, and I'm not adept enough at query writing to do that. I suspect I need some flavor of join but not sure which one. Here was my first attempt, more to show what I'm trying to do, it obviously isn't working. If anyone has this working and is willing to share, I'd appreciate it. SELECT '$myTsmServer' AS TsmServer, FS.Node_Name, FS.Filespace_Name, FS.Filespace_ID, DAYS(Current_Date)-DAYS(FS.Backup_End) AS Age, SUM(OC.Num_Files) FROM Filespaces FS, Occupancy OC WHERE FS.Filespace_ID=OC.Filespace_ID AND FS.filespace_id=OC.filespace_id AND DAYS(Current_Date)-DAYS(FS.Backup_End)>$myAgeLimit Steve Schaub Systems Eng II, Backup & Recovery BlueCross BlueShield of Tennessee
