Using Subquery in System.Data.SqlCommand (C #) - Syntax error working in SQL Server Mgmt Studio
I got a request used by a Webservice that contains a sub request. When running the query in SQL Server Management Studio, it works fine. However, using this in a webservice like SqlCommand
I get:
System.Data.SqlClient.SqlException: Incorrect syntax near the "JOIN" keyword. Incorrect syntax near the "AS" keyword.
Inserting additional characters allows me to understand that the error refers to the position immediately after the subquery.
The request looks like this:
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 required to get the latest version of the TD table record. If there are better ways to do this, feel free to let me know. :-)
Are there any limitations with SqlCommand in C # that don't exist in MS SQL, or am I doing something wrong?
I would really appreciate any help!
source to share
I would have thought that using a generic table expression would be more efficient in this case. I'm not 100% sure that this is exactly what you need, as I don't know your database structure and data, but it 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;
source to share