Multiple while fetch cursor code

I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or?

What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string.

For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @.instrumentlinje remains empty.

If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument" statement doesn't execute.

DELETE FROM ALL_tbl_instrumentkoder

DECLARE @.medlem int
DECLARE @.instrument varchar(10)
DECLARE @.instrumentlinje varchar(150)

DECLARE medlemmer_cursor CURSOR FOR
SELECT medlemsnummer
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

DECLARE instrumenter_cursor CURSOR FOR
SELECT [MCPS Kode]
FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

OPEN medlemmer_cursor

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

WHILE @.@.FETCH_STATUS = 0
BEGIN

OPEN instrumenter_cursor
FETCH NEXT FROM instrumenter_cursor INTO @.instrument

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument
FETCH NEXT FROM instrumenter_cursor INTO @.instrument
END

CLOSE instrumenter_cursor

INSERT INTO ALL_tbl_instrumentkoder VALUES(@.medlem, @.instrumentlinje)

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

END

CLOSE medlemmer_cursor
DEALLOCATE medlemmer_cursor
DEALLOCATE instrumenter_cursorWell, I suspect the problem is related to referencing a variable in your cursor definition, but you shouldn't be using a cursor anyway.

Here is a simpler (non-cursor) method:

First, create this function:
create function dbo.instrumentlinje(@.medlem int)
returns varchar(4000) as
begin
declare @.instrumentlinje
select @.instrumentlinje = isnull(@.instrumentlinje + ' ', '') + MCPS Kode
from Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

return @.instrumentlinje
end

Then, run this code:
insert into ALL_tbl_instrumentkoder
(medlem,
instrumentlinje)
select medlemsnummer,
dbo.instrumentlinje(medlem)
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

Warning! Not tested for syntax errors, and you may need to edit object ownership.|||Basically the same way I did it in Access.. just a greenhorn when it comes to SQL-server.

Thanks man :-)|||TSQL is similar to Access SQL, though there are a few syntactical differences. The concept of avoiding cursors and loops in favor of set-based operations is the same, though.

Multiple Time Dimensions in a cube

I have a fact table that includes multiple date fields OrderEntryDateId, WantDateId, ShippedDateId, InvoicedDateId.

Can I join time to more than one field or do I need to create 4 different cubes?

Thanks,

Chris

Hello. Provided that you have the same keys in the time dimensions table as for each date in your fact table, you can use this single time dimension for all time keys in the fact table.

You design the relations(primary key and foreign key) in the data source view, that you create before the cube.

When you build your cube i BI-Dev Studio, with the wizard, it will take care of creating different cube time dimensions for each fact table date field.

So one time dimension table will work in SSAS2005.

HTH

Thomas Ivarsson

|||

I am using SSAS2000. Does this mean I need to upgrade? I don't see a way to do this in 2000.

|||

Hello. In SSAS2000 you will have to build views in order to clone your original time dimension. Each time view will have to be joined to the fact table.

Upgrade or not? This depends. Some day you will have to.

Role playing dimensions(that i have decribed to you) is not the most important feature in SSA2005.

Regards

Thomas Ivarsson

|||

Are you saying to make a view in SQL Server, or is this something that is done in SSAS 2000.

Thanks

|||

Hello again.

With SSAS2000 you will have to build the view in SQL Server.

Regards

Thomas Ivarsson

Multiple Time Dimensions in a cube

I have a fact table that includes multiple date fields OrderEntryDateId, WantDateId, ShippedDateId, InvoicedDateId.

Can I join time to more than one field or do I need to create 4 different cubes?

Thanks,

Chris

Hello. Provided that you have the same keys in the time dimensions table as for each date in your fact table, you can use this single time dimension for all time keys in the fact table.

You design the relations(primary key and foreign key) in the data source view, that you create before the cube.

When you build your cube i BI-Dev Studio, with the wizard, it will take care of creating different cube time dimensions for each fact table date field.

So one time dimension table will work in SSAS2005.

HTH

Thomas Ivarsson

|||

I am using SSAS2000. Does this mean I need to upgrade? I don't see a way to do this in 2000.

|||

Hello. In SSAS2000 you will have to build views in order to clone your original time dimension. Each time view will have to be joined to the fact table.

Upgrade or not? This depends. Some day you will have to.

Role playing dimensions(that i have decribed to you) is not the most important feature in SSA2005.

Regards

Thomas Ivarsson

|||

Are you saying to make a view in SQL Server, or is this something that is done in SSAS 2000.

Thanks

|||

Hello again.

With SSAS2000 you will have to build the view in SQL Server.

Regards

Thomas Ivarsson