September 2, 2014 at 10:19 am
Hi, I normally just do Report writing so this is outside of my normal day to day. My goal is to run a script on the db that checks for the version of the database and then Creates the appropriate view. I don't know the best way to do this or how to do it because I keep getting syntax errors. Any guidance is appreciated.
Here is some pseudo code I have tried.
IF dbo.cusGetCPSVersion()='Apples'
BEGIN
CREATE VIEW APPLES AS
SELECT * FROM APPLES_FRUIT
END
ELSE
BEGIN
CREATE VIEW ORANGES AS
SELECT * FROM ORANGES_FRUIT
END
***SQL born on date Spring 2013:-)
September 2, 2014 at 10:37 am
thomashohner (9/2/2014)
Hi, I normally just do Report writing so this is outside of my normal day to day. My goal is to run a script on the db that checks for the version of the database and then Creates the appropriate view. I don't know the best way to do this or how to do it because I keep getting syntax errors. Any guidance is appreciated.Here is some pseudo code I have tried.
IF dbo.cusGetCPSVersion()='Apples'
BEGIN
CREATE VIEW APPLES AS
SELECT * FROM APPLES_FRUIT
END
ELSE
BEGIN
CREATE VIEW ORANGES AS
SELECT * FROM ORANGES_FRUIT
END
It is pretty tough to help with syntax errors unless you post the actual code you are running. I am guessing here but I suspect it is your first line that is the problem. Is dbo.cusGetCPSVersion() a scalar function?
Something like this:
if (select dbo.cusGetCPSVersion()) = 'Apples'
--do something
else
--do something else
Now the additional issue is you are trying to wrap ddl inside of a conditional which isn't going to work either. A view MUST be the only statement in a batch. That means you would have to wrap your create view code in dynamic sql for this to work. Surely there is a simpler solution to what you are trying to here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 2, 2014 at 10:41 am
That's exactly what I'm getting is the Create View must be the only statement in the batch. I wrote a series of reports. That needs to span 3 different databases for different versions of the application. I need 3 views because on each version they have different tables that contain the same info. Its a nightmare. The only want one install package for customers to download. So I need a script that will create the appropriate view with out erroring out when the others wont work on that version.
I also tried BEGIN TRY but I get the same thing
***SQL born on date Spring 2013:-)
September 2, 2014 at 11:00 am
This perhaps?
IF dbo.cusGetCPSVersion()='Apples'
BEGIN
exec ('CREATE VIEW APPLES AS SELECT * FROM APPLES_FRUIT')
END
ELSE
BEGIN
exec ('CREATE VIEW ORANGES AS SELECT * FROM ORANGES_FRUIT')
END
September 2, 2014 at 11:09 am
I thought that was going to work Lynn, darn it. I have some Case WHEN Col1.code IN ('A','B','C') that's throwing it off.
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cusUDSPatientRaceEthnicityCPS12_2014]'))
DROP VIEW [dbo].[cusUDSPatientRaceEthnicityCPS12_2014]
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cusUDSPatientRaceEthnicityCPS1_2014]'))
DROP VIEW [dbo].[cusUDSPatientRaceEthnicityCPS1_2014]
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cusUDSPatientRaceEthnicityCPS2_2014]'))
DROP VIEW [dbo].[cusUDSPatientRaceEthnicityCPS2_2014]
GO
IF dbo.cusGetCPSVersion() >= '12.00'
BEGIN
/**Create View for CPS version 12 or Greater**/
('CREATE VIEW cusUDSPatientRaceEthnicityCPS12_2014 AS
WITH Race_CTE
AS
(
SELECT DISTINCT
pp.PatientProfileID
,pp.PatientId
,pp.PId
,PatientRaceDescription = Race.[Description]
,PatientRaceSubCategoryDescription = Rsub.[Description]
,RaceMID1 = Race.MedListsId
,Race.Code AS RacePrimary
,Rsub.code AS RaceSub
,RaceMID2 = Rsub.MedListsId
,RowNumber = ROW_NUMBER( )OVER( PARTITION BY pp.PatientProfileID ORDER BY pr.LastModified DESC)
,RaceCode= CASE WHEN Race.Code IN ('A', 'NH', 'OPI', 'B', 'AI','W') AND rSub.Code IN ('A', 'NH', 'OPI', 'B', 'AI','W') AND Race.Code <> Rsub.Code THEN 'M'
WHEN Race.Code IN ('A', 'NH', 'OPI', 'B', 'AI','W','M') THEN Race.Code
WHEN Rsub.Code IN ('A', 'NH', 'OPI', 'B', 'AI','W','M') THEN Rsub.Code
ELSE 'U'
END
,PatientRace = CASE WHEN Race.Code IN ('A', 'NH', 'OPI', 'B', 'AI','W') AND Rsub.Code IN ('A', 'NH', 'OPI', 'B', 'AI','W') AND race.Code <> Rsub.Code THEN 'MultiRacial'
ELSE COALESCE( Rsub.[description] , Race.[description],'Unreported/Refused to report' )
END
,EthnicityCode = ISNULL(eth.Code, 'NR')
,Ethnicity = ISNULL(eth.[Description],'Not Reported')
,UDSEthnicityCode = CASE WHEN eth.Code = 'H' THEN 'H' ELSE 'N' END
,UDSEthnicityLine = CASE WHEN eth.Code = 'H' THEN '1. Hispanic/Latino'
ELSE '2. Not Hispanic or Latino' END
,PatientName = RTRIM(RTRIM(ISNULL(pp.[Last], '') + ' ' + ISNULL(pp.Suffix, '')) + ', ' + ISNULL(pp.[First], '') + ' ' + ISNULL(pp.Middle, ''))
,pp.FacilityId
,Zip = LEFT(ISNULL(pp.Zip, 'Other'),5)
,UDSGender = CASE WHEN pp.Sex IN ('M','F') THEN pp.SEX ELSE 'U' END
,UDSGenderID = CASE WHEN pp.Sex ='M' THEN 1
WHEN pp.Sex ='F' THEN 2
ELSE 3 END
,BirthDate = ISNULL(pp.Birthdate, '01/01/1900')
,LanguageID = ISNULL(l.LanguageId,0)
,PatientPrefLanguage = ISNULL(l.ShortDescription,'None')
,FacilityName = ISNULL(fv.ListName, 'No Facility')
,AgWrkrStatusID = ISNULL(cri.AgWrkrStatusID,0)
,Translation = ISNULL(Cri.Translation,0)
,AgrWrkStatus = ISNULL(cml4c.Description, 'Unknown')
,MigrantCode = ISNULL(cml4c.code,'U')
,MigrantStatusId = CASE WHEN cml4c.Description LIKE 'Non%' THEN 1
WHEN cml4c.Description LIKE 'Seas%' THEN 2
WHEN cml4c.Description LIKE 'Migr%' THEN 3
WHEN cml4c.Description LIKE 'Emplo%' THEN 4
WHEN cml4c.Description LIKE 'Retired%' THEN 5
ELSE 0 END
FROM
PatientProfile pp
LEFT JOIN PatientRace pr ON pp.PatientProfileId = pr.PatientProfileId
LEFT JOIN MedLists eth ON pp.EthnicityMId = eth.MedlistsId
LEFT JOIN MedLists Race ON pr.PatientRaceMID = Race.MedListsID
LEFT JOIN MedLists Rsub ON pr.PatientRaceSubCategoryMID = Rsub.MedListsId
LEFT JOIN Language l ON pp.LanguageId = l.LanguageId
LEFT JOIN cusCRIInterview cri ON CRI.PatientProfileID = pp.PatientProfileId
LEFT JOIN cusCRIMedLists cml4c ON cri.AgWrkrStatusID = cml4c.MedListsId
LEFT JOIN DoctorFacility fv ON fv.DoctorFacilityId = pp.FacilityId
)
SELECT
r.PatientProfileID
,r.PatientId
,r.PId
,r.BirthDate
,r.PatientName
,r.UDSGender
,r.UDSGenderID
,r.LanguageID
,r.Zip
,RaceCode = ISNULL(r.RaceCode, 'U')
,Race = ISNULL(r.PatientRace,'Unreported/Refused to report')
,UDSRaceLine = CASE r.RaceCode WHEN 'A' THEN '1. Asian'
WHEN 'NH' THEN '2a. Native Hawaiian'
WHEN 'OPI' THEN '2b. Other Pacific Islander'
WHEN 'B' THEN '3. Black/African American'
WHEN 'AI' THEN '4. American Indian/Alaskan Native'
WHEN 'W' THEN '5. White'
WHEN 'M' THEN '6. More than one race'
WHEN 'U' THEN '7. Unreported/Refused to report'
ELSE '7. Unreported/Refused to report' END
,r.EthnicityCode
,r.Ethnicity
,r.UDSEthnicityLine
,r.UDSEthnicityCode
,r.PatientPrefLanguage
,r.FacilityName
,r.AgWrkrStatusID
,r.Translation
,r.AgrWrkStatus
,r.MigrantCode
,r.MigrantStatusId
,FacilityId = ISNULL(r.FacilityId,0)
FROM Race_CTE r
WHERE r.RowNumber =1;')
END
ELSE
IF EXISTS(SELECT * FROM sys.columns WHERE object_id=object_id('PatientProfile') AND name='EthnicityMId') AND dbo.cusGetCPSVersion() < '12.00'
BEGIN
('CREATE VIEW cusUDSPatientRaceEthnicityCPS1_2014 AS
/**** CREATE View for CPS versions before 12 WHERE SELECT * FROM sys.columns where object_id=object_id('PatientProfile') and name='EthnicityMId'******/
SELECT
pp.PatientProfileId
,PID = pp.Pid
,PatientId = pp.PatientId
,Birthdate = ISNULL(pp.Birthdate, '01/01/1900')
,PatientName = RTRIM(RTRIM(ISNULL(pp.[Last], '') + ' ' + ISNULL(pp.Suffix, '')) + ', ' + ISNULL(pp.[First], '') + ' ' + ISNULL(pp.Middle, ''))
,UDSGender = CASE WHEN pp.Sex IN ('M','F') THEN pp.SEX ELSE 'U' END
,UDSGenderID = CASE WHEN pp.Sex ='M' THEN 1
WHEN pp.Sex ='F' THEN 2
ELSE 3 END
,LanguageID = ISNULL(pp.PrefLanguageMId,0)
,Zip = LEFT(ISNULL(pp.Zip, 'Other'),5)
,RaceCode = ISNULL(r.Code, 'U')
,Race = ISNULL(r.description, 'Unknown')
,UDSRaceLine = CASE WHEN r.Code = 'A' THEN '1. Asian'
WHEN r.Code = 'NH' THEN '2a. Native Hawaiian'
WHEN r.Code = 'OPI' THEN '2b. Other Pacific Islander'
WHEN r.Code = 'B' THEN '3. Black/African American'
WHEN r.Code = 'AI' THEN '4. American Indian/Alaskan Native'
WHEN r.Code = 'W' THEN '5. White'
WHEN r.Code = 'M' THEN '6. More than one race'
WHEN r.Code = 'U' THEN '7. Unreported/Refused to report'
ELSE '7. Unreported/Refused to report' END
,EthnicityCode = ISNULL(eth.Code, 'NR')
,Ethnicity = CASE WHEN eth.Code = 'H' OR r.Code = 'H' THEN 'Hispanic/Latino'
WHEN eth.Code IS NULL or eth.Code = 'NR' THEN 'Not Reported'
ELSE 'All Others' END
,UDSEthnicityLine= CASE WHEN eth.Code = 'H' OR r.Code = 'H' THEN '1. Hispanic/Latino'
ELSE '2.Not Hispanic or Latino ' END
,UDSEthnicityCode = CASE WHEN eth.Code = 'H' OR r.Code = 'H' THEN 'H' ELSE 'N' END
,PatientPrefLanguage = ISNULL(lang.Description, 'None')
,FacilityName = ISNULL(fv.ListName, 'No Facility')
,FacilityId = ISNULL(pp.FacilityId,0)
,AgWrkrStatusID = ISNULL(cri.AgWrkrStatusID,0)
,Translation = ISNULL(Cri.Translation,0)
,AgrWrkStatus = ISNULL(cml4c.Description, 'Unknown')
,MigrantCode = ISNULL(cml4c.code,'U')
,MigrantStatusId = CASE WHEN cml4c.Description LIKE 'Non%' THEN 1
WHEN cml4c.Description LIKE 'Seas%' THEN 2
WHEN cml4c.Description LIKE 'Migr%' THEN 3
WHEN cml4c.Description LIKE 'Emplo%' THEN 4
WHEN cml4c.Description LIKE 'Retired%' THEN 5
ELSE 0 END
FROM
PatientProfile pp
LEFT JOIN Medlists r ON pp.RaceMId=r.MedlistsId
LEFT JOIN MedLists lang ON pp.PrefLanguageMId = lang.MedListsID
LEFT JOIN MedLists eth on pp.EthnicityMId = eth.MedlistsId
LEFT JOIN cusCRIInterview cri ON CRI.PatientProfileID = pp.PatientProfileId
LEFT JOIN cusCRIMedLists cml4c ON cri.AgWrkrStatusID = cml4c.MedListsId
LEFT JOIN DoctorFacility fv ON fv.DoctorFacilityId = pp.FacilityId;')
END
ELSE
BEGIN
/**** CREATE View for CPS versions before 12 WHERE DOES NOT EXIST (SELECT * FROM sys.columns where object_id=object_id('PatientProfile') and name='EthnicityMId'******/
('CREATE VIEW cusUDSPatientRaceEthnicityCPS2_2014 AS
SELECT
pp.PatientProfileId
,PID = pp.Pid
,PatientId = pp.PatientId
,Birthdate = ISNULL(pp.Birthdate, '01/01/1900')
,PatientName = RTRIM(RTRIM(ISNULL(pp.[Last], '') + ' ' + ISNULL(pp.Suffix, '')) + ', ' + ISNULL(pp.[First], '') + ' ' + ISNULL(pp.Middle, ''))
,UDSGender = CASE WHEN pp.Sex IN ('M','F') THEN pp.SEX ELSE 'U' END
,UDSGenderID = CASE WHEN pp.Sex ='M' THEN 1
WHEN pp.Sex ='F' THEN 2
ELSE 3 END
,LanguageID = ISNULL(pp.PrefLanguageMId,0)
,Zip = LEFT(ISNULL(pp.Zip, 'Other'),5)
,RaceCode = ISNULL(r.Code, 'U')
,Race = ISNULL(r.description, 'Unknown')
,UDSRaceLine = CASE WHEN r.Code = 'A' THEN '1. Asian'
WHEN r.Code = 'NH' THEN '2a. Native Hawaiian'
WHEN r.Code = 'OPI' THEN '2b. Other Pacific Islander'
WHEN r.Code = 'B' THEN '3. Black/African American'
WHEN r.Code = 'AI' THEN '4. American Indian/Alaskan Native'
WHEN r.Code = 'W' THEN '5. White'
WHEN r.Code = 'M' THEN '6. More than one race'
WHEN r.Code = 'U' THEN '7. Unreported/Refused to report'
ELSE '7. Unreported/Refused to report' END
,EthnicityCode = ISNULL(eth.Code, 'NR')
,Ethnicity = CASE WHEN eth.Code = 'H' OR r.Code = 'H' THEN 'Hispanic/Latino'
WHEN eth.Code IS NULL or eth.Code = 'NR' THEN 'Not Reported'
ELSE 'All Others' END
,UDSEthnicityLine= CASE WHEN eth.Code = 'H' OR r.Code = 'H' THEN '1. Hispanic/Latino'
ELSE '2.Not Hispanic or Latino ' END
,UDSEthnicityCode = CASE WHEN eth.Code = 'H' OR r.Code = 'H' THEN 'H' ELSE 'N' END
,PatientPrefLanguage = ISNULL(lang.Description, 'None')
,FacilityName = ISNULL(fv.ListName, 'No Facility')
,FacilityId = ISNULL(pp.FacilityId,0)
,AgWrkrStatusID = ISNULL(cri.AgWrkrStatusID,0)
,Translation = ISNULL(Cri.Translation,0)
,AgrWrkStatus = ISNULL(cml4c.Description, 'Unknown')
,MigrantCode = ISNULL(cml4c.code,'U')
,MigrantStatusId = CASE WHEN cml4c.Description LIKE 'Non%' THEN 1
WHEN cml4c.Description LIKE 'Seas%' THEN 2
WHEN cml4c.Description LIKE 'Migr%' THEN 3
WHEN cml4c.Description LIKE 'Emplo%' THEN 4
WHEN cml4c.Description LIKE 'Retired%' THEN 5
ELSE 0 END
FROM
PatientProfile pp
LEFT JOIN Medlists r ON pp.RaceMId=r.MedlistsId
LEFT JOIN MedLists lang ON pp.PrefLanguageMId = lang.MedListsID
LEFT JOIN cusCRIInterview cri1 on pp.patientprofileid = cri1.patientprofileid
LEFT JOIN cusCRIMedLists eth ON cri1.RaceMID2 = eth.MedListsId
LEFT JOIN cusCRIInterview cri ON CRI.PatientProfileID = pp.PatientProfileId
LEFT JOIN cusCRIMedLists cml4c ON cri.AgWrkrStatusID = cml4c.MedListsId
LEFT JOIN DoctorFacility fv ON fv.DoctorFacilityId = pp.FacilityId;')
END
GO [/sql]
***SQL born on date Spring 2013:-)
September 2, 2014 at 11:32 am
Try this:
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cusUDSPatientRaceEthnicityCPS12_2014]'))
DROP VIEW [dbo].[cusUDSPatientRaceEthnicityCPS12_2014]
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cusUDSPatientRaceEthnicityCPS1_2014]'))
DROP VIEW [dbo].[cusUDSPatientRaceEthnicityCPS1_2014]
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cusUDSPatientRaceEthnicityCPS2_2014]'))
DROP VIEW [dbo].[cusUDSPatientRaceEthnicityCPS2_2014]
GO
IF dbo.cusGetCPSVersion() >= '12.00'
BEGIN
/**Create View for CPS version 12 or Greater**/
exec('
CREATE VIEW cusUDSPatientRaceEthnicityCPS12_2014 AS
WITH Race_CTE
AS
(
SELECT DISTINCT
pp.PatientProfileID
,pp.PatientId
,pp.PId
,PatientRaceDescription = Race.[Description]
,PatientRaceSubCategoryDescription = Rsub.[Description]
,RaceMID1 = Race.MedListsId
,Race.Code AS RacePrimary
,Rsub.code AS RaceSub
,RaceMID2 = Rsub.MedListsId
,RowNumber = ROW_NUMBER( )OVER( PARTITION BY pp.PatientProfileID ORDER BY pr.LastModified DESC)
,RaceCode= CASE WHEN Race.Code IN (''A'', ''NH'', ''OPI'', ''B'', ''AI'',''W'') AND rSub.Code IN (''A'', ''NH'', ''OPI'', ''B'', ''AI'',''W'') AND Race.Code <> Rsub.Code THEN ''M''
WHEN Race.Code IN (''A'', ''NH'', ''OPI'', ''B'', ''AI'',''W'',''M'') THEN Race.Code
WHEN Rsub.Code IN (''A'', ''NH'', ''OPI'', ''B'', ''AI'',''W'',''M'') THEN Rsub.Code
ELSE ''U''
END
,PatientRace = CASE WHEN Race.Code IN (''A'', ''NH'', ''OPI'', ''B'', ''AI'',''W'') AND Rsub.Code IN (''A'', ''NH'', ''OPI'', ''B'', ''AI'',''W'') AND race.Code <> Rsub.Code THEN ''MultiRacial''
ELSE COALESCE( Rsub.[description] , Race.[description],''Unreported/Refused to report'' )
END
,EthnicityCode = ISNULL(eth.Code, ''NR'')
,Ethnicity = ISNULL(eth.[Description],''Not Reported'')
,UDSEthnicityCode = CASE WHEN eth.Code = ''H'' THEN ''H'' ELSE ''N'' END
,UDSEthnicityLine = CASE WHEN eth.Code = ''H'' THEN ''1. Hispanic/Latino''
ELSE ''2. Not Hispanic or Latino'' END
,PatientName = RTRIM(RTRIM(ISNULL(pp.[Last], '''') + '' '' + ISNULL(pp.Suffix, '''')) + '', '' + ISNULL(pp.[First], '''') + '' '' + ISNULL(pp.Middle, ''''))
,pp.FacilityId
,Zip = LEFT(ISNULL(pp.Zip, ''Other''),5)
,UDSGender = CASE WHEN pp.Sex IN (''M'',''F'') THEN pp.SEX ELSE ''U'' END
,UDSGenderID = CASE WHEN pp.Sex =''M'' THEN 1
WHEN pp.Sex =''F'' THEN 2
ELSE 3 END
,BirthDate = ISNULL(pp.Birthdate, ''01/01/1900'')
,LanguageID = ISNULL(l.LanguageId,0)
,PatientPrefLanguage = ISNULL(l.ShortDescription,''None'')
,FacilityName = ISNULL(fv.ListName, ''No Facility'')
,AgWrkrStatusID = ISNULL(cri.AgWrkrStatusID,0)
,Translation = ISNULL(Cri.Translation,0)
,AgrWrkStatus = ISNULL(cml4c.Description, ''Unknown'')
,MigrantCode = ISNULL(cml4c.code,''U'')
,MigrantStatusId = CASE WHEN cml4c.Description LIKE ''Non%'' THEN 1
WHEN cml4c.Description LIKE ''Seas%'' THEN 2
WHEN cml4c.Description LIKE ''Migr%'' THEN 3
WHEN cml4c.Description LIKE ''Emplo%'' THEN 4
WHEN cml4c.Description LIKE ''Retired%'' THEN 5
ELSE 0 END
FROM
PatientProfile pp
LEFT JOIN PatientRace pr ON pp.PatientProfileId = pr.PatientProfileId
LEFT JOIN MedLists eth ON pp.EthnicityMId = eth.MedlistsId
LEFT JOIN MedLists Race ON pr.PatientRaceMID = Race.MedListsID
LEFT JOIN MedLists Rsub ON pr.PatientRaceSubCategoryMID = Rsub.MedListsId
LEFT JOIN Language l ON pp.LanguageId = l.LanguageId
LEFT JOIN cusCRIInterview cri ON CRI.PatientProfileID = pp.PatientProfileId
LEFT JOIN cusCRIMedLists cml4c ON cri.AgWrkrStatusID = cml4c.MedListsId
LEFT JOIN DoctorFacility fv ON fv.DoctorFacilityId = pp.FacilityId
)
SELECT
r.PatientProfileID
,r.PatientId
,r.PId
,r.BirthDate
,r.PatientName
,r.UDSGender
,r.UDSGenderID
,r.LanguageID
,r.Zip
,RaceCode = ISNULL(r.RaceCode, ''U'')
,Race = ISNULL(r.PatientRace,''Unreported/Refused to report'')
,UDSRaceLine = CASE r.RaceCode WHEN ''A'' THEN ''1. Asian''
WHEN ''NH'' THEN ''2a. Native Hawaiian''
WHEN ''OPI'' THEN ''2b. Other Pacific Islander''
WHEN ''B'' THEN ''3. Black/African American''
WHEN ''AI'' THEN ''4. American Indian/Alaskan Native''
WHEN ''W'' THEN ''5. White''
WHEN ''M'' THEN ''6. More than one race''
WHEN ''U'' THEN ''7. Unreported/Refused to report''
ELSE ''7. Unreported/Refused to report'' END
,r.EthnicityCode
,r.Ethnicity
,r.UDSEthnicityLine
,r.UDSEthnicityCode
,r.PatientPrefLanguage
,r.FacilityName
,r.AgWrkrStatusID
,r.Translation
,r.AgrWrkStatus
,r.MigrantCode
,r.MigrantStatusId
,FacilityId = ISNULL(r.FacilityId,0)
FROM Race_CTE r
WHERE r.RowNumber =1;
')
END
ELSE
IF EXISTS(SELECT * FROM sys.columns WHERE object_id=object_id('PatientProfile') AND name='EthnicityMId') AND dbo.cusGetCPSVersion() < '12.00'
BEGIN
exec('
CREATE VIEW cusUDSPatientRaceEthnicityCPS1_2014 AS
/**** CREATE View for CPS versions before 12 WHERE SELECT * FROM sys.columns where object_id=object_id(''PatientProfile'') and name=''EthnicityMId''******/
SELECT
pp.PatientProfileId
,PID = pp.Pid
,PatientId = pp.PatientId
,Birthdate = ISNULL(pp.Birthdate, ''01/01/1900'')
,PatientName = RTRIM(RTRIM(ISNULL(pp.[Last], '''') + '' '' + ISNULL(pp.Suffix, '''')) + '', '' + ISNULL(pp.[First], '''') + '' '' + ISNULL(pp.Middle, ''''))
,UDSGender = CASE WHEN pp.Sex IN (''M'',''F'') THEN pp.SEX ELSE ''U'' END
,UDSGenderID = CASE WHEN pp.Sex =''M'' THEN 1
WHEN pp.Sex =''F'' THEN 2
ELSE 3 END
,LanguageID = ISNULL(pp.PrefLanguageMId,0)
,Zip = LEFT(ISNULL(pp.Zip, ''Other''),5)
,RaceCode = ISNULL(r.Code, ''U'')
,Race = ISNULL(r.description, ''Unknown'')
,UDSRaceLine = CASE WHEN r.Code = ''A'' THEN ''1. Asian''
WHEN r.Code = ''NH'' THEN ''2a. Native Hawaiian''
WHEN r.Code = ''OPI'' THEN ''2b. Other Pacific Islander''
WHEN r.Code = ''B'' THEN ''3. Black/African American''
WHEN r.Code = ''AI'' THEN ''4. American Indian/Alaskan Native''
WHEN r.Code = ''W'' THEN ''5. White''
WHEN r.Code = ''M'' THEN ''6. More than one race''
WHEN r.Code = ''U'' THEN ''7. Unreported/Refused to report''
ELSE ''7. Unreported/Refused to report'' END
,EthnicityCode = ISNULL(eth.Code, ''NR'')
,Ethnicity = CASE WHEN eth.Code = ''H'' OR r.Code = ''H'' THEN ''Hispanic/Latino''
WHEN eth.Code IS NULL or eth.Code = ''NR'' THEN ''Not Reported''
ELSE ''All Others'' END
,UDSEthnicityLine= CASE WHEN eth.Code = ''H'' OR r.Code = ''H'' THEN ''1. Hispanic/Latino''
ELSE ''2.Not Hispanic or Latino '' END
,UDSEthnicityCode = CASE WHEN eth.Code = ''H'' OR r.Code = ''H'' THEN ''H'' ELSE ''N'' END
,PatientPrefLanguage = ISNULL(lang.Description, ''None'')
,FacilityName = ISNULL(fv.ListName, ''No Facility'')
,FacilityId = ISNULL(pp.FacilityId,0)
,AgWrkrStatusID = ISNULL(cri.AgWrkrStatusID,0)
,Translation = ISNULL(Cri.Translation,0)
,AgrWrkStatus = ISNULL(cml4c.Description, ''Unknown'')
,MigrantCode = ISNULL(cml4c.code,''U'')
,MigrantStatusId = CASE WHEN cml4c.Description LIKE ''Non%'' THEN 1
WHEN cml4c.Description LIKE ''Seas%'' THEN 2
WHEN cml4c.Description LIKE ''Migr%'' THEN 3
WHEN cml4c.Description LIKE ''Emplo%'' THEN 4
WHEN cml4c.Description LIKE ''Retired%'' THEN 5
ELSE 0 END
FROM
PatientProfile pp
LEFT JOIN Medlists r ON pp.RaceMId=r.MedlistsId
LEFT JOIN MedLists lang ON pp.PrefLanguageMId = lang.MedListsID
LEFT JOIN MedLists eth on pp.EthnicityMId = eth.MedlistsId
LEFT JOIN cusCRIInterview cri ON CRI.PatientProfileID = pp.PatientProfileId
LEFT JOIN cusCRIMedLists cml4c ON cri.AgWrkrStatusID = cml4c.MedListsId
LEFT JOIN DoctorFacility fv ON fv.DoctorFacilityId = pp.FacilityId;
')
END
ELSE
BEGIN
/**** CREATE View for CPS versions before 12 WHERE DOES NOT EXIST (SELECT * FROM sys.columns where object_id=object_id('PatientProfile') and name='EthnicityMId'******/
exec('
CREATE VIEW cusUDSPatientRaceEthnicityCPS2_2014 AS
SELECT
pp.PatientProfileId
,PID = pp.Pid
,PatientId = pp.PatientId
,Birthdate = ISNULL(pp.Birthdate, ''01/01/1900'')
,PatientName = RTRIM(RTRIM(ISNULL(pp.[Last], '''') + '' '' + ISNULL(pp.Suffix, '''')) + '', '' + ISNULL(pp.[First], '''') + '' '' + ISNULL(pp.Middle, ''''))
,UDSGender = CASE WHEN pp.Sex IN (''M'',''F'') THEN pp.SEX ELSE ''U'' END
,UDSGenderID = CASE WHEN pp.Sex =''M'' THEN 1
WHEN pp.Sex =''F'' THEN 2
ELSE 3 END
,LanguageID = ISNULL(pp.PrefLanguageMId,0)
,Zip = LEFT(ISNULL(pp.Zip, ''Other''),5)
,RaceCode = ISNULL(r.Code, ''U'')
,Race = ISNULL(r.description, ''Unknown'')
,UDSRaceLine = CASE WHEN r.Code = ''A'' THEN ''1. Asian''
WHEN r.Code = ''NH'' THEN ''2a. Native Hawaiian''
WHEN r.Code = ''OPI'' THEN ''2b. Other Pacific Islander''
WHEN r.Code = ''B'' THEN ''3. Black/African American''
WHEN r.Code = ''AI'' THEN ''4. American Indian/Alaskan Native''
WHEN r.Code = ''W'' THEN ''5. White''
WHEN r.Code = ''M'' THEN ''6. More than one race''
WHEN r.Code = ''U'' THEN ''7. Unreported/Refused to report''
ELSE ''7. Unreported/Refused to report'' END
,EthnicityCode = ISNULL(eth.Code, ''NR'')
,Ethnicity = CASE WHEN eth.Code = ''H'' OR r.Code = ''H'' THEN ''Hispanic/Latino''
WHEN eth.Code IS NULL or eth.Code = ''NR'' THEN ''Not Reported''
ELSE ''All Others'' END
,UDSEthnicityLine= CASE WHEN eth.Code = ''H'' OR r.Code = ''H'' THEN ''1. Hispanic/Latino''
ELSE ''2.Not Hispanic or Latino '' END
,UDSEthnicityCode = CASE WHEN eth.Code = ''H'' OR r.Code = ''H'' THEN ''H'' ELSE ''N'' END
,PatientPrefLanguage = ISNULL(lang.Description, ''None'')
,FacilityName = ISNULL(fv.ListName, ''No Facility'')
,FacilityId = ISNULL(pp.FacilityId,0)
,AgWrkrStatusID = ISNULL(cri.AgWrkrStatusID,0)
,Translation = ISNULL(Cri.Translation,0)
,AgrWrkStatus = ISNULL(cml4c.Description, ''Unknown'')
,MigrantCode = ISNULL(cml4c.code,''U'')
,MigrantStatusId = CASE WHEN cml4c.Description LIKE ''Non%'' THEN 1
WHEN cml4c.Description LIKE ''Seas%'' THEN 2
WHEN cml4c.Description LIKE ''Migr%'' THEN 3
WHEN cml4c.Description LIKE ''Emplo%'' THEN 4
WHEN cml4c.Description LIKE ''Retired%'' THEN 5
ELSE 0 END
FROM
PatientProfile pp
LEFT JOIN Medlists r ON pp.RaceMId=r.MedlistsId
LEFT JOIN MedLists lang ON pp.PrefLanguageMId = lang.MedListsID
LEFT JOIN cusCRIInterview cri1 on pp.patientprofileid = cri1.patientprofileid
LEFT JOIN cusCRIMedLists eth ON cri1.RaceMID2 = eth.MedListsId
LEFT JOIN cusCRIInterview cri ON CRI.PatientProfileID = pp.PatientProfileId
LEFT JOIN cusCRIMedLists cml4c ON cri.AgWrkrStatusID = cml4c.MedListsId
LEFT JOIN DoctorFacility fv ON fv.DoctorFacilityId = pp.FacilityId;
')
END
September 2, 2014 at 11:36 am
As always Lynn you are one awesome dude. Count me as a member of the LP fan club:-)
No to look at what you did and try to understand it. Thanks again!
***SQL born on date Spring 2013:-)
September 2, 2014 at 11:40 am
I started adding the extra ''. But wasn't exactly sure how that worked after it tried to run it. I should have put more effort into it. I also noticed on my first try on it I also left off the exec.
Thanks again
***SQL born on date Spring 2013:-)
September 2, 2014 at 11:43 am
You are welcome. Dynamic SQL can be a pain to write, I should know as I have written my share of it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply