Comparing CASE statement products

The query I have written below works fine. However, I now want to uncomment the WHERE clause below to find entries where the PatientAge does not fall between the PAGBeginningAge and PAGEndingAge. However, when I uncomment the WHERE clause line I receive the following error message:

Msg 156, Level 15, State 1, Line 28

Incorrect syntax near the keyword 'FROM'.

SELECT ampfm.rpt_AdtVisit.PatientFullName, ampfm.rpt_AdtVisit.AdmitPriorityCode, ampfm.dct_AdmitPriorityType.AdmitPriorityTypeName,

ampfm.rpt_AdtVisit.AccountNumber, ampfm.rpt_PatientDemographics.PatientAge, ampfm.rpt_PatientDemographics.PatientAgeGroup,

ampfm.rpt_PatientDemographics.PatientSex,

CASE WHEN PatientAgeGroup = '0 - 14' THEN '0' ELSE

CASE WHEN PatientAgeGroup = '15 - 24' THEN '15' ELSE

CASE WHEN PatientAgeGroup = '25 - 34' THEN '25' ELSE

CASE WHEN PatientAgeGroup = '35 - 44' THEN '35' ELSE

CASE WHEN PatientAgeGroup = '45 - 54' THEN '45' ELSE

CASE WHEN PatientAgeGroup = '55 - 64' THEN '55' ELSE

CASE WHEN PatientAgeGroup = '65 - 74' THEN '65' ELSE

CASE WHEN PatientAgeGroup = '75 - 79' THEN '75' ELSE

CASE WHEN PatientAgeGroup = '80 - OVER' THEN '80'

END END END END END END END END END AS PAGBeginningAge,

CASE WHEN PatientAgeGroup = '0 - 14' THEN '14' ELSE

CASE WHEN PatientAgeGroup = '15 - 24' THEN '24' ELSE

CASE WHEN PatientAgeGroup = '25 - 34' THEN '34' ELSE

CASE WHEN PatientAgeGroup = '35 - 44' THEN '44' ELSE

CASE WHEN PatientAgeGroup = '45 - 54' THEN '54' ELSE

CASE WHEN PatientAgeGroup = '55 - 64' THEN '64' ELSE

CASE WHEN PatientAgeGroup = '65 - 74' THEN '74' ELSE

CASE WHEN PatientAgeGroup = '75 - 79' THEN '79' ELSE

CASE WHEN PatientAgeGroup = '80 - OVER' THEN '200'

END END END END END END END END END AS PAGEndingAge

--WHERE PatientAge NOT BETWEEN PAGBeginningAge AND PAGEndingAge

FROM ampfm.dct_AdmitPriorityType INNER JOIN

ampfm.rpt_AdtVisit ON ampfm.dct_AdmitPriorityType.AdmitPriorityTypeCode = ampfm.rpt_AdtVisit.AdmitPriorityCode INNER JOIN

ampfm.rpt_PatientDemographics ON ampfm.rpt_AdtVisit.RegNum = ampfm.rpt_PatientDemographics.RegNum

Two things:

First, your WHERE statment needs to come after the last ON condition.

second, you are not allowed to alias the "column/expression list" of the SELECT clause like this in MS SQL Server as you can do from DB2; you will need to include the entire case statement in your WHERE clause.

You might be able to create an inline TVF to make this complicated CASE statement a little easier to read.

Maybe something like:

Code Snippet

alter function dbo.ageRange
( @.arg_ageGroup varchar(12)
)
returns table
as
return
( select cast(parsename(replace(@.arg_ageGroup,' - ','.'),2)
as int)
as lowPart,
cast( case when parsename(replace(@.arg_ageGroup,' - ','.'),1)
= 'over'
then '200'
else parsename(replace(@.arg_ageGroup,' - ','.'),1)
end as int)
as highPart
)

go

declare @.ageGroup table ( ageGroup varchar(9))
insert into @.ageGroup
select '0 - 14' union all
select '15 - 24' union all
select '25 - 34' union all
select '35 - 44' union all
select '45 - 54' union all
select '55 - 64' union all
select '65 - 74' union all
select '75 - OVER'

select ageGroup,
lowPart,
highPart
from @.ageGroup
cross apply ageRange(ageGroup)

Another idea would be to put your ranges in a table that included (1) the range, (2) the low and (3) the high of the range. Then you could use a CROSS APPLY to fetch the desired numeric LOW and HIGH of the range.

|||

I can't believe I had the WHERE statement before the FROM statement; boy this has been a long day.

I placed the entire case statement in the WHERE clause and everything is working fine now. Thanks!

BTW, I am not familiar with inline TVF. What is it or where could I read up on it?

|||

( I edited my previous response to include it; please take a look. )

|||

Thanks! I may try something like that on my next project.

sqlsql