[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

worked fine in SQL 2005 and in SQL 2008, I get error

Posted on 2011-04-21
6
Medium Priority
?
262 Views
Last Modified: 2012-06-21
This report worked fine in SQL 2005 and in SQL 2008, I get this error.

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' '.
Msg 137, Level 15, State 1, Line 7
Must declare the scalar variable "@groupby1".
Msg 137, Level 15, State 1, Line 8
Must declare the scalar variable "@groupby2".
Msg 102, Level 15, State 1, Line 138
Incorrect syntax near ' '.
SET NOCOUNT ON

DECLARE
    @groupby1 varchar(60),    
    @groupby2 varchar(60)

SELECT @groupby1 = convert(varchar(60),'None')
SELECT @groupby2 = convert(varchar(60),'None')

SELECT  
                pp.PatientProfileId,
                ISNULL(pp.[First], '') AS [First],
                ISNULL(pp.Middle, '') AS Middle,
                pp.[Last],
                dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName,
                ISNULL(race.Description, 'Unknown') AS Race,
                ISNULL(pp.Sex, '') AS Gender,
                pp.Birthdate,
                ImmunCodes.Code AS ImmunCode,
                ImmunCodes.Description AS ImmunDescription,
                ISNULL(ImmunCats.Category,CAST(ImmunCodes.CatCode AS VARCHAR(2)) + ' (undefined in cusImmunCategories)') AS Category,
                pvp.DateOfServiceFrom AS ImmunDte,
                pvp.Code AS ProcCode,
                pvp.CPTCode AS ProcCPTCode,
                pvp.Description AS ProcDescription,
                dbo.cusCETfCalcAge(pp.Birthdate, pvp.DateOfServiceFrom) AS AgeYears,
                dbo.cusCalcAgeMonths(pp.Birthdate, pvp.DateOfServiceFrom) AS AgeMonths,
--             dbo.cusCalcAgeYearsMonths(AgeYears, AgeMonths) AS AgeAtImmun,
                ImmunCats.ListOrder,
                pv.PatientVisitId,
                pv.TicketNumber,
                pvp.PatientVisitProcsId,
                cipv.cusImmunPatVisitID AS ImmunID,
                ImmunCodes.cusImmunCodesID,
                f.ListName AS FacilityName,
                pv.FacilityID,
                d.ListName AS Doctor,
                RTRIM(pvp.Code) + ' - ' + ImmunCodes.Description AS Description,
                ISNULL(pv.PrimaryInsuranceCarriersId,0)AS PrimaryInsuranceCarriersId,
                ISNULL(ic.ListName,'Self Pay') AS PrimaryInsuranceCarrier,
                ISNULL(pv.FinancialClassMId,0) AS FinancialClassMId, 
                ISNULL(fc.Description,  'Unknown') AS FinancialClass, 
                ISNULL(pt.Description, 'No Policy Type') AS PolicyType,
                case @groupby1
                                when 'Facility' then f.ListName
                                when 'Provider' then d.ListName
                                when 'Insurance' then ISNULL(ic.ListName,'Self Pay') 
                                when 'Policy Type' then ISNULL(pt.Description, 'No Policy Type') 
                                when 'Financial Class' then ISNULL(fc.Description,  'Unknown')
                                when 'Immunization Category' then ImmunCats.Category
                                when 'Patient Race' then ISNULL(race.Description, 'Unknown') 
                                when 'Patient Name' then dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix)
                                else 'None'
                end as Group1,
                case @groupby2
                                when 'Facility' then f.ListName
                                when 'Provider' then d.ListName
                                when 'Insurance' then ISNULL(ic.ListName,'Self Pay') 
                                when 'Policy Type' then ISNULL(pt.Description, 'No Policy Type') 
                                when 'Financial Class' then ISNULL(fc.Description,  'Unknown')
                                when 'Immunization Category' then ImmunCats.Category
                                when 'Patient Race' then ISNULL(race.Description, 'Unknown') 
                                when 'Patient Name' then dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix)
                                else 'None'
                end as Group2
INTO #Tmp

FROM    
                PatientVisitProcs pvp
                INNER JOIN cusImmunCodes ImmunCodes ON pvp.CPTCode = ImmunCodes.Code
                LEFT OUTER JOIN cusImmunCategories ImmunCats ON ImmunCodes.CatCode = ImmunCats.CatCode
                INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
                INNER JOIN DoctorFacility f ON pv.FacilityID = f.DoctorFacilityID
                INNER JOIN DoctorFacility d ON pv.DoctorID = d.DoctorFacilityID
                INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
                LEFT JOIN Medlists race ON pp.RaceMId = race.MedListsId
                LEFT OUTER JOIN cusImmunPatVisit cipv ON pvp.PatientVisitProcsId = cipv.PatientVisitProcsID
                LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID 
                LEFT OUTER JOIN MedLists fc ON pv.FinancialClassMID = fc.MedListsID 
                LEFT OUTER JOIN Medlists pt ON ic.PolicyTypeMID = pt.MedlistsID

WHERE 
                pvp.DateofServiceFrom   >= ISNULL('01/01/2010','1/1/1900') AND pvp.DateofServiceFrom  < dateadd(day,1,ISNULL('12/31/2010','1/1/3000')) 
                

SELECT * FROM #Tmp

WHERE                 
    --Filter on Age
    (
    ([AgeYears] >= ('-1') AND [AgeYears] <= ('1'))     
    )

ORDER BY 
                TicketNumber

Open in new window

0
Comment
Question by:Jeff S
6 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 35440703
try replacing lines 3-4 with

