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.
| Author |
Topic |
|
rankone
Starting Member
24 Posts |
 Posted - 2011-06-27 : 11:12:10
|
| Hey everyone, I am confused as to if I should use an IF or CASE statement in what I am trying to do. I am trying to check if a column's value is a word & if it is that word then to subtract '1' from a value in a column in another table. So something along the lines of: IF Value in ColumnA from TableA = "YES" Then Minus '1' from ColumnB.TableBElse Select Column B from Table B Where ID = @IDAny ideas? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-27 : 12:05:45
|
| I'm guessing you already have a join.What do you mean by subtract '1'? - is it an int or character column?Do you want to update the value in the table or return the value?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rankone
Starting Member
24 Posts |
Posted - 2011-06-27 : 12:15:45
|
Well I am working off this statement so far: SELECT ColB - CASE WHEN ColA = 'YES' THEN 1 ELSE 0 END AS ColB FROM dbo.TableB B INNER JOIN dbo.TableA A ON B.id = A.id But I have two other values specified in there such As 'No' & 'Other', basically if it's No/Other I don't want it to do anything but I am getting a conversion error since it cannot convert from datatype varchar to tinyint. So I am trying to work around that |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-27 : 12:21:32
|
| >> I have two other values specified in there such As 'No' & 'Other'In where - ColA or ColB?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rankone
Starting Member
24 Posts |
Posted - 2011-06-27 : 12:25:32
|
| In Column A, since I was having a conversion error. I created a new column that actually has values. So if its Yes then the value is 1 and if its No then 0. How can I associate these 2 columns in table A with and subtract from column in Table B. Such as If Value in Column A is 'Yes' then goto ValueType (The other column)= '1' - (ColumnB.TableB) |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-27 : 12:27:09
|
| MaybeSELECT ColB = CASE WHEN A.ColA = 'YES' THEN convert(varchar(10),B.ColB - 1) ELSE B.ColB END FROM dbo.TableB B INNER JOIN dbo.TableA A ON B.id = A.id==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rankone
Starting Member
24 Posts |
Posted - 2011-06-27 : 13:25:06
|
| I used a couple of convert statements within IF's and point it towards the value column, that worked for me. Thanks for all your help |
|
|
|
|
|
|
Subtract on IF Statement