Pass data from a lookup table 1

[IAMINFO]

gmmastro I am still having a problem,you provided me with this code it works great for a single value, when I try to select from my lookup table I get a error message that says
Subquery returned more than 1 value. This is not permitted.
What am I missing? Thank you


Declare @rd VarChar(20), @Output VarChar(20)
Declare @number Table(Digit Char(1))

Set @rd = (select numdata from numbers)

Insert Into @number
Select SubString(@rd, Number, 1) As Digit
From master..spt_values
Where Type = 'P'
And Number Between 1 And Len(@rd)

Set @Output = ''

Select @Output = @Output + Digit
From @number As Digits
Order By Digit

Select @Output

 

[kjv1611]

On which line of code are you getting the error? It sounds like you're returning multiple values where the design of the query only expected a single value.

 

[kjv1611]

I'm guessing this is the spot:

And Number Between 1 And Len(@rd)

For the Between statement to work, it's looking for 2 values. And within that, the Len() function, unless I'm forgetting something, can only grab the length of a single entity - such as a string, or perhaps a number... I think it has to be a string. But what it's getting is "Give me the length of a recordset/table" - so the computer can't quite understand that request, and pukes out the error message - that's my guess.

 

[IAMINFO]

kjv1611 thank you for taking the time , I am still looking at this I believe I might have to explore writing cursor or use a while loop, any thoughts.

The error pertains to this line

Set @rd = (select numdata from numbers)

 

[gmmastros]

Can you post some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IAMINFO]

gmmastros the numbers table have one column named numdata
which is varchar(5) the values in the column are like this


59345
48374
32953

I need the output to be
34559
34478
23359

Thank you for responding gmmastros

 

[gmmastros]

Since you are only dealing with 5 digit numbers, it's probably best to "brute force" this.

First, copy/paste this function to a query window and run it.

Create Function dbo.OrderDigits(@Input VarChar(5))
Returns VarChar(5)
As
Begin
  Declare @Output VarChar(5)

  Set @Output = ''

  Select @Output = @Output + Digit
  From   (
         Select SUBSTRING(@Input,1 , 1) As Digit
         Union All 
         Select SUBSTRING(@Input,2 , 1)
         Union All 
         Select SUBSTRING(@Input,3 , 1)
         Union All 
         Select SUBSTRING(@Input,4 , 1)
         Union All 
         Select SUBSTRING(@Input,5 , 1)
         ) As Digits
  Order By Digit

  Return @Output 
End

Now you can call this function with as many rows as you would like by doing this:

Select NumData, dbo.OrderDigits(NumData) As OrderedDigits
From   Numbers

Using a function like this may not be the best way to write the code, but it is relatively simple and straight forward. Running this against a table that has 100,000 rows (00000 to 99999) took 3 seconds on my computer.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IAMINFO]

gmmastros I cannot thank you enough , if it's ok I would like to make a donation in your name to tek-tips, I have to give back some kind of way.

Thank you if not tek-tips tell me your favorite charity or something. I really appreciate this.

 

[gmmastros]

You don't really need to do anything. I do appreciate the heart-felt thank-you though.

I don't usually mention personal stuff on here, because that's not really what this forum is for, but... I've been diabetic for 12 years now. I am currently taking 4 doses of insulin per day. As such, my favorite charity is the American Diabetes Association.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IAMINFO]

Thank you gmmastro take care I am making that donation right now