DECLARE    @groupby1 varchar(60)    
DECLARE    @groupby2 varchar(60)
0
 
LVL 7

Author Comment

by:Jeff S
ID: 35440739
HainKurt -

Same Error.
0
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 668 total points
ID: 35440874
I think you have some invalid chars in your script.  Try copying the version that is posted on your question and running it on your end.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 668 total points
ID: 35441130
try it like this
SET NOCOUNT ON

DECLARE @groupby1 varchar(60)
DECLARE @groupby2 varchar(60)

SET @groupby1 = 'None'
SET @groupby2 = 'None'

SELECT  
                pp.PatientProfileId,
                ISNULL(pp.[First], '') AS [First],
                ISNULL(pp.Middle, '') AS Middle,
                pp.[Last],
                dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName,
                ISNULL(race.Description, 'Unknown') AS Race,
                ISNULL(pp.Sex, '') AS Gender,
                pp.Birthdate,
                ImmunCodes.Code AS ImmunCode,
                ImmunCodes.Description AS ImmunDescription,
                ISNULL(ImmunCats.Category,CAST(ImmunCodes.CatCode AS VARCHAR(2)) + ' (undefined in cusImmunCategories)') AS Category,
                pvp.DateOfServiceFrom AS ImmunDte,
                pvp.Code AS ProcCode,
                pvp.CPTCode AS ProcCPTCode,
                pvp.Description AS ProcDescription,
                dbo.cusCETfCalcAge(pp.Birthdate, pvp.DateOfServiceFrom) AS AgeYears,
                dbo.cusCalcAgeMonths(pp.Birthdate, pvp.DateOfServiceFrom) AS AgeMonths,
--             dbo.cusCalcAgeYearsMonths(AgeYears, AgeMonths) AS AgeAtImmun,
                ImmunCats.ListOrder,
                pv.PatientVisitId,
                pv.TicketNumber,
                pvp.PatientVisitProcsId,
                cipv.cusImmunPatVisitID AS ImmunID,
                ImmunCodes.cusImmunCodesID,
                f.ListName AS FacilityName,
                pv.FacilityID,
                d.ListName AS Doctor,
                RTRIM(pvp.Code) + ' - ' + ImmunCodes.Description AS Description,
                ISNULL(pv.PrimaryInsuranceCarriersId,0)AS PrimaryInsuranceCarriersId,
                ISNULL(ic.ListName,'Self Pay') AS PrimaryInsuranceCarrier,
                ISNULL(pv.FinancialClassMId,0) AS FinancialClassMId, 
                ISNULL(fc.Description,  'Unknown') AS FinancialClass, 
                ISNULL(pt.Description, 'No Policy Type') AS PolicyType,
                case @groupby1
                                when 'Facility' then f.ListName
                                when 'Provider' then d.ListName
                                when 'Insurance' then ISNULL(ic.ListName,'Self Pay') 
                                when 'Policy Type' then ISNULL(pt.Description, 'No Policy Type') 
                                when 'Financial Class' then ISNULL(fc.Description,  'Unknown')
                                when 'Immunization Category' then ImmunCats.Category
                                when 'Patient Race' then ISNULL(race.Description, 'Unknown') 
                                when 'Patient Name' then dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix)
                                else 'None'
                end as Group1,
                case @groupby2
                                when 'Facility' then f.ListName
                                when 'Provider' then d.ListName
                                when 'Insurance' then ISNULL(ic.ListName,'Self Pay') 
                                when 'Policy Type' then ISNULL(pt.Description, 'No Policy Type') 
                                when 'Financial Class' then ISNULL(fc.Description,  'Unknown')
                                when 'Immunization Category' then ImmunCats.Category
                                when 'Patient Race' then ISNULL(race.Description, 'Unknown') 
                                when 'Patient Name' then dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix)
                                else 'None'
                end as Group2
INTO #Tmp

FROM    
                PatientVisitProcs pvp
                INNER JOIN cusImmunCodes ImmunCodes ON pvp.CPTCode = ImmunCodes.Code
                LEFT OUTER JOIN cusImmunCategories ImmunCats ON ImmunCodes.CatCode = ImmunCats.CatCode
                INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
                INNER JOIN DoctorFacility f ON pv.FacilityID = f.DoctorFacilityID
                INNER JOIN DoctorFacility d ON pv.DoctorID = d.DoctorFacilityID
                INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
                LEFT JOIN Medlists race ON pp.RaceMId = race.MedListsId
                LEFT OUTER JOIN cusImmunPatVisit cipv ON pvp.PatientVisitProcsId = cipv.PatientVisitProcsID
                LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID 
                LEFT OUTER JOIN MedLists fc ON pv.FinancialClassMID = fc.MedListsID 
                LEFT OUTER JOIN Medlists pt ON ic.PolicyTypeMID = pt.MedlistsID

WHERE 
                pvp.DateofServiceFrom   >= ISNULL('01/01/2010','1/1/1900') AND pvp.DateofServiceFrom  < dateadd(day,1,ISNULL('12/31/2010','1/1/3000')) 
                

SELECT * FROM #Tmp

WHERE                 
    --Filter on Age
    (
    ([AgeYears] >= ('-1') AND [AgeYears] <= ('1'))     
    )

ORDER BY 
                TicketNumber

Open in new window

0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 664 total points
ID: 35442516
copy ypur code into a text file save attach here in a new post...
close file, open file copy the code, paste into your editor and run again... maybe copy paste in/out to notepad removes some invalid characters :)
0
 
LVL 7

Author Comment

by:Jeff S
ID: 35700371
I have not forgotten this ... I will try the examples and report back.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question