DataSet TableAdapter throwing OVER SQL construct or statement not supported.

Question

I have a t-sql query written with this sample help.

SELECT  t.gName AS 'Product'
, isnull(SUM(CASE WHEN t.Col = 1 THEN t.Quantity END),0) AS '180ml'
, isnull(SUM(CASE WHEN t.Col = 2 THEN t.Quantity END),0) AS '375ml'
, isnull(SUM(CASE WHEN t.Col = 3 THEN t.Quantity END),0) AS '500ml'
, isnull(SUM(CASE WHEN t.Col = 4 THEN t.Quantity END),0) AS '1000ml'
, isnull(SUM(CASE WHEN t.Col = 5 THEN t.Quantity END),0) AS '2000ml'
FROM (
SELECT p.pName
     , p.pCode
     , p.pGroup
     , p.pSize, i.gName, i.gCode
     , sl.Quantity, sl.BillDate
     , DENSE_RANK() OVER(PARTITION BY p.pGroup ORDER BY p.pSize) AS Col 
    FROM 
        ItemGroup AS i INNER JOIN 
                          Products AS p ON i.gCode = p.pGroup INNER JOIN 
                          SalesLog AS sl ON p.pGroup = sl.pGroup
       AND p.pCode = sl.ProductCode   
       ) AS t where t.BillDate=@BillDate and t.pGroup!=15 and t.pGroup!=16
     GROUP BY t.gName
    order by t.gName

Its working in Management Studio Query Editor but not in DataSet throwing error like The OVER SQL construct or statement not supported.

this is query needed for for Report (.rdlc). Please help

thank you

Answer 1

I would put this SQL in to a Stored Procedure or a View. You can then populate your DataSet from there.