SqlStatement Issue

[firoz.raj]

Hi same query is working in the queryAnalyzer but when i put the same code in the code
window of vb it says .

-2147217900 Column 'M.SaleSerial' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.[code]the workable code from query analyzer

Select m.saleserial, 'SAUDI ARABIA' as country,day(M.SaleInternalDate) as sDay,datename(month,M.SaleInternalDate) as sMonth,year(M.SaleInternalDate) as sYear,
a.l1description, M.StoreID, M.SaleInternalDate, d.serial1,  d.serial2,o.price,d.saleserial,du.saleserial,   
SUM(CASE WHEN ReturnFlag =1 THEN -1*QTY ELSE QTY END) QTY , 
SUM((CASE RETURNFLAG   WHEN 0 THEN  Case M.DetailTotal - M.DetailDiscount   when 0 then   ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount )else   ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount -      ((M.totalDiscount - M.DetailDiscount)* ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount)  / (M.DetailTotal - M.DetailDiscount)))end  
ELSE   Case M.DetailTotal - M.DetailDiscount   when 0 then   -((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount )   else   -((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount - ((M.totalDiscount - M.DetailDiscount)* ((D.Price - D.Discount) * D.qty - D.ManualDiscount - 
D.promotionDiscount)  / (M.DetailTotal - M.DetailDiscount) )) end   END) / CASE WHEN ReturnFlag =1 THEN -1*QTY ELSE QTY END)  As Price 
from  PS_SalesMaster As M with (nolock)Inner Join PS_SalesDetail As D with (nolock) ON M.SaleSerial = D.SaleSerial 
inner join ps_stores as a on m.StoreID=a.StoreID inner join dbo.V_Y_ItemsDefaultPrice_Original as o on o.itemid=d.itemid 
left outer join interface.dbo.cnk_dailysales1 AS DU on M.Storeid = du.Storeid and M.SaleSerial = du.SaleSerial 
/* Where M.Storeid Like 'CNK%' AND  m.SaleinternalDate >= getdate()-9  */ 
Where M.Storeid Like 'CNK%' AND (M.saleinternaldate >= '2011-05-18' and m.SaleinternalDate < getdate()-1) --and m.SaleinternalDate > '2011-05-11' 
ANd M.Finished = 1 And D.Error = 0 And M.Deleted = 0 And M.Parked = 0  and 
Du.saleserial is null 
GROUP BY M.SaleInternalDate,m.saleserial, M.StoreID, d.itemid, d.serial1, d.serial2, a.l1description,o.price ,d.saleserial,du.saleserial
order by m.saleserial,m.saleinternaldate

the following code is not working in code window of vb6 why ????

 StrSql = "Select m.saleserial, 'SAUDI ARABIA' as country,day(M.SaleInternalDate) as sDay,datename(month,M.SaleInternalDate) as sMonth,year(M.SaleInternalDate) as sYear, " & _
" a.l1description, M.StoreID, M.SaleInternalDate, d.serial1,  d.serial2,o.price, " & _
" SUM(CASE WHEN ReturnFlag =1 THEN -1*QTY ELSE QTY END) QTY ," & _
" SUM((CASE RETURNFLAG   WHEN 0 THEN  Case M.DetailTotal - M.DetailDiscount   when 0 then   ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount )else   ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount -      ((M.totalDiscount - M.DetailDiscount)* ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount)  / (M.DetailTotal - M.DetailDiscount)))end " & _
" ELSE   Case M.DetailTotal - M.DetailDiscount   when 0 then   -((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount )   else   -((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount - ((M.totalDiscount - M.DetailDiscount)* ((D.Price - D.Discount) * D.qty - D.ManualDiscount -" & _
" D.promotionDiscount)  / (M.DetailTotal - M.DetailDiscount) )) end   END) / CASE WHEN ReturnFlag =1 THEN -1*QTY ELSE QTY END)  As Price " & _
" from  PS_SalesMaster As M with (nolock)Inner Join PS_SalesDetail As D with (nolock) ON M.SaleSerial = D.SaleSerial " & _
" inner join ps_stores as a on m.StoreID=a.StoreID inner join dbo.V_Y_ItemsDefaultPrice_Original as o on o.itemid=d.itemid " & _
" left outer join interface.dbo.cnk_dailysales1 AS DU on M.Storeid = du.Storeid and M.SaleSerial = du.SaleSerial " & _
" /* Where M.Storeid Like 'CNK%' AND  m.SaleinternalDate >= getdate()-9  */ " & _
" Where M.Storeid Like 'CNK%' AND (M.saleinternaldate >= '2011-05-18' and m.SaleinternalDate < getdate()-1) --and m.SaleinternalDate > '2011-05-11'" & _
" ANd M.Finished = 1 And D.Error = 0 And M.Deleted = 0 And M.Parked = 0  and " & _
" Du.saleserial is null " & _
" GROUP BY M.SaleInternalDate,m.saleserial, M.StoreID, d.itemid, d.serial1, d.serial2, a.l1description,o.price " & _
" order by m.saleserial,m.saleinternaldate "