Group By clause in scripting

sdk development
1

Hi friends,
I need some help in sending a Group By clause to generate a report.

I am generating a BO report using the VB OLE automation. The report here takes some user inputs (as fields that come in the report, conditions etc) dynamically from some VB controls. Depending on the user choice I am sending a command to BO to generate a report with the fields the user wants in the report along with the Conditions on the data. The user here can also specify a Group By. (Ex. my SQL might look like this - Select * from Employee Where Employee.emp_salary < 5000 Group By Employee.Dept_Id ).

I am unable to add a Group By clause to the Data providers. To create the report I am using the various BO Classes as BOApplication, BOCondition etc. But I donot find any class or method to send a Group By. Can anybody help with this???

Thanks

Kalyan Jandhyala
Bay Networks

Listserv Archives (BOB member since 2002-06-25)

2

In a message dated 98-10-31 14:34:37 EST, you write:

[snip stuff about scripting question]

The user here can also specify a Group By.
(Ex. my SQL might look like this - Select * from Employee
Where
Employee.emp_salary < 5000 Group By Employee.Dept_Id ).

Unfortunately I don’t have an answer for your scripting question, but I do have a concern about what you are trying to do. It may be different in your SQL, but in most databases I don’t think you can group by only one item. Therefore the SQL you wrote above will not work.

In Oracle, for example, every column in the select clause must be grouped; either by being included in a GROUP BY, or by a aggregate (group) function such as SUM(), MAX(), etc. So, you could not do this, for example:

select col1, col2, col3, sum(col4)
from table

… because co1, col2, and col3 are not grouped in any way while col4 is. You can also not do this:

select col1, col2, col3, sum(col4)
from table
group by col1

because, while col1 and col4 are grouped, col2 and col3 still are not. In other words, if one column in the select is somehow grouped then all of the columns must be grouped. The only valid option for using a sum on col4 would be:

select col1, col2, col3, sum(col4)
from table
group by col1, col2, col3

Now, if what you are really looking for is a BREAK on the resulting report data, you can certainly do that. But that is totally independent of the SQL code that is sent to the database.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)