SqlStatement Issue

Printable View

May 23rd, 2011, 01:03 PM
firoz.raj

SqlStatement Issue

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

Code:

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 ????

Code:

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 "
May 23rd, 2011, 01:20 PM
si_the_geek

Re: SqlStatement Issue

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)
May 23rd, 2011, 01:47 PM
TysonLPrice

Re: SqlStatement Issue

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.
May 24th, 2011, 03:53 AM
firoz.raj

Re: SqlStatement Issue

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

Code:

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 .

Code:

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 "

:(
May 25th, 2011, 10:15 AM
firoz.raj

Re: SqlStatement Issue

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 ?

Code:

select * from cnk_stores into feroz.cnkstores1

:(
May 25th, 2011, 10:18 AM
GaryMazzone

Re: SqlStatement Issue

Select * into tableName From TableName1
May 25th, 2011, 10:38 AM
TysonLPrice

Re: SqlStatement Issue

Quote:

Originally Posted by GaryMazzone

Select * into tableName From TableName1

That's assuming you want it into a new table. Is that the case?
May 25th, 2011, 11:02 AM
GaryMazzone

Re: SqlStatement Issue

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

Insert INTO tableName Select * From tableName1
May 25th, 2011, 11:03 AM
TysonLPrice

Re: SqlStatement Issue

Quote:

Originally Posted by GaryMazzone

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 :D
Jun 1st, 2011, 07:26 AM
firoz.raj

Re: SqlStatement Issue

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 .

Code:

select * from cnk_dailysales1 with (nolock)

:(
Jun 1st, 2011, 07:39 AM
TysonLPrice

Re: SqlStatement Issue

Quote:

Originally Posted by 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 .

Code:

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.
Jun 19th, 2011, 04:05 AM
firoz.raj

Re: SqlStatement Issue

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'.:(

Code:

delete Emp_Designation from Employees
Jun 19th, 2011, 07:36 AM
si_the_geek

Re: SqlStatement Issue

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.
Sep 1st, 2011, 09:22 AM
firoz.raj

Re: SqlStatement Issue

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.

Code:

select * into SixStores from infinity-db.jaj_stores1

:(
Sep 1st, 2011, 10:09 AM
GaryMazzone

Re: SqlStatement Issue

infinity-db needs to be in brackets like this:

[infinity-db]
Sep 1st, 2011, 11:03 AM
firoz.raj

Re: SqlStatement Issue

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

Code:

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

:(
Sep 1st, 2011, 11:25 AM
GaryMazzone

Re: SqlStatement Issue

Thanks You should probably mark this thread resolved and use a new one for other issues now.
Sep 5th, 2011, 11:20 AM
firoz.raj

Re: SqlStatement Issue

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

Code:

delete top(64) from six_stores
Sep 5th, 2011, 11:34 AM
si_the_geek

Re: SqlStatement Issue

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.