June 27, 2011 at 9:13 am
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.TableB
Else
Select Column B from Table B
Where ID = @ID
Any ideas?
June 27, 2011 at 9:18 am
First instinct :
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
June 27, 2011 at 9:25 am
I tried your statement, I am getting a conversion error. There are 3 datatypes. 'Yes' 'No' And 'Other'. So right now the column has the value 'Other' & it cannot convert this value. I am sorry I should have specified this earlier.
June 27, 2011 at 9:27 am
This is really basic. You need to use the same datatypes on both sides of the equation. I can't see your screen and I can't do your work for you.
Another first instinct would be to join on the lookup table... or at least look it up to get the [int or else] value.
June 27, 2011 at 9:33 am
staindromeo1 (6/27/2011)
I tried your statement, I am getting a conversion error. There are 3 datatypes. 'Yes' 'No' And 'Other'. So right now the column has the value 'Other' & it cannot convert this value. I am sorry I should have specified this earlier.
The "Other" entry shouldn't matter in the script that was posted.
What data type is "Column B"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2011 at 9:36 am
GSquared (6/27/2011)
staindromeo1 (6/27/2011)
I tried your statement, I am getting a conversion error. There are 3 datatypes. 'Yes' 'No' And 'Other'. So right now the column has the value 'Other' & it cannot convert this value. I am sorry I should have specified this earlier.The "Other" entry shouldn't matter in the script that was posted.
What data type is "Column B"?
Column B is tinyint & that's what it is having trouble converting to.
June 27, 2011 at 9:38 am
As I said, you need to find the right value. I'd guess that 0 means NO, 1 means Yes AND 2 means other. But you need to find the table or documentation that lists those values. Then use the correct value instead of the string. If you insist on using the string value then you need 1 more join to go get it.
June 27, 2011 at 9:42 am
Sorry I didn't see your response, but basically I am trying to convert a varchar value to a tinyint.
June 27, 2011 at 9:47 am
staindromeo1 (6/27/2011)
Sorry I didn't see your response, but basically I am trying to convert a varchar value to a tinyint.
Go back to school, you're way behind on the very basics of how sql works.
That value has to be stored in another table (unless the design is flawed). Find the table, make the join and move on. You can't cast a char to a tinyint. Not with the values you have in the varchar at the moment.
June 27, 2011 at 9:53 am
Alright mate,
There is no other table where the value exists, If its Other or No , I don't want it to do anything. Your Else statement doesn't seem to be doing anything. Anyways thanks for your help.
June 27, 2011 at 10:01 am
staindromeo1 (6/27/2011)
Alright mate,There is no other table where the value exists, If its Other or No , I don't want it to do anything. Your Else statement doesn't seem to be doing anything. Anyways thanks for your help.
Well that's the whole point. Do SOMETHING if = 'YES' if not do nothing.
I used Cola = CASE because I assumed an arithmetic problem. If it's not the case then we need more info.
June 27, 2011 at 10:01 am
Here's a sample of what you seem to be describing:
CREATE TABLE #A
(ID INT PRIMARY KEY,
ColA CHAR(5)) ;
CREATE TABLE #B
(ID INT PRIMARY KEY,
ColB TINYINT) ;
INSERT INTO #A
(ID, ColA)
VALUES (1, 'Yes'),
(2, 'No'),
(3, 'Other'),
(4, 'Yes') ;
INSERT INTO #B
(ID, ColB)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 4) ;
SELECT CASE ColA
WHEN 'Yes' THEN ColB - 1
ELSE ColB
END AS ColBNewValue,
*
FROM #A
INNER JOIN #B
ON #A.ID = #B.ID ;
You'll need to modify the insert statements if you're using a version of SQL Server prior to 2008, but that should be easy enough.
Now, what about that doesn't match your situation?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2011 at 10:04 am
The thing is we can't see you data. Can you post something that builds a table and populates it with sample data?
Something like
Create tableA
( ID int
, ColA varchar(10)
)
go
insert TableA select 1, 'yes'
insert TableA select 2, 'No'
insert TableA select 3, 'Other'
and we can help.
Basically the CASE allows you to specify multiple tests in a SELECT. So you can do
SELECT
CASE when colA = 1 then colB -1
when colA = 2 then colb - 5
when colA = 3 then 7
else 0
end
from TableA
inner join Table B
where tableA.id = TableB.id
June 27, 2011 at 11:26 am
I created a column with the value types & put a bunch of if statements to perform the function with an Inner join in there. That worked for me. thanks for all your help
June 27, 2011 at 11:33 am
staindromeo1 (6/27/2011)
I created a column with the value types & put a bunch of if statements to perform the function with an Inner join in there. That worked for me. thanks for all your help
Bunch of if??? Should have been a case in there, not a series of ifs.
Care to post the code so we confirm it's ok?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply