SQL Database Integration

Re: SQL Question(Group By)

m181354 — Thu, 07 Feb 2008 20:37:27 GMT

I created a SQL query with a Group By clause using Toad for SQL Server. It worked great. The query is below. But when I run the query in InfoPath, I get the error message below. Does that mean InfoPath does not support the Group By clause? I could create a view but I need to pass in variables from the form.

The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
No query fields have been generated because the specified SQL statement is too complex for InfoPath to parse.

SELECT [LaborSummary].PROGRAM, [LaborSummary].OBS, [LaborSummary].ECAM, SUM ([LaborSummary].SV_HRS) as SV_TOTAL

from [LaborSummary] as [LaborSummary]

where ([LaborSummary].PROGRAM = 'EA18GSDD") and ([LaborSummary].ECAM Like "1%")

group by [LaborSummary].PROGRAM, [LaborSummary].OBS, [LaborSummary].ECAM

Re: SQL Question(Group By)

Matt Faus — Thu, 07 Feb 2008 15:41:03 GMT

Hi Steve,

The error message is troubling and it might mean InfoPath does not understand the Group By clause. However, you do have a problem with the query.

When using the Group By clause, you must include all columns that you select. So, in your query, you must either remove ECAM from the select statement, or add it in the group by clause.

In general, I would suggest that when you are formulating your queries you try them in SQL Server Management studio first, and then once you have them perfected there, transfer them to InfoPath. If you had done this in this scenario, the error message from SQL Server would have said almost exactly what I did above.

Re: SQL Question(Group By)

Clay Fox — Thu, 07 Feb 2008 14:49:49 GMT

Hey Steve.

If SQL query works in SQL then it should work anywhere.

You have three fields in your select but only one is a sum. For Group By to work you can only have one field that is not summed or have a function to it and you must group on that field.

SQL Question(Group By)

m181354 — Thu, 07 Feb 2008 13:25:19 GMT

I have a SQL statement with a Group By clause that get the following error:

The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
No query fields have been generated because the specified SQL statement is too complex for InfoPath to parse.

Does anyone know how to code a SQL statement with a Group By clause this get this to work in InfoPath?

SQL statement below:

"select ECAM, OBS, sum(SV_HRS) as SV_HRS_TOTAL ";

"from ";

"[LaborSummary] as [LaborSummary] ";

"where (PROGRAM = '" + programPrefix + "' and LaborSummary.[ECAM] Like '" + ecamPrefix + "%') ";

"group by OBS ";