0

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
Hasitha D
  • 19
  • 2
  • What error do you get, exactly? – Diego Aug 04 '12 at 14:52
  • not a VistaDB guy, but seems odd to do a SELECT DISTINCT on the same/only two columns that you have in the GROUP BY. Typically GROUP BY means there's another aggregate column in the projection - like a average or count. Haven't played with it, but also seems like your subselect could be unnecessary? – Jim O'Neil Aug 04 '12 at 14:54
  • thanks :) these are the errors Error 509 (Provider v. 4.2.18.1): Invalid or incomplete statement: SELECT Line #: 1; Column #: 1 ________________________ ADDITIONAL INFORMATION: Error 509 (Provider v. 4.2.18.1): Invalid or incomplete statement: SELECT Line #: 6; Column #: 29 Error 571 (Provider v. 4.2.18.1): HAVING can have only expressions which is in SELECT result or in GROUP BY Line #: 7; Column #: 65 – Hasitha D Aug 04 '12 at 15:01
  • Send this into Gibraltar Support and we can work with you to find the right query to get the result you want. – Kendall Miller Sep 13 '12 at 15:16

1 Answers1

0

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
heximal
  • 10,327
  • 5
  • 46
  • 69
  • thanks dude. its also not working :( I'm getting an error Error 509 (Provider v. 4.2.18.1): Invalid or incomplete statement: SELECT Line #: 1; Column #: 1 ADDITIONAL INFORMATION: Error 509 (Provider v. 4.2.18.1): Invalid or incomplete statement: SELECT Line #: 6; Column #: 4 Error 569 (Provider v. 4.2.18.1): Expression is not present in GROUP BY clause: ItemCode Line #: 7; Column #: 14 – Hasitha D Aug 04 '12 at 15:13
  • forget about vistadb syntex :) Is there any mssql alternatives to this script? – Hasitha D Aug 04 '12 at 15:30
  • wait a minute... you use both DISTINCT in SELECT clause and GROUP BY with the same fields list. It's redundant isn't it? try remove DISTINCT from top-level SELECT – heximal Aug 04 '12 at 21:48