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 "

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[TysonLPrice]

The one you say works has these in the select after o.price:

,d.saleserial,du.saleserial,

And the VB version doesn't. I stopped looking there.

[firoz.raj]

But why the following code is working in Qe .but it does not work in VbCode Window .

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

but same code is not working in vb code window .here it is .

Set objexcel = New Excel.Application          'Process of Exporting rssales into the ExcelSheet
                    Set wbook = objexcel.Workbooks.Add
                    Set osheet = wbook.Worksheets(1)
                     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 "

[firoz.raj]

can anyone tell me ?how should i copy them table data from one table to
another table ?.i have tried but failed .can anyone tell me ?

select * from cnk_stores into feroz.cnkstores1

[GaryMazzone]

Select * into tableName From TableName1

[TysonLPrice]

Select * into tableName From TableName1

That's assuming you want it into a new table. Is that the case?

[GaryMazzone]

That was not specified. If it is to an existing table it would be:

Insert INTO tableName Select * From tableName1

[TysonLPrice]

That was not specified. If it is to an existing table it would be:

Insert INTO tableName Select * From tableName1

I was asking the OP...I know you knew that

[firoz.raj]

ok sometimes we use lock keyword.with select statement .so can you tell me ?what does the meaning of lock statement in
a select statement ?.actually what happens when i use lock keyword.suppose
in the following case .

select * from cnk_dailysales1  with (nolock)

[TysonLPrice]

ok sometimes we use lock keyword.with select statement .so can you tell me ?what does the meaning of lock statement in
a select statement ?.actually what happens when i use lock keyword.suppose
in the following case .

select * from cnk_dailysales1  with (nolock)

From Google:

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data never made it into the database. So, while it can prevent reads being deadlocked by other operations.

The risk is uncertain results or dirty reads as mentioned. We tend to use it everywhere but that call was made on how data is maintained.

[firoz.raj]

can anyone tell me ?how should i delete specific column name using delete statement ?When i tried to delete one of the column .
it Says Invalid object name 'Emp_Designation'.

delete Emp_Designation from Employees

[si_the_geek]

That is not related to the question at the top of this thread, so does not belong in this thread - so create a new thread for it.

[firoz.raj]

hi friends,i have one table jaj_stores interface database on in infinity-db server .i want to copy the table from infinity-db into fr-infinity .but when i write a query it says .
Line 1: Incorrect syntax near '-'.so kindly let me know the idea.

select * into SixStores from infinity-db.jaj_stores1

[GaryMazzone]

infinity-db needs to be in brackets like this:

[infinity-db]

[firoz.raj]

yep,
Working nice .i would like to rate this specific post.

select * into Six_dailysales from [infinity-db].interface.dbo.jaj_DAILYSALES1

[GaryMazzone]

Thanks You should probably mark this thread resolved and use a new one for other issues now.

[firoz.raj]

why the following statement is not working in Sql server Query Analyser?

delete top(64) from  six_stores

[si_the_geek]

That is not directly related to the question you asked in the first post, so does not belong in this thread - create a new thread for it.

As your initial question seems to have been answered to your satisfaction, yet you keep on asking unrelated questions despite repeated prompting, I am closing this thread.