cursor usage

hi guys

i have a table that contains a tremendous amount of row. i have written a stored procedure that takes a summary of that information and updates it's master table as well as another table. the problem is it takes very long to do. is what i am doing correct or is there a better way. here is the source

CREATE PROCEDURE update_cvrbatches
AS

declare @.code varchar(25),@.type varchar(3),@.batchno varchar(10),@.batchqty Float,@.issued float,@.returned float,@.transfered float,@.itemcount float,@.totestcost float,@.totactcost float,@.reserved float,@.warehouse varchar(3)

Update cvrwarehouse set BoughtQty =0, IssuedQty = 0, ReservedQty =0 ,Returned = 0 ,Transfered = 0

DECLARE getbatches CURSOR
for
select Code,Type,BatchNo,sum(BoughtQty) as BoughtQty,sum(IssuedQty) as IssuedQty,sum(ReservedQty) as ReservedQty,sum(ReturnedQty) as ReturnedQty,sum(TransferQty) as TransferQty,count(Barcode) as ItemCount,
sum(EstCost) as EstCost,sum(ActCost) as ActCost,Warehouse
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
OPEN getbatches

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost ,@.warehouse
WHILE @.@.FETCH_STATUS = 0
BEGIN
--doen iets hier

update cvrbatchctrl set BatchQty = @.batchqty, Issued = @.issued, Reserved = @.reserved ,Returned = @.returned ,Transfered = @.transfered, ItemCount = @.itemcount,TotalEstCost = @.totestcost,TotalActCost = @.totactcost
where Code = @.code and Type = @.type and Warehouse = @.warehouse and BatchNo = @.batchno
update cvrwarehouse set BoughtQty =BoughtQty + @.batchqty, IssuedQty = IssuedQty + @.issued, ReservedQty = ReservedQty + @.reserved ,Returned = Returned + @.returned ,Transfered = Transfered + @.transfered
where Code = @.code and Type = @.type and Warehouse = @.warehouse

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost,@.warehouse

END
CLOSE getbatches
DEALLOCATE getbatches

is there a better way?

Hi,

better use setbased solutions rather than cursors, one example would be (*untested*) the below one: (put in a more human readble format)

update cvrbatchctrl
set BatchQty = SUbQuery.BoughtQty,
Issued = SUbQuery.IssuedQty,
Reserved = SUbQuery.ReservedQty,
Returned = SUbQuery.ReservedQty,
Transfered = SUbQuery.TransferQty,
ItemCount = SUbQuery.ItemCount,
TotalEstCost = SUbQuery.EstCost,
TotalActCost = SUbQuery.ActCost
FROM cvrbatchctrl cvr
INNER JOIN
(
select Code,
Type,
BatchNo,
Warehouse,
sum(BoughtQty) as BoughtQty,
sum(IssuedQty) as IssuedQty,
sum(ReservedQty) as ReservedQty,
sum(ReturnedQty) as ReturnedQty,
sum(TransferQty) as TransferQty,
count(Barcode) as ItemCount,
sum(EstCost) as EstCost,
sum(ActCost) as ActCost
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
) SUbQuery
ON
cvr.Code = SUbQuery.Code and
cvr.Type = SUbQuery.Type and
cvr.Warehouse = SUbQuery.Warehouse and
cvr.BatchNo = SUbQuery.batchno

--Second one, taking the value from the first update (make surethat your condition is complete in the below script)

update cvrwarehouse
set BoughtQty = cvrhouse.BoughtQty + @.batchqty,
IssuedQty = cvrhouse.IssuedQty + @.issued,
ReservedQty = cvrhouse.ReservedQty + @.reserved ,
Returned = cvrhouse.Returned + @.returned ,
Transfered = cvrhouse.Transfered + @.transfered
FROM cvrwarehouse cvrhouse
INNER JOIN cvrbatchctrl cvrbatch
ON
cvrhouse.Code = cvrbatchctrl.Code AND
cvrhouse.Type = cvrbatchctrl.Type
cvrhouse.Warehouse = cvrbatchctrl.Warehouse

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you.

haven't tried it yet but it sure makes for an interesting solution.

let you know as soon as i do.

again thanks

|||

i just tested it

works really well and the speed is a lot better

thanks again

Currency Format Issue

Please Help!

I am stuggeling with a amount measure in my cube. The format of the measure is set to currency but i always shows the dollar($) sign no matter the regional settings or the language setting of the report.

How can i fix this problem as i do not know where to start looking. Everyone keeps saying the currency will change according to the language setting of the report or language setting under properties of sql 2005 analysis services.

Thanks in advance,

Jacques kruger

