To GROUP, or not to GROUP...

Hi, I'm developing a SQL statement (that will be called by a RUNSQLSTM in a CL program) that joins 6 tables. I use nested table expressions and a few subqueries. It's pretty ambitious for someone who has not done much SQL coding.

The main problem is that the "left-most" table uses Group By, but I have to specify a couple of column names in that table that have no grouping functions - they are detail-level columns. I just need to specify them so I can join them later to other tables. I can get the query to run by including the detail-level columns in my Group By statement but then, of course, I do not get the grouping level that I need.

Am I trying to do too much in one statement, or is there a way to join at a detail record level to get data from other tables and still retain output at a group level?

I decided to remove the grouping functions, output all the detail to one file, and then run a second SQL statement to do the grouping. It works fine. This issue - as short-lived as it was - is resolved.