November 22, 2009 at 11:55 pm
Hi,
How do I compute for the total of the value in Alias vladd1, vladd2, vladd3, vladd4 or any alternative using declare statement ?
Here is my sample statement below.
select alan8,
case
when rtrim(aladd1) <> '' then len(aladd1) + 9 else 0 end as vladd1 ,
case
when rtrim(aladd1) <> '' then len(aladd2) + 9 else 0 end as vladd2 ,
case
when rtrim(aladd1) <> '' then len(aladd3) + 9 else 0 end as vladd3 ,
case
when rtrim(aladd1) <> '' then len(aladd4) + 9 else 0 end as vladd4,
vladd1 + vladd2 + vladd3 + vladd4 as Total
from Customers
Thanks and appreciate your help.
Regards,
cyberarnet
November 23, 2009 at 12:48 am
arnold-491793 (11/22/2009)
select alan8,case
when rtrim(aladd1) <> '' then len(aladd1) + 9 else 0 end as vladd1 ,
case
when rtrim(aladd1) <> '' then len(aladd2) + 9 else 0 end as vladd2 ,
case
when rtrim(aladd1) <> '' then len(aladd3) + 9 else 0 end as vladd3 ,
case
when rtrim(aladd1) <> '' then len(aladd4) + 9 else 0 end as vladd4,
vladd1 + vladd2 + vladd3 + vladd4 as Total
from Customers
Two ways that I can think of are,
Select vladd1, vladd2, vladd3, vladd4 , (vladd1+ vladd2 + vladd3 + vladd4) FROM
(select alan8,
case
when rtrim(aladd1) <> '' then len(aladd1) + 9 else 0 end as vladd1 ,
case
when rtrim(aladd1) <> '' then len(aladd2) + 9 else 0 end as vladd2 ,
case
when rtrim(aladd1) <> '' then len(aladd3) + 9 else 0 end as vladd3 ,
case
when rtrim(aladd1) <> '' then len(aladd4) + 9 else 0 end as vladd4,
vladd1 + vladd2 + vladd3 + vladd4 as Total
from Customers) t
and
(select alan8,
case
when rtrim(aladd1) <> '' then len(aladd1) + 9 else 0 end as vladd1 ,
case
when rtrim(aladd1) <> '' then len(aladd2) + 9 else 0 end as vladd2 ,
case
when rtrim(aladd1) <> '' then len(aladd3) + 9 else 0 end as vladd3 ,
case
when rtrim(aladd1) <> '' then len(aladd4) + 9 else 0 end as vladd4,
<<repeating and adding all your case statements here!>>
from Customers)
Still not sure if case is on the same column aladd1 for all thos columns as you are checking the same condition!
---------------------------------------------------------------------------------
November 23, 2009 at 7:20 pm
This is cool. Thanks. That's right the 3 aladd1 in case statement should be aladd2, aladd3, aladd4. Just a typo error.
November 23, 2009 at 8:45 pm
Heh... I just can't resist...
Since all of the following...
SELECT LEN(' '),
LEN(' '),
LEN('')
... equal zero, there is no need for the CASE statements nor the RTRIM comparisons. The code below is only a second faster on a million rows (about 4.9 CPU seconds instead of 5.9 CPU seconds... still, that's a 17% improvement) and probably isn't worth the obfuscation but forget all of that... it's just fun to write something totally different once in a while...
WITH
ctePreAgg AS
(
SELECT alan8,
ISNULL(NULLIF(LEN(aladd1),0)+9,0) AS vladd1,
ISNULL(NULLIF(LEN(aladd2),0)+9,0) AS vladd2,
ISNULL(NULLIF(LEN(aladd3),0)+9,0) AS vladd3,
ISNULL(NULLIF(LEN(aladd4),0)+9,0) AS vladd4
FROM dbo.Customers
)
SELECT alan8, vladd1, vladd2, vladd3, vladd4,
vladd1 + vladd2 + vladd3 + vladd4 AS Total
FROM ctePreAgg;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply