locked
TableAdapter Derived Table Not Parsing RRS feed

  • Question

  • User-2057173053 posted

    I am trying to build a report inside an ASP.Net page (VS 2012).  I created a dataset within the Solution Explorer and created a TableAdapter element within it.  I then put in this SQL statement and when I get to the Query Builder and press Execute Query, an error comes up saying "SQL statement could not be parsed." And "The query cannot be represented graphically in the Diagram and Criteria Pane."

    It runs fine in TOAD and even though the error comes up, it still returns accurate data in the Query Builder.  Great so I just proceed.  But when I get to the FIll and Get statements, it cannot generate their statements.  Can anyone help me with this?

    SELECT
    INVOICEDATE,
    POI.INVOICEPK,
    POI.TOTAL,
    Project.ShopName,
    SUBSTRING(Project.UDFChar3, 1, 4) AS PROJECT_FY,
    Project.UDFChar3 + ' - ' + Project.ProjectName AS Project,
    Project.Status,
    Project.Budget,
    Project.UDFChar1 AS Type,
    Project.UDFChar2 AS Category,
    Project.AuthStatusDate AS Approval_Date
    FROM Project LEFT OUTER JOIN
    PurchaseOrder AS PO ON Project.ZoneID = PO.UDFChar1 LEFT OUTER JOIN
    PurchaseOrderInvoice AS POI ON POI.POPK = PO.POPK INNER JOIN
    /* here is the derived table */
    (SELECT
    Project_1.UDFChar3
    FROM Project AS Project_1 LEFT OUTER JOIN
    PurchaseOrder AS PO ON Project_1.ZoneID = PO.UDFChar1 LEFT OUTER JOIN
    PurchaseOrderInvoice AS POI ON POI.POPK = PO.POPK
    WHERE      (POI.InvoiceDate BETWEEN CASE WHEN MONTH(GETDATE()) IN ('10', '11', '12')
    THEN '10/01/' + CAST(YEAR(GETDATE()) AS varchar) ELSE '10/01/' + CAST(YEAR(GETDATE()) - 1 AS VARCHAR) END AND GETDATE()) OR
    (Project_1.CLOSED BETWEEN CASE WHEN MONTH(GETDATE()) IN ('10', '11', '12')
    THEN '10/01/' + CAST(YEAR(GETDATE()) AS varchar) ELSE '10/01/' + CAST(YEAR(GETDATE()) - 1 AS VARCHAR) END AND GETDATE()) OR
    (Project_1..STATUS = 'ISSUED')
    GROUP BY Project_1.UDFChar3) AS PROJ ON PROJ.UDFChar3 = Project.UDFChar3
    GROUP BY Project.UDFChar3, Project.ShopName, Project.ProjectName, Project.Status, Project.Budget, Project.UDFChar1,
    Project.UDFChar2, Project.AuthStatusDate, POI.InvoiceDate, POI.TOTAL, POI.INVOICEPK

    Friday, September 13, 2013 9:35 AM

Answers

  • User-2057173053 posted

    There was no error.  I am not sure why the derived query would not execute.

    I ended up creating a view with the query posted and then queried the view.  Simple enough to get me through the problem.  Still, I'd like to understand why the Query Editor rejected a perfectly valid derived table.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 25, 2013 12:32 PM

All replies

  • User697462465 posted

    Hi JKalebP,

    According to your description, I have search some solution:

    1. About The query cannot be represented graphically in the Diagram and Criteria Pane
      1. The SQL statement is incomplete or contains one or more syntax errors.
      2. The SQL statement is valid but is not supported in the graphical panes (for example, a Union query).
      3. The SQL statement is valid but contains syntax specific to the data connection you are using.
      4. SQL statement could not be parsed
        1. There is an error in the syntax of your query. The Query Designer has attempted to locate the source of the error. Review your query syntax and correct the error before running your query.

    I suggest you to copy your T-SQL statement and paste into your DBMS, then execute it, now you can find the error.

    Hope it can help you.

    Best Regards,
    Terry Guo

    Monday, September 16, 2013 8:17 AM
  • User-2057173053 posted

    There was no error.  I am not sure why the derived query would not execute.

    I ended up creating a view with the query posted and then queried the view.  Simple enough to get me through the problem.  Still, I'd like to understand why the Query Editor rejected a perfectly valid derived table.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 25, 2013 12:32 PM