I have struggled with the same problem. This solution work if you have the same currency in a column: (### ### ### ###.##) or (### ### ### ###.##EUR) with a currency. Note the blanks as a thousand separator and the dot as decimal).

HTH

Thomas Ivarsson

|||

I have found this link to more information in Mosha's Blog: http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

Regards

Thomas Ivarsson

Currency Column in Report Problem

I have a column in a report which displays an amount of currency. The currency is stored in sqlserver databse as 'Money' data type.

My problem is when I run the report it rounds off the value and does not disply to any decimal places. I want it to display the currency as 2 decimal places as this is how it is input and stored in the database.

anyone know how to do this?

macca

You could format the textbox like this:

http://msdn2.microsoft.com/en-us/library/ms251684(VS.80).aspx

Find more info here:

http://msdn2.microsoft.com/en-us/library/ms252073(VS.80).aspx

Currency being rounded up in Report

I am displaying an amount of money to two decimal places in a field in a report.

If I run the stored procedure in sql server the amounts are displayed to two decimal places like so €31.75.

But when I run the report the amount is returned as so €32.00, it is being rounded up to the nearest euro.

Anyone any ideas how to solve this?

macca

Figured this out myself.

Currency - not $

Hi

I have a cube with a sales amount measure. When I choose the format string to currency i get a $ infront of it when i browse the cube.

What should I do to get a ￡ infront or maybe SEK in the back (SEK = Swedish Kronor)

Thanks for answers

//Patrik

Try changing the format string to #,#.00 kr.

/Per

|||

Well, that worked.

But it seems to be a work around, and not the final solution. You must be able to select what type of currency the currency format code is.

/Patrik

|||

Yes, I agree. Should be possible - calculated members work fine.

Maybe Frederik knows - can ask him during the coffe break.

/Per

|||Change the language setting in the properties section for the cube in Business Intelligence Development Studio. Then you will get the correct regional display of the currencies.|||

Thanks man...that did it.

I think thats the only Language setting that I didn't change :P

//Patrik

Currency

hi,
i would like to write a sql function which takes 3 parameters.
1. Source currency type
2. destination currency type
3. Amount to be converted
I need the dynamic rates.
Does anybody hav any idea about how to go with this.
Thanks in advance,
Vinu
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1Are you using SQL Server 2005 ? Therefore you could use Webservices to
call an currency service synchronously or async. by storing the data
in a currency table. That sure is possible with SQL 2000 but it MORE
easier with SQL 2005.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--

Currency

hi,
i would like to write a sql function which takes 3 parameters.
1. Source currency type
2. destination currency type
3. Amount to be converted
I need the dynamic rates.
Does anybody hav any idea about how to go with this.
Thanks in advance,
Vinu
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1Are you using SQL Server 2005 ? Therefore you could use Webservices to
call an currency service synchronously or async. by storing the data
in a currency table. That sure is possible with SQL 2000 but it MORE
easier with SQL 2005.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

cube measure only displays one total

I did something to my cube where it is only displaying a much smaller value for

the sales amount measure than it should.

I have a project with two dimensions and one measure.

After deploying the cube i browsed it and pulled the sales amount into the

details field area. normally i should end up with some 400 + million in dollars instead i get 90+ thousand. It seems as if the cube is not processing fully or the browser tab is somehow being filtered. I checked and there is nothing in the subcube or any other field.

Can someone direct me on how best to debug this?

thanks

I found the problem and thought I would share what I did.

I'm surprised it works this way maybe it is a bug. Perhaps the pros here could add some info to this.

here is what I did.

I have a field called "Rate" which is multiplied by the "Qty" to come up the "Sales Amount" measure. This is done in the DSV as a "Named Calculation" in the measures table.

This same table is also used as a dimension table. In here i considered the "Rate" field to be non-Aggregatable or (IsAggregatable = false). Since i would never add up the rates for a total.

Apparently SSAS does not like this and hence things got really messed up. Switching it back to (IsAggregatable = true) fixed everything.

Hopefully my hair will grow back soon.

Cube History

I am trying to build a cube to monitor the sales performance. My measure is the sale amount. My dimensions include 1) Time 2) Sale hierarchy (salesman, sales office, region), and 3) Product Line. The problem I have is that when the salesman move from one office to another, the sales occured before this movement should continue to be credited to the old office. Only sales happen after the movement should be credited to the new office.
How should I implement the cube to achieve this effectively? We have more that 40,000 sales, about 500 offices, in the company to monitor. So I don't think keeping a copy of the sales hierarchy whenever there is a change is feasible.

Any inputs will be highly appreciated!

Thanks.You will need to Consider creating another dim for salesman.

