SQL Question(Group By) - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

SQL Question(Group By)

Last post 02-07-2008 12:37 PM by m181354. 3 replies.
Page 1 of 1 (4 items)
Sort Posts: Previous Next
  • 02-07-2008 05:25 AM

    SQL Question(Group By)

    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 ";

  • 02-07-2008 06:49 AM In reply to

    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.

  • 02-07-2008 07:41 AM In reply to

    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.

    Matt Faus / Microsoft InfoPath MVP
    Qdabra® Software / Streamline data gathering to turn process into knowledge
  • 02-07-2008 12:37 PM In reply to

    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

Page 1 of 1 (4 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.