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 |
|
shajimanjeri
Posting Yak Master
179 Posts |
 Posted - 2010-06-16 : 07:25:30
|
| Dear all,I have a table with three fields Id as int, Eligible as varchar, Commission as decimalData stored in my table as below:Id = 1 Eligible = Yes Commission = 1000Id = 2 Eligible = No Commission = 0Id = 3 Eligible = No Commission = 678like as these. But here my issue is that I want to make Commission = 0 if Eligible = No.See in the above table the third row shows Commission=678 but Eligible = 0, so I want to make Commission = 0 in my query.select Eligible as Eligible, convert(varchar,CAST(IsNull(Commission,0) AS decimal(20,0))) as Commission from v_commission_valuesCan any one Just update this query with those changes pleaseThanks in advance,,regards |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-06-16 : 07:35:38
|
| select Eligible as Eligible,CASE WHEN eligible = 'YES' THEN convert(varchar,CAST(IsNull(Commission,0) AS decimal(20,0))) ELSE 0 END as Commissionfrom v_commission_valuesJimEveryday I learn something that somebody else already knew |
 |
|
|
shajimanjeri
Posting Yak Master
179 Posts |
Posted - 2010-06-16 : 07:39:20
|
| Perfect, many thanks mr. JimregardsShaji |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-16 : 08:22:43
|
quote:
Originally posted by jimf
select Eligible as Eligible,CASE WHEN eligible = 'YES' THEN convert(varchar,CAST(IsNull(Commission,0) AS decimal(20,0))) ELSE 0 END as Commissionfrom v_commission_valuesJimEveryday I learn something that somebody else already knew
When you convert to character datatypes, make sure to specify the lengthhttp://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Query Update