Getting a table value before updating.

[jcoleman]

I'm sure this is a newbie question that can be easily answered.

What I'm trying to do is find out if my result from a calulation based on a field in my database is above or below 10.

If it's above ten do one thing if not do another. My problem is that the script does not reconize my table name with in the if statement.

I'm sure that I'm only missing a couple of easy statements, but like everything else around here I'm flying by the seat of my pants.

Anyways, here's the part of my script that's giving me issues.

if (105+(substring([BarCode conv].[Barcode Number],1,2))+
(substring([BarCode conv].[Barcode Number],3,2)*2)+
(substring([BarCode conv].[Barcode Number],5,2)*3)+
(substring([BarCode conv].[Barcode Number],7,2)*4)+
(substring([BarCode conv].[Barcode Number],9,2)*5)+
(substring([BarCode conv].[Barcode Number],11,2)*6))/103.00 >= 10
begin
update [BarCode conv]
set [Check] = [BarCode Values].Value
from [BarCode conv] inner join [BarCode Values]
on [BarCode Values].Number = substring(cast((round(((105+(substring([BarCode conv].[Barcode Number],1,2))+
(substring([BarCode conv].[Barcode Number],3,2)*2)+
(substring([BarCode conv].[Barcode Number],5,2)*3)+
(substring([BarCode conv].[Barcode Number],7,2)*4)+
(substring([BarCode conv].[Barcode Number],9,2)*5)+
(substring([BarCode conv].[Barcode Number],11,2)*6))/103.00),2)) as char(10)),4,2)
end

So line 1 is where my problem is, if I run just the update part it works great.

Thanks for any pointers.
jc

 

[SQLSister]

I don;t think yoiu need an if statement. Tery:

update [BarCode conv]
 set [Check] = [BarCode Values].Value
   from [BarCode conv] inner join [BarCode Values]
   on [BarCode Values].Number = substring(cast((round(((105+(substring([BarCode conv].[Barcode Number],1,2))+
            (substring([BarCode conv].[Barcode Number],3,2)*2)+
            (substring([BarCode conv].[Barcode Number],5,2)*3)+
            (substring([BarCode conv].[Barcode Number],7,2)*4)+
            (substring([BarCode conv].[Barcode Number],9,2)*5)+
            (substring([BarCode conv].[Barcode Number],11,2)*6))/103.00),2)) as char(10)),4,2)
Where (105+(substring([BarCode conv].[Barcode Number],1,2))+
            (substring([BarCode conv].[Barcode Number],3,2)*2)+
            (substring([BarCode conv].[Barcode Number],5,2)*3)+
            (substring([BarCode conv].[Barcode Number],7,2)*4)+
            (substring([BarCode conv].[Barcode Number],9,2)*5)+
            (substring([BarCode conv].[Barcode Number],11,2)*6))/103.00 >= 10

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[jcoleman]

Perfect

Thanks,