Vistadb SQL Syntax error

Question

vistadb4 not recognize this sql view script. but it is working with sql2005. is there a solution ? thanks.

SELECT DISTINCT ItemCode, SellingPrice
FROM         dbo.SalesPurchases AS a
WHERE     (KeyID =
                          (SELECT     TOP (1) MAX(KeyID) AS KeyID
                            FROM          dbo.SalesPurchases AS b
                            GROUP BY ItemCode, Type
                            HAVING      (ItemCode = a.ItemCode) AND (Type = 'purchase')))
GROUP BY ItemCode, SellingPrice

Answer 1

not sure but take a look at this:

SELECT 
  DISTINCT a.ItemCode, a.SellingPrice
FROM         
  dbo.SalesPurchases AS a
JOIN
  (SELECT
     TOP (1) ItemCode, Type, MAX(KeyID) AS KeyID
   FROM
     dbo.SalesPurchases AS 
   GROUP BY 
     ItemCode, Type
   HAVING
     (ItemCode = a.ItemCode) AND (Type = 'purchase')
   ) b
   on b.KeyID = a.KeyID
GROUP BY 
  a.ItemCode, a.SellingPrice