Case error - cast? - 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

General SQL Server Forums

New to SQL Server Programming

Case error - cast?

Author

Topic

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 13:55:09

I have a case like

case

WHEN (@income IS NULL)THEN ( '0000')
WHEN (@income='') THEN ( '0000')
WHEN (@income LIKE '%N%A%')THEN 'NCD'
WHEN (@income = 0) THEN 'ST'
ELSE 'NA'
END

return @income

@income is DECIMAL(17,0)

but @income need to be 'ncd'

how to cast ?

ERROR
Msg 8114, Level 16, State 5, Line 28
Error converting data type varchar to numeric.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 14:09:42

No CAST!
When you need your variable @income to take something like 'NCD' or 'ST' then your variable should be declared as VARCHAR(17) because you cannot cast 'NCD' or 'ST' to a DECIMAL.

Webfred

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 14:12:56

Also in the above case you're not doing any casting.what you want to do is to just put result of case into a varchar field as webfred pointed out.

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 14:31:12

This income field is a function and is used in another procedure.In that procedure column name Income is decimal(17,0).That cannot be changed.the value for that field is from @income,so i think this should be decimal(17,0)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 14:41:26

quote:
Originally posted by Chinni

This income field is a function and is used in another procedure.In that procedure column name Income is decimal(17,0).That cannot be changed.the value for that field is from @income,so i think this should be decimal(17,0)

Sorry, but i'm total confused with this art of information and i'm not able to give any tips.

Webfred

Planning replaces chance by mistake

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 14:44:46

Sorry-

This case statement is in a fucntion.This fuction is used to retrive income field in aa Stored Procedre.In SP income field is decimal(17,0).This cannot be changed

In function if the return @income is converted to varchar that may become problem at SP.

So do we have any chance with out changinf @income to varchar?

and also a condition is present before case which has a value like @income = 2345 if this dont have a value then case statement.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 14:54:57

Where does the value in @income come from?
A part of your function is: [WHEN (@income LIKE '%N%A%')THEN 'NCD'].
But when @income is decimal then why you're testing its content for impossible characters like N and A?
Say it's possible to return 'NCD' or 'ST' or 'NA', then what should happen in the calling Storecd Procedure?

Planning replaces chance by mistake

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 15:12:34

@INCOME = select income from table 1
income datatype: decimal

If income has a value then display value else these conditions

case
WHEN (@income IS NULL)THEN ( '0000')
WHEN (@income='') THEN ( '0000')
WHEN (@income LIKE '%N%A%')THEN 'NCD'
WHEN (@income = 0) THEN 'ST'
ELSE (select income from table )
END

This @ income is used in stored proc that is to retrive one incoem field in Sp we use this fucntion.The retrived fields are loaded to a table2 where in table income datatype in decimal

when i used as varchar they suggested me

*You can cast to varchar later in your case statement if you want to return a string

Sorry if i did not explain properly

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 15:33:11

OK!
There is table1 and it's the source.
INCOME - coming from table1 - can only be a numeric value or a null value because of datatype decimal.
[qoute]If income has a value then display value[/quote]
What is the meaning of DISPLAY? What is the meaning of "has a value"?

-->
Now there is your CASE-Statement and in some cases it will return non-numeric values!
-->
Now the calling SP wants to store THE RETURNED VALUE in table2 where income data type is decimal?

That is not possible!

Don't know your system, but maybe it's a way to declare some numeric values to treat as 'NCD' or 'ST' like
value of income = -9999999999 is treatet as 'NCD'
value of income = -8888888888 is treated as 'ST'

That is not a fine solution but i cannot see another way without picking your system to pieces.

Webfred

Planning replaces chance by mistake

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 15:45:19

so what i understand is the column in source table 1 Income with datatype decimal can not have N or A as its value in the field.

So the logic given is not correct in case

and we can change the source table datatype if we want to Varchar .

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 15:52:24

Yes.
Yes.
Yes.
But it's not the solution for the destination table and your Returnvalues - isn't it?

Planning replaces chance by mistake

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 16:04:07

yes..

I dont know why they have this logic like
if NA in table1 send NCD to table2

I will find out my BA regarding this

since we dont have a question of having NA in table1

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 16:12:10

I have one more doubt

declare @Incomeid varchar(17)
declare @Income DECIMAL(17,0)
set @income =(select v_table.income from v_table where id = 91686)

set @incomeid =
case
WHEN (@income LIKE '%N%A%')THEN 'NCD'
WHEN (@income = 0) THEN 'ST'
WHEN (@income ='')THEN 'NCD'
WHEN (@income IS NULL)THEN ( '0000')
else @income
end
Error:
Msg 8114, Level 16, State 5, Line 7
Error converting data type varchar to numeric.

but when i dont have those two conditions its working good
when i split them its good

like

if(select v_table.income from v_table where id = 91686)in not null
set @income =(select v_table.income from v_table where id = 91686)
set @incomeid =
case
WHEN (@income LIKE '%N%A%')THEN 'NCD'
WHEN (@income = 0) THEN 'ST'
else @income
end
end
else
begin
set @incomeid =
case
WHEN (@income ='')THEN 'NCD'
WHEN (@income IS NULL)THEN ( '0000')
else'na'
end

end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 16:13:28

Now here it's about 10:13 PM and i will get a delicious Beer (elephant)

greetings
Webfred

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 03:19:27

what's the datatype of v_table.income ?And you've declared @income as decimal. then why are you comparing it to '%N%A%'? if its decimal how can it hold N,A characters?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources