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