Numeric in Strings problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Numeric in Strings problem

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-19 : 10:15:05

Hi I have a problem trying to peform a calculated update in a table through a string loop statement:

Declare @Number_1 numeric,
@Number_2 numeric,
@Counter numeric,
@string varchar

set @Counter = 1

while @Counter < 100
begin
set @Number_1 = (select num from table where id = @counter)
set @Number_2 = (select num from table where id = @counter + 1)
set @string = 'update table set difference = ' + @Number_2 + ' - ' + @Number_1
set @counter = @counter + 1
end

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-19 : 10:18:36

Use CAST or CONVERT to convert numeric to varchar:

set @string = 'update table set difference = ' + Cast(@Number_2 as varchar(20)) + ' - ' + cast(@Number_1 as varchar(20))

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-19 : 10:32:07

I would suggest telling us what you are trying to do (not HOW you are trying to do it, but what needs to be done overall) and we can probably help you come up with a better solution. Looks like you might be making things way more complicated than necessary.

- Jeff

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-19 : 10:47:52

thanks

Basically I need to identify ONLY consecutive invoice numbers for a particular supplier.

I have a table in the format of:

Supplier_No, Invoice_Number, SRN(numeric identity column)
123 123 1
123 124 2
123 150 3
145 161 4
145 165 5
145 166 6

Therefore my results should be:

123 123 1
123 124 2
145 165 5
145 166 6

as these are only consecutive invoice numbers which I need to identify.

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-19 : 10:55:16

EDIT: intial solution only returns the first of the two consecutive numbers, never the second. Let's fix that.

something like this should do the trick:

select t1.*
from YourTable t1
inner join Yourtable t2 on t1.Supplier_no = t2.SupplierNo and t1.Invoice_Number+1 = t2.InvoiceNumber


select t1.*
from YourTable t1
where exists 
  (select * from 
   YourTable t2 
   where t1.Supplier_no =t2.SupplierNO and 
         t2.Invoice_Number between t1.Invoice_Number -1 and t1.InvoiceNumber + 1)

That should do it ... bascially it says "return all rows where the supplier has an invoice number 1 more or 1 less than the current row's invoice numnber".
- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 10:58:14

quote:
Originally posted by jsmith8858

Looks like you might be making things way more complicated than necessary.

I agree. Especially since the UPDATE statement is never even run

And if it were run, there is no WHERE clause so to entire operation can be set to just one update

update table set difference = (select num from table where id = 99) - (select num from table where id = 100)

Peter Larsson
Helsingborg, Sweden

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-19 : 11:10:36

awesome jsmith8858!

that works great. Yes I did think a loop of update statements was a bit long winded.

Learn something new everyday...:)

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-19 : 11:18:05

.....apologies that doesn't bring back the results required...I'll try and amend it..

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-19 : 11:21:35

select t1.*
from YourTable t1
inner join Yourtable t2 on t1.Supplier_no = t2.SupplierNo and t1.Invoice_Number+1 = t2.InvoiceNumber
or t1.InvoiceNumber - 1 = t2.InvoiceNumber

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:51:07

Did you notice his EDIT?

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-19 : 12:50:18

quote:
Originally posted by dnf999

select t1.*
from YourTable t1
inner join Yourtable t2 on t1.Supplier_no = t2.SupplierNo and t1.Invoice_Number+1 = t2.InvoiceNumber
or t1.InvoiceNumber - 1 = t2.InvoiceNumber

as mentioned, I edited my post with a better solution ...

the problem with joining like this is that you will get duplicate results returned; if an invoice matches one before and one after, it will be returned twice.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources