Case Statment - Not sure if I should use it or not

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 Statment - Not sure if I should use it or not

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2007-10-30 : 08:42:42

I am trying to have all these calulcation int one column under AccessFeeFinal, but it is not working. It was working when I just had the one case statement, but we needed to add another one in because we were getting some blank info in the column. Below are the two statement then I added a third on to show you how I think it should be, but it is not working for me. Please help me out if you can. I was thinking maybe I needed to use an IF Statement.

CASE clm_att1
WHEN 'NG' THEN (clm_H30)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_H30)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,

CASE clm_att1
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,

CASE clm_att1
WHEN 'NG' THEN (clm_H30)*(clio_fee04/100)
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_H30)*(clio_fee04/100)*.65
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,

Thanks

Wendy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-30 : 08:47:44

What does "is not working" or "getting some blank info" mean? How are we supposed to guess what the problem is if you don't tell us specifically? Can you provide specific examples of your data and what the problem is?

Also, CASE is an expression, not a statement, it just returns a single value. You should not be mixing data types in your return values; in your CASE expressions, you are sometimes returning a varchar and sometimes a numeric value; this requires an implicit conversion and should be avoided. Always be consistent and return 0 (without the quotes) if you need to return the *value* of zero as a possibility along with other numeric values.

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-30 : 08:48:24

If a condition is satisfied in a CASE Expression then it terminates. Though you used WHEN 'NG' twice only the first would be exeucted for the value NG

select case
		when 10=20/2 then 'true'
		when 10=20/2 then 'false'
	end

Can you explain what you want to do? Do you want to sum the expressions?

Madhivanan

Failing to plan is Planning to fail

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-30 : 12:31:00

Wendy,

Also, all of your CASE statements end with "END as AccessFeeFinal". You can't use a column alias more than once in the same SQL, so you'll need to use different aliases for each column.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-02 : 04:22:06

CASE clm_att1
WHEN 'NG' THEN (clm_H30)*(clio_fee04/100)
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_H30)*(clio_fee04/100)*.65
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,

the problem as per I think is twiceuse of same value of clm_att1
i.e. first statement and second
fourth and fifth
these represents the same values for clm_att1

but in case statement as soon as first match get fixed control will not see for
other values so
when u will search with clm_att1=NG every time first statement will be executed
second would not be executed ever.

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 05:16:41

Isn't that what Madhi said? or have I missed something extra you are adding?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources