3

I got a query used by a Webservice which contains a subquery. When executing the query in SQL Server Management Studio, it works fine. However, using this in the webservice as a SqlCommand, I get:

System.Data.SqlClient.SqlException: Incorrect Syntax near the keyword 'JOIN'. Incorrect Syntax near the keyword 'AS'.

Inserting extra characters let me figure out that the error refers to the position right after the subquery.

The query is as following:

SELECT H.H_ID AS ID, H.Name, TD.TDValue, A.A_ID, A_C.Value
FROM 
    H CROSS JOIN 
        A JOIN
            (SELECT TD.A_ID, TD.H_ID, MAX(cast(TD.Version AS bigint)) AS Version FROM TD GROUP BY TD.A_ID, TD.H_ID) AS TData ON TData.H_ID = H.H_ID AND TData.A_ID = A.A_ID LEFT JOIN
                TD2 ON TD2.H_ID = H.H_ID AND TD2.A_ID = A.A_ID AND TD2.Version = TData.Version LEFT JOIN 
                    A_C ON A_C.A_ID = A.A_ID AND cast(A_C.R AS CHAR) = cast(TD.TDValue AS CHAR)
WHERE   (H.Info = 1);

The Subquery is needed to get the last Version of an entry of the table TD. If there are better ways to do that, feel free to let me know as well. :-)

Are there any limitations with the SqlCommand in C# that do not exist in MS SQL, or am I doing something wrong?

I would appreciate any help so much!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2083834
  • 73
  • 1
  • 7
  • Why not use a stored procedure instead? – dead_ant Feb 18 '13 at 16:10
  • I am so sorry - another two hours of research made me check my code: I put it together in a String and left out some blanks at the end (or beginning) of a line... so annoying! – user2083834 Feb 19 '13 at 08:44

1 Answers1

0

I would think that using a common table expression would be more efficient in this case. I'm not 100% that this is exactly what you need as I don't know your database structure and data needs, but this should help.

;with
cte_Tdata AS
(
    SELECT 
         TD.A_ID
        ,TD.H_ID
        ,MAX(cast(TD.Version AS bigint)) AS Version 
    FROM 
        TD 
    GROUP BY 
         TD.A_ID
        ,TD.H_ID
),cte_TD AS
(
    SELECT
        TD.A_ID
        ,TD.H_ID
        ,TD.TDValue
    FROM
        TD
)
SELECT H.H_ID AS ID, H.Name, cte_TD.TDValue, A.A_ID, A_C.Value
FROM 
    H CROSS JOIN 
        A JOIN cte_Tdata ON cte_Tdata.H_ID = H.H_ID 
                    AND cte_Tdata.A_ID = A.A_ID 
            INNER JOIN cte_TD ON cte_Tdata.H_ID = cte_TD.H_ID 
                             AND cte_Tdata.A_ID = cte_TD.A_ID
            LEFT JOIN A_C ON A_C.A_ID = A.A_ID 
                         AND cast(A_C.R AS CHAR) = cast(cte_TD.TDValue AS CHAR)
        LEFT JOIN TD2 ON TD2.H_ID = H.H_ID 
                     AND TD2.A_ID = A.A_ID 
                     AND TD2.Version = cte_Tdata.Version 

WHERE 
    H.Info = 1;
Charles380
  • 1,269
  • 8
  • 19