Cursor doesn't loop properly??

Can someone please tell me why this doesn't work?
I am trying to loop through a table and populate a column called
LocalIDNumber with random numbers... But there must not be any duplicates!
This runs perfectly but only populates the first record and not the other
5000 records in the database...
PLEASE, PLEASE Help...
Thanks
...SQL...
DECLARE RandomCursor CURSOR FOR
SELECT personNo FROM person
OPEN RandomCursor
--Get a variable for the id of the record we are going 2 update
DECLARE @.IDField as int
-- Perform the first fetch.
FETCH NEXT FROM RandomCursor
-- Get the data from the cursor into local variables
INTO @.IDField
-- Check @.@.FETCH_STATUS to see if there are any more ros to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM RandomCursor
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
-- This will create a random number between 10000 and 99999
SET @.Lower = 10000 -- The lowest random number
SET @.Upper = 99999 -- The highest random number
SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
UPDATE
person
SET LocalIDNumber = @.Random
WHERE personNo= @.IDField
END
CLOSE RandomCursor
DEALLOCATE RandomCursorshouldn't this
FETCH NEXT FROM RandomCursor
--Something is missing here!!!!!!!!!!!!!!!!!!!
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
be
FETCH NEXT FROM RandomCursor
INTO @.IDField -- Here we go ;-)
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
Denis the SQL Menace
http://sqlservercode.blogspot.com/
.. wrote:
> Can someone please tell me why this doesn't work?
> I am trying to loop through a table and populate a column called
> LocalIDNumber with random numbers... But there must not be any duplicates!
> This runs perfectly but only populates the first record and not the other
> 5000 records in the database...
> PLEASE, PLEASE Help...
> Thanks
> ...SQL...
> DECLARE RandomCursor CURSOR FOR
> SELECT personNo FROM person
>
> OPEN RandomCursor
> --Get a variable for the id of the record we are going 2 update
> DECLARE @.IDField as int
> -- Perform the first fetch.
> FETCH NEXT FROM RandomCursor
>
> -- Get the data from the cursor into local variables
> INTO @.IDField
> -- Check @.@.FETCH_STATUS to see if there are any more ros to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM RandomCursor
> DECLARE @.Random int;
> DECLARE @.Upper int;
> DECLARE @.Lower int
> -- This will create a random number between 10000 and 99999
> SET @.Lower = 10000 -- The lowest random number
> SET @.Upper = 99999 -- The highest random number
> SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
> UPDATE
> person
> SET LocalIDNumber = @.Random
> WHERE personNo= @.IDField
> END
> CLOSE RandomCursor
> DEALLOCATE RandomCursor|||"Tim::.." <myatix_at_hotmail.com> wrote in message
news:31C2DFA2-38E6-4217-B597-5C0F9F1CD326@.microsoft.com...
> Can someone please tell me why this doesn't work?
> I am trying to loop through a table and populate a column called
> LocalIDNumber with random numbers... But there must not be any duplicates!
> This runs perfectly but only populates the first record and not the other
> 5000 records in the database...
> PLEASE, PLEASE Help...
> Thanks
> ...SQL...
> DECLARE RandomCursor CURSOR FOR
SELECT personNo FROM person
OPEN RandomCursor
--Get a variable for the id of the record we are going 2 update
DECLARE @.IDField as int
-- Perform the first fetch.
-- Get the data from the cursor into local variables
FETCH NEXT FROM RandomCursor INTO @.IDField
-- Check @.@.FETCH_STATUS to see if there are any more ros to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
-- This will create a random number between 10000 and 99999
SET @.Lower = 10000 -- The lowest random number
SET @.Upper = 99999 -- The highest random number
SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
UPDATE
person
SET LocalIDNumber = @.Random
WHERE personNo= @.IDField
FETCH NEXT FROM RandomCursor INTO @.IDField
END
David|||you missed INTO @.IDField clause in your second fetch. Note that you can
get duplicates. To get rid of them, use this:
select
1 id, 1 rand_num
into #t
union all
select 2, 0
union all
select 3, 5
union all
-- rand() generated a duplicate
select 4, 1
union all
select 5, 17
union all
select 6, 3
union all
select 7, 13
union all
select 8, 9
union all
select 9, 4
union all
select 10, 21
go
select * from #t
-- remove duplicates
update #t set rand_num = rand_num + (select count(*) from #t t1
where t1.rand_num < #t.rand_num or (t1.rand_num = #t.rand_num and
t1.id < #t.id))
go
select * from #t

Currency Conversion

I am using the cube intelligence wizard to add currency conversions. I have defined everything properly. At a certain moment, a query is launched against my Oracle backend. This is the query:

SELECT "Reporting Currency".*

FROM

(

SELECT "CCDM_IL"."CCDM_IL_CRRN_T"."CURRENCY_CODE","CCDM_IL"."CCDM_IL_CRRN_T"."CURRENCY_NAME"

FROM "CCDM_IL"."CCDM_IL_CRRN_T"

WHERE [CURRENCY_NAME] IN ('Euro') UNION

SELECT

DISTINCT

'Local' "Local",'Local' "Local 2"

FROM "CCDM_IL"."CCDM_IL_CRRN_T"

)

"Reporting Currency"

Now, this query returns the error "ORA-00936: Missing Expression". This error is caused by the square brackets around currency_name in the where clause. Once I remove these the query runs fine.

How can I make this query work?

Thanks

Joos

By the look of it this is coming from a named query in the DSV. Check if the wizard has actually inserted the square brackets into the named query in the DSV. If so, you should be able to edit the definition to remove the square brackets.|||

The query is generated by the BI wizard itself. In my DSV, I do not have a named query for the currency dimension. It is a real table...

The currency dimension has one attribute for which the key is set to CCDM_IL_CRRN_T.CURRENCY_CODE and the name is set to CCDM_IL_CRRN_T.CURRENCY_NAME.

|||Can you double check? Right click on the object in the DSV and tell me if the third menu item you see says either "Edit Named Query..." or "Replace Table with...". I would not normally ask, but the reason I am is becuase the BI wizard actually generates objects in your DSV and cubes, but it does not actually send any queries to the relational source.|||

For the table CCDM_IL_CRRN_T, it definitely says "Replace Table".

Joos

|||

Have you been able to isolate if this error is coming from a dimension or from a partition? (by processing objects associated with currencies one at a time)

Try going into SSMS, right clicking on the database and generating an alter script. This will generate a heap of xmla, search through this for the table name in square brackets or some other identifiable portion of this problem query. You can then either run the alter script and then import the database back into BIDS or hopefully this will help identify where exactly the problem query is coming from.

|||

In the script, the square brackets do not occur. Extracts where currency_name occurs:

<NameColumn>

<DataType>WChar</DataType>

<DataSize>255</DataSize>

<Source xsi:type="ColumnBinding">

<TableID>CCDM_CCDM_IL_CRRN_T</TableID>

<ColumnID>CURRENCY_NAME</ColumnID>

</Source>

</NameColumn>

<xs:element name="CURRENCY_NAME" msprop:FriendlyName="CURRENCY_NAME" mspropbColumnName="CURRENCY_NAME" minOccurs="0">

<xsimpleType>

<xs:restriction base="xstring">

<xs:maxLength value="255" />

</xs:restriction>

</xsimpleType>

</xs:element>

Joos