VIEWS 1

[GCM06]

hi guys below I have the following code. The problem that I am having is that for totalpts, I will like to get the accumulation but instead I get null values. Any ideas?
by the way all the column's data types are ints.

CREATE VIEW dbo.Calculate
AS
SELECT ptage =
CASE
WHEN age <= 35 THEN 0
WHEN age <= 40 THEN 8
END,
'pttrus' =
CASE
WHEN trus < 40 THEN 78
WHEN trus >= 40 and trus < 55 THEN 59
END,
'ptpsa' =
CASE
WHEN psa < 4 THEN 0
WHEN psa >= 4 AND psa <10 THEN 68
END,
'totalpts' = ptage + ptpsa + pttrus
FROM dbo.Risk

Thanks in advance,
GC

 

[vbkris]

try:

CREATE VIEW dbo.Calculate
AS
SELECT ptage =
CASE
WHEN age <= 35 THEN 0
WHEN age <= 40 THEN 8
END,
'pttrus' =
CASE
WHEN trus < 40 THEN 78
WHEN trus >= 40 and trus < 55 THEN 59
END,
'ptpsa' =
CASE
WHEN psa < 4 THEN 0
WHEN psa >= 4 AND psa <10 THEN 68
END,
(ptage + ptpsa + pttrus) as totalpts
FROM dbo.Risk


Known is handfull, Unknown is worldfull

 

[GCM06]

Thanks for replying but It still gives me a null value.

 

[vbkris]

hi, sorry my mistake, didnt read it properly.

try this:


select
*,
(ptage + ptpsa + pttrus) as totalpts
from
(
SELECT ptage =
CASE
WHEN age <= 35 THEN 0
WHEN age <= 40 THEN 8
END,
'pttrus' =
CASE
WHEN trus < 40 THEN 78
WHEN trus >= 40 and trus < 55 THEN 59
END,
'ptpsa' =
CASE
WHEN psa < 4 THEN 0
WHEN psa >= 4 AND psa <10 THEN 68
END,

FROM dbo.Risk
)Dtable1


Known is handfull, Unknown is worldfull