March 4, 2013 at 8:25 am
Eugene Elutin (3/4/2013)
if you want unique values then use 36-base encoding function from here:http://www.sqlservercentral.com/Forums/Topic1267659-391-2.aspx
That one will generate unique values and it will "automatically" determine the size required based on how many numbers to be generated...
Thank you Eugene. But i can generate unique values, but i am not able to skip if i have to generate more than 36. If you look at my previous question, you can abe to understand what i am looking for....
exec Sp_Coded 'Test','BKBOOK','BMTYPE',5025,99999,10,'311','1','','','',''
exec Sp_Coded 'Test','BKBOOK','BMINCR',5026,99999,10,'370','1','','','',''
exec Sp_Coded 'Test','BKPOSS','PPCODE',5011,99999,10,'310','1','','','',''
exec Sp_Coded 'Test','BKPOSS','PPLOCA',3094,999,10,'2085','1','','','',''
exec Sp_Coded 'Test','BKPOSS','PPRESN',3102,999,10,'2087','1','','','',''
you see, we pass multiple exec statement, and if one get stuck because of not able to generate more than 36 values(depends on how many rows i have to insert) , it will not move to the next.
March 4, 2013 at 8:29 am
prakashr.r7 (3/4/2013)
ChrisM@Work (3/4/2013)
prakashr.r7 (3/4/2013)
...Yes...i am restricted, Chris 🙁 . This is how my functionality works. So i have to stick with it. no other option. Have to generate unique random values.
Checking to see if all 36 charactes have been consumed in a single call to the sproc doesn't sound tricky. Changing it so that the loop (we'll get back to this) picks the characters sequentially off a stack until they've all been picked would be trivial, too. Cadavre and Eugene both show how to do this. But why do you have to live with this functionality which is crippling the actual, simple, process of inserting a set of data into one table from another - and increasing the amount and complexity of your code by a factor of 10? What makes it so critical that it overrides the business requirement?
I agreee with you...and the function Eugene gave is really good. I can over ride minw with this , but he is also unsure of unique values. You may think i am mad (no surprise :-)). I have been ordered to generate only unique values. already explained to my high authorites. they are not listening to me. And we pass lot of exec statements to call the stored procedure which contains this cursor. So i am not able to commit if one get stuck. How can i do this in a better way and to move to the next statement?
🙂 Atleast what i can do is, i have to skip the one which get stuck. and that's why i came to you all SQL gurus.
So, if table #tempSrcCodeDB contains more than 36 rows, it's okay to process 36 and throw away the rest?
Next thing - why not pick 36 rows at random from #tempSrcCodeDB and sequentially assign the unique characters? The end result is the same, but the code would be a hell of a lot simpler.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 4, 2013 at 8:29 am
Eugene,
that topic was posted by me and from there, i was able to get some of the ideas at that time.. Thank you for reminding me. But still i can not resolve my problem. 🙂
March 4, 2013 at 8:39 am
prakashr.r7 (3/4/2013)
Eugene Elutin (3/4/2013)
if you want unique values then use 36-base encoding function from here:http://www.sqlservercentral.com/Forums/Topic1267659-391-2.aspx
That one will generate unique values and it will "automatically" determine the size required based on how many numbers to be generated...
Thank you Eugene. But i can generate unique values, but i am not able to skip if i have to generate more than 36. If you look at my previous question, you can abe to understand what i am looking for....
exec Sp_Coded 'Test','BKBOOK','BMTYPE',5025,99999,10,'311','1','','','',''
exec Sp_Coded 'Test','BKBOOK','BMINCR',5026,99999,10,'370','1','','','',''
exec Sp_Coded 'Test','BKPOSS','PPCODE',5011,99999,10,'310','1','','','',''
exec Sp_Coded 'Test','BKPOSS','PPLOCA',3094,999,10,'2085','1','','','',''
exec Sp_Coded 'Test','BKPOSS','PPRESN',3102,999,10,'2087','1','','','',''
you see, we pass multiple exec statement, and if one get stuck because of not able to generate more than 36 values(depends on how many rows i have to insert) , it will not move to the next.
I do not understand what do you mean by "but i am not able to skip if i have to generate more than 36 values" and "if one get stuck because of not able to generate more than 36 values".
Skip what?
Stuck where?
I have re-read your post. I think you need to specify clearly what is your INPUT, what is expected OUTPUT for your input. Do you need stored proc to return a set of values? Do you need ability to generate values on fly for an existing/built dataset?
Here is another small UDF which can generate required set of unique character strings (basically Decimal-to-36-base conversion):
CREATE FUNCTION dbo.f_ConvertDecTo36Base (@N AS BIGINT)
RETURNS VARCHAR(50) WITH SCHEMABINDING
AS
BEGIN
-- some variables
DECLARE @result VARCHAR(50) = '';
-- encode value back to Base36
WHILE @N > 0
SELECT @result = SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', @N % 36 + 1, 1) + @result,
@N = @N / 36;
RETURN @result;
END
GO
select top 10000 dbo.f_ConvertDecTo36Base(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
from sys.columns c1, sys.columns c2
March 4, 2013 at 8:50 am
Eugene,
I can generate unique values....what i am asking is " How do you generate unique values for 40 rows ? " We can generate upto 36 right? How to throw the remaining 4 rows ?
March 4, 2013 at 8:52 am
prakashr.r7 (3/4/2013)
Eugene,I can generate unique values....what i am asking is " How do you generate unique values for 40 rows ? " We can generate upto 36 right? How to throw the remaining 4 rows ?
What do you mean by throw the remaining 4 rows?
March 4, 2013 at 8:55 am
prakashr.r7 (3/4/2013)
Eugene,I can generate unique values....what i am asking is " How do you generate unique values for 40 rows ? " We can generate upto 36 right? How to throw the remaining 4 rows ?
Sorry mate, but I'm afraid that I do not completely understand what you really want because of words you are using. "Throw"? do you mean generate values for 40 rows or generate it just for first 36 rows and throw 4 away?
Using my previous example, to generate 40 values you do just this:
select top 40 dbo.f_ConvertDecTo36Base(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
from sys.columns
throw
/THro/
Verb: Propel (something) with force through the air by a movement of the arm and hand
March 4, 2013 at 9:11 am
prakashr.r7 (3/4/2013)
Eugene,I can generate unique values....what i am asking is " How do you generate unique values for 40 rows ? " We can generate upto 36 right? How to throw the remaining 4 rows ?
Here's a way to get all 36 characters at random and know when they've all been consumed:
DROP TABLE #RandomChars
SELECT v, rn = ROW_NUMBER() OVER(ORDER BY (SELECT NEWID()))
INTO #RandomChars
FROM (VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z') ) d (v)
SELECT * FROM #RandomChars ORDER BY rn
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 4, 2013 at 9:12 am
First of all, Sorry Eugene...i am not a native speaker of english...so bare with me
yes..Eugene....generating 36 and skipping the the next incoming rows...since we can not generate more than unique 36 values of length 1 character.
I am not bothering about 2 length characters....just one length...
for example if i have to generate unique values of one length for 50 rows, i can generate upto 36 for the first incoming 36 rows....since unique is not sure in my function, i use if statement to check for duplicates....obviously my 37th row is going to get a duplicate value, my if statement is keep on looping to get a different unique value since i used "ELSE continue " ....So i am not able to complete this.
March 4, 2013 at 9:25 am
prakashr.r7 (3/4/2013)
First of all, Sorry Eugene...i am not a native speaker of english...so bare with meyes..Eugene....generating 36 and skipping the the next incoming rows...since we can not generate more than unique 36 values of length 1 character.
I am not bothering about 2 length characters....just one length...
for example if i have to generate unique values of one length for 50 rows, i can generate upto 36 for the first incoming 36 rows....since unique is not sure in my function, i use if statement to check for duplicates....obviously my 37th row is going to get a duplicate value, my if statement is keep on looping to get a different unique value since i used "ELSE continue " ....So i am not able to complete this.
Check all replies to your post. No one here tries to fix your code! And there is a good reason for this - because what you are trying to do doesn't require such complicated cursor which will generate values row-by-row.
Your requirements sound very messy!
You cannot generate 50 unique values from the list of 36. If you going to have duplicates, then you cannot call them unique!
Again, I still cannot understand what is going to be an INPUT in your logic!
Could you please clearly specify what are you going to pass into function or stored proc.
March 4, 2013 at 9:48 am
Create TAble Coded (JurisID int,CodeID int, srcCodeValue varchar(50), Description varchar(100), PnxCodeValue varchar(10))
insert into Coded(JurisId,CodeID, srcCodeValue,Description)
Select 0,1034,'BLU','BLUE'
union
Select 0,1034,'O','ORANGE'
union
Select 0 , 1034,'R','RED'
union
Select 0 , 1034,'GR','GREEN'
union
Select 0 , 1034,'BW','BROWN'
If you see the above table, the field called PnxCodeValue which is going to be updated with random values...
my functionality is, i have to update the pnxcodevalue feild, with the same SrcCodeValue if the length of the SrcCodeValue is equal to one. for example, the second row has 'O' as SrcCodeValue so i will update as it is in PnxCodeValue field and so 'RED'. For the remaining values, i have to generate unique one length value which is not already inserted...i mean we must not update that field again with 'O' and 'R'.....so for 'GREEN', i have to generate a random unique value.....so i use a update statement initially, to update the rows which has SrcCodeValue feild with one length value.....and remaining rows with random unique values...
March 4, 2013 at 9:54 am
Since i don't want to update the rows with the existing values...i do row by row operation to avoid duplicates.....well, some function can be able to give unique values, but since some of the rows are already updated, i have to skip those values and need to generate a new value to be updated with. So i need 'IF' statement to check for duplicates....
March 4, 2013 at 10:35 am
....and remaining rows with random unique values...
and if you have more than 36 (or, as per your sample which already contains two of one-char codes, 34) rows remaining, what code they should be set to? There will be no unique values left.
March 4, 2013 at 10:42 am
Eugene Elutin (3/4/2013)
....and remaining rows with random unique values...
and if you have more than 36 (or, as per your sample which already contains two of one-char codes, 34) rows remaining, what code they should be set to? There will be no unique values left.
yes, as per my sample we can generate 34 new one char codes apart from those 2...but if it is more than 36, this is where i am getting kicked off....my process is keep on looping to get a new value...so how can i stop it, if it exceeds more than we could generate....?
Eugene, do you understand my problem now ? just curious
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply