We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
276 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

Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try replacing lines 3-4 with

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

Author

Commented:
HainKurt -

Same Error.
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I have not forgotten this ... I will try the examples and report back.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.