you apear to need to collect when an employee was at a particular site for a particular sale so you will have to add a location value to each ORDER and not rely on the salesman's location. it has to be tied to the sale itself so the location attribute should be derived for the order

if this is a seldom problem you may want to make your cube holap (part relational and part cube)instead of pure olap and then you can use the drill down feature for a particular sale or set of sales to find the distinction between sites.|||Thank you very much for the information. I can see how that will fix the problem. However, I am not sure how to make the drilldown work using holap as you have suggested. Could you please elaborate it a bit about your last paragraph? Thanks. Sorry, I am new in the DWH field...|||check out those topics in BOL for analysis services
i will try to reply later

CSV Field Stored Procedure

Hi All,
I would like to submit a large amount of CSV text into a stored procedure. At present I'm using a varchar(8000) parameter and then converting this into a temporary table using something like the sql below. My question is does anybody have any suggestions on how to get round the 8000 limit without doing round trips?
thanks
Steve
declare @.separator char(3)
set @.separator = '%' + @.delimeter + '%'
declare @.separator_position int
declare @.array_value varchar(1000)
set @.input = @.input + ','
while patindex(@.separator , @.input) <> 0
begin
select @.separator_position = patindex(@.separator , @.input)
select @.array_value = left(@.input, @.separator_position - 1)
Insert @.IntTable
Values (@.array_value)
select @.input = stuff(@.input, 1, @.separator_position, '')
end
Multiple parameters?
--=20
Keith
"Steve" <steve@.nospam.com> wrote in message =
news:empTbYQVEHA.1164@.tk2msftngp13.phx.gbl...
Hi All,
I would like to submit a large amount of CSV text into a =
stored procedure. At present I'm using a varchar(8000) parameter and =
then converting this into a temporary table using something like the sql =
below. My question is does anybody have any suggestions on how to get =
round the 8000 limit without doing round trips?
thanks
Steve
declare @.separator char(3)
set @.separator =3D '%' + @.delimeter + '%'
declare @.separator_position int=20
declare @.array_value varchar(1000)=20
=20
set @.input =3D @.input + ','
=20
while patindex(@.separator , @.input) <> 0=20
begin
=20
select @.separator_position =3D patindex(@.separator , @.input)
select @.array_value =3D left(@.input, @.separator_position - 1)
=20
Insert @.IntTable
Values (@.array_value)
select @.input =3D stuff(@.input, 1, @.separator_position, '')
end
|||Steve,
You can pass the CSV text as a parameter of type text or ntext, and use PATINDEX and SUBSTRING to parse it. An alternative, that if possible will make parsing the long parameter much easier is to pass it as non-separated text with a fixed-length field width instead of comma-separated. You can find an example of this technique at http://www.sommarskog.se/arrays-in-s...xstring_multi, and the entire article http://www.sommarskog.se/arrays-in-sql.html may also be useful.
Steve Kass
Drew University
"Steve" <steve@.nospam.com> wrote in message news:empTbYQVEHA.1164@.tk2msftngp13.phx.gbl...
Hi All,
I would like to submit a large amount of CSV text into a stored procedure. At present I'm using a varchar(8000) parameter and then converting this into a temporary table using something like the sql below. My question is does anybody have any suggestions on how to get round the 8000 limit without doing round trips?
thanks
Steve
declare @.separator char(3)
set @.separator = '%' + @.delimeter + '%'
declare @.separator_position int
declare @.array_value varchar(1000)
set @.input = @.input + ','
while patindex(@.separator , @.input) <> 0
begin
select @.separator_position = patindex(@.separator , @.input)
select @.array_value = left(@.input, @.separator_position - 1)
Insert @.IntTable
Values (@.array_value)
select @.input = stuff(@.input, 1, @.separator_position, '')
end
|||Many thanks for your help guys.
Regards
Steve
"Steve" <steve@.nospam.com> wrote in message news:empTbYQVEHA.1164@.tk2msftngp13.phx.gbl...
Hi All,
I would like to submit a large amount of CSV text into a stored procedure. At present I'm using a varchar(8000) parameter and then converting this into a temporary table using something like the sql below. My question is does anybody have any suggestions on how to get round the 8000 limit without doing round trips?
thanks
Steve
declare @.separator char(3)
set @.separator = '%' + @.delimeter + '%'
declare @.separator_position int
declare @.array_value varchar(1000)
set @.input = @.input + ','
while patindex(@.separator , @.input) <> 0
begin
select @.separator_position = patindex(@.separator , @.input)
select @.array_value = left(@.input, @.separator_position - 1)
Insert @.IntTable
Values (@.array_value)
select @.input = stuff(@.input, 1, @.separator_position, '')
end