It is necessary to execute 'native SQL' query (Returns value of type Int32):
Code:
WITH Family (GroupID, ParentID) AS
(
SELECT DS_Group.GroupID, S_Group.ParentID
FROM DS_Group
WHERE DS_Group.GroupID = (SELECT GroupID FROM DS_Product
WHERE ProductID = @ID)
UNION ALL
SELECT DS_Group.GroupID, DS_Group.ParentID
FROM DS_Group INNER JOIN Family ON DS_Group.GroupID = Family.ParentID
)
INSERT INTO DS_ProductUpdate (ProductUpdateID, ProductID, EcrGroupID)
SELECT (COALESCE((SELECT MAX(ProductUpdateID) FROM DS_ProductUpdate), 0)+(ROW_NUMBER() OVER (order by ProductID))),
Updates.ProductID,
Updates.EcrGroupID
FROM (SELECT DISTINCT @ID AS ProductID,
DS_EcrGroupData.EcrGroupID
FROM Family INNER JOIN
DS_EcrGroupData ON Family.GroupID = DS_EcrGroupData.GroupID
WHERE (NOT EXISTS (SELECT *
FROM DS_ProductUpdate
WHERE @ID = DS_ProductUpdate.ProductID AND
DS_EcrGroupData.EcrGroupID = DS_ProductUpdate.EcrGroupID))
UNION
SELECT @ID AS ProductID,
DS_EcrData.EcrGroupID
FROM DS_EcrData
WHERE DS_EcrData.ProductID = @ID AND (NOT EXISTS (SELECT *
FROM DS_ProductUpdate
WHERE DS_EcrData.ProductID = DS_ProductUpdate.ProductID AND
DS_EcrData.EcrGroupID = DS_ProductUpdate.EcrGroupID))) AS Updates
I describe approximately so:
Code:
ISqlQuery sqlQuery = ISession.CreateSQLQuery(...);
object value = sqlQuery.UniqueResult();
Quote:
I receive Exception:
"Return types of SQL query were not specified [WITH Family (GroupID, ParentID) AS\r\n( \r\n SELECT DS_Group.GroupID, \r\n DS_Group.ParentID \r\n FROM DS_Group\r\n WHERE DS_Group.GroupID = (SELECT GroupID\r\n FROM DS_Product\r\n WHERE ProductID = 1)\r\n UNION ALL\r\n SELECT DS_Group.GroupID,\r\n DS_Group.ParentID\r\n FROM DS_Group INNER JOIN\r\n Family ON DS_Group.GroupID = Family.ParentID \r\n)\r\nINSERT INTO DS_ProductUpdate\r\n (ProductUpdateID, ProductID, EcrGroupID)\r\nSELECT (COALESCE((SELECT MAX(ProductUpdateID) FROM DS_ProductUpdate), 0)+(ROW_NUMBER() OVER (order by ProductID))),\r\n Updates.ProductID,\r\n Updates.EcrGroupID\r\nFROM (SELECT DISTINCT 1 AS ProductID,\r\n DS_EcrGroupData.EcrGroupID\r\n FROM Family INNER JOIN\r\n DS_EcrGroupData ON Family.GroupID = DS_EcrGroupData.GroupID\r\n WHERE (NOT EXISTS (SELECT *\r\n FROM DS_ProductUpdate\r\n WHERE 1 = DS_ProductUpdate.ProductID AND\r\n DS_EcrGroupData.EcrGroupID = DS_ProductUpdate.EcrGroupID))\r\n UNION \r\n SELECT 1 AS ProductID,\r\n DS_EcrData.EcrGroupID\r\n FROM DS_EcrData\r\n WHERE DS_EcrData.ProductID = 1 AND (NOT EXISTS (SELECT *\r\n FROM DS_ProductUpdate\r\n WHERE DS_EcrData.ProductID = DS_ProductUpdate.ProductID AND\r\n DS_EcrData.EcrGroupID = DS_ProductUpdate.EcrGroupID))) AS Updates]"
Hibernate version:1.2.0.400[/quote]
Login
FAQ
Search
