Author |
Topic |
stevehatpa
Starting Member
21 Posts |
Posted - 2008-04-18 : 14:19:56
|
I'm still somewhat of a novice with writing sql statements, so please bear with me.I wrote the following statement in a Column in design view of a "View" that I am designing. I wrote ROUND((100 - dbo.TX_PA_HOMESTEAD.HOMESTEAD_12) * .01 * dbo.TX_RE_ASSESSMENT.VA_TX_VALUE, - 1)That works perfectly, however I also want to an additional statement in front of it that will essentially say "if isnull [tx_pa_homestead.homestead_12] then [dbo.tx_re_assessment.va_tx_value] Else ROUND((100 - dbo.TX_PA_HOMESTEAD.HOMESTEAD_12) * .01 * dbo.TX_RE_ASSESSMENT.VA_TX_VALUE, - 1)I know this isn't the way most people would do it, but I think I'm somewhat close to an answer, but I'm just not sure how to do it. Any help would be greatly appreciated.-Steve H. |
|
bfoster
Starting Member
30 Posts |
Posted - 2008-04-18 : 14:25:39
|
CASE WHEN [tx_pa_homestead.homestead_12] IS NULL THEN[dbo.tx_re_assessment.va_tx_value] ELSE ROUND((100 - dbo.TX_PA_HOMESTEAD.HOMESTEAD_12) * .01 * dbo.TX_RE_ASSESSMENT.VA_TX_VALUE, - 1) ENDI think you could probably use COALESCE instead of CASE here as well. COALESCE returns the first argument that isn't NULL. COALESCE(ROUND((100 - dbo.TX_PA_HOMESTEAD.HOMESTEAD_12) * .01 * dbo.TX_RE_ASSESSMENT.VA_TX_VALUE, - 1), [dbo.tx_re_assessment.va_tx_value]) |
|
|
stevehatpa
Starting Member
21 Posts |
Posted - 2008-04-18 : 15:12:40
|
The COALESCE statement seems to be working perfectly.thanks for the help! |
|
|
stevehatpa
Starting Member
21 Posts |
Posted - 2008-04-21 : 11:48:12
|
I have one more question.In this same VIEW, I want anything in column TX_PA_HOMESTEAD.HOMESTEAD_12, that is less than 51, to be shown as 50. How would I write it in the view?ThanksSteve H. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-21 : 12:00:22
|
CASE WHEN TX_PA_HOMESTEAD.HOMESTEAD_12<51 THEN 50 ELSE TX_PA_HOMESTEAD.HOMESTEAD_12 END |
|
|
stevehatpa
Starting Member
21 Posts |
Posted - 2008-04-21 : 12:02:30
|
Unfortunately Query Designer doesn't support CASE Expressions. Is there any other way to do it?-Steve |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
|
stevehatpa
Starting Member
21 Posts |
Posted - 2008-04-21 : 13:02:04
|
Unfortunately I'm somewhat limited to Query Designer, because I don't have great skills with query analyzer. Is there any possible way to do it in Query Designer?Thanks.-Steve |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-21 : 13:10:16
|
you just require a very basic knowledge of sql constructs to get yourself working with query analyser.You can get lots of help from forums & tutorial sites etc.Post whatever doubt you've and we will be happy to help you out. |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-04-21 : 13:27:14
|
visakh16 is right - it should be very easy for you to get up and running with query analyzer. Just ask us what you need.Your request was an interesting problem for me however - so thanks . I would not recommend using this technique...declare @t table (i int)insert @t select 30union all select 40union all select 49union all select 50union all select 51union all select 60union all select 160select i, cast(isnull(nullif(i*1.0*cast(floor((i/50)) as bit), 0), 50) as int) as x from @t/* Results (works if i is > -50)i x----------- -----------30 5040 5049 5050 5051 5160 60160 160*/ Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|