Missing NOK Relationship Data from IBID Query

From EHS Help
Jump to: navigation, search

This is a Technical (IT) Instructions topic


Overview

This problem manifests itself as the NOK Relationship column being empty in output from the IBID Query function. This affected all versions of the software up to and including v1.1.8.

Software Patch

The software patch takes the form of an SQL script that re-creates the VIEW_IBID_MAIN object so that the NOK Relationship field is correctly output.

The file is available on request via www.Evohelpdesk.co.uk.

SQL

Instead of requesting the SQL file, you can cut & paste the contents here:

DROP VIEW [DBO].[VIEW_IBID_MAIN];

GO

CREATE VIEW [dbo].[VIEW_IBID_MAIN] (IBID_MAIN_ID, CASE_ID, PATIENT_ID, PATIENTS_NAME_ID, PATIENTS_ADDRESS_ID, PATIENT_NOK_ID, PATIENTS_GP_ID, IBID_DEMOGRAPHY_ID, UNIT_ID, GIVEN_NAME, MIDDLE_NAME, FAMILY_NAME, PHONETIC_GIVEN_NAME, PHONETIC_FAMILY_NAME, DATE_OF_BIRTH, GENDER, GENDER_DESC, NHS_NUMBER, NHS_NUMBER_STATUS, ADDRESS_1, ADDRESS_2, DISTRICT, TOWN, COUNTY, COUNTRY, COUNTRY_DESC, POSTCODE_1, POSTCODE_2, LATITUDE, LONGITUDE, HOSPITAL_NUMBER, DAYTIME_TEL, EVENING_TEL, MOBILE_TEL, EMAIL_ADDRESS, RACE, RACE_DESC, ETHNIC_CATEGORY, ETHNIC_CATEGORY_DESC, MARITAL_STATUS, MARITAL_STATUS_DESC, VULNERABLE_PERSON, RELATIONSHIP, RELATIONSHIP_DESC, NOK_FORENAME, NOK_SURNAME, NOK_ADDRESS_1, NOK_ADDRESS_2, NOK_DISTRICT, NOK_TOWN, NOK_POSTCODE, NOK_TELEPHONE, NOK_MOBILE_TEL, NOK_EMAIL_ADDRESS, GP_NAME, GP_CODE, GP_ADDRESS, GP_TELEPHONE, LIVING_CIRCUMSTANCES, LIVING_CIRCUMSTANCES_DESC, SOCIO_ECONOMIC_CLASS, SOCIO_ECONOMIC_CLASS_DESC, OCCUPATION, EARNER_OCCUPATION, AGE, HEIGHT, WEIGHT, BODY_MASS_INDEX, BODY_HABITUS, BODY_HABITUS_DESC, RECORDYEAR, REC_TYPE_ACTION, RECTYPEACTION_DESC, RECORDREASON, RECORDREASON_DESC, MAJOR_INJURY_EVENT_NUM, TRAUMA_NETWORK_ID) 
AS
SELECT

MAIN.IBID_MAIN_ID,
MAIN.CASE_ID,
P.PATIENT_ID,
PN.PATIENTS_NAME_ID,
PA.PATIENTS_ADDRESS_ID,
PK.PATIENT_NOK_ID,
GP.PATIENTS_GP_ID,
DEM.IBID_DEMOGRAPHY_ID,

P.UNIT_ID,
PN.GIVEN_NAME,
PN.MIDDLE_NAME,
PN.FAMILY_NAME,
PN.PHONETIC_GIVEN_NAME,
PN.PHONETIC_FAMILY_NAME,
P.DATE_OF_BIRTH,
P.GENDER,
SEXCODE.TERM_DESC AS GENDER_DESC,
P.NHS_NUMBER,
P.NHS_NUMBER_STATUS,
PA.ADDRESS_1,
PA.ADDRESS_2,
PA.DISTRICT,
PA.TOWN,
PA.COUNTY,
PA.COUNTRY,
COUNTRYCODES1.TERM_DESC AS COUNTRY_DESC,
PA.POSTCODE_1,
PA.POSTCODE_2,
PA.LATITUDE,
PA.LONGITUDE,
P.HOSPITAL_NUMBER,
P.DAYTIME_TEL,
P.EVENING_TEL,
P.MOBILE_TEL,
P.EMAIL_ADDRESS,
P.RACE,
RACECODE.TERM_DESC AS RACE_DESC,
P.ETHNIC_CATEGORY,
ETHNICCODE.TERM_DESC AS ETHNIC_CATEGORY_DESC,
P.MARITAL_STATUS,
MARITALCODE.TERM_DESC AS MARITAL_STATUS_DESC,
P.VULNERABLE_PERSON,

PK.RELATIONSHIP,
RELATIONSHIPCODE.TERM_DESC AS RELATIONSHIP_DESC,
PK.FORENAME as NOK_FORENAME,
PK.SURNAME as NOK_SURNAME,
PK.ADDRESS_1 as NOK_ADDRESS_1,
PK.ADDRESS_2 as NOK_ADDRESS_2,
PK.DISTRICT as NOK_DISTRICT,
PK.TOWN as NOK_TOWN,
PK.POSTCODE as NOK_POSTCODE,
PK.TELEPHONE as NOK_TELEPHONE,
PK.MOBILE_TEL as NOK_MOBILE_TEL,
PK.EMAIL_ADDRESS as NOK_EMAIL_ADDRESS,

GP.GP_NAME,
GP.GP_CODE,
GP.GP_ADDRESS,
GP.GP_TELEPHONE,

DEM.LIVING_CIRCUMSTANCES,
LIVINGCIRCUMSTANCESCODE.TERM_DESC AS LIVING_CIRCUMSTANCES_DESC,
DEM.SOCIO_ECONOMIC_CLASS,
SECCODE.TERM_DESC AS SOCIO_ECONOMIC_CLASS_DESC,
DEM.OCCUPATION,
DEM.EARNER_OCCUPATION,
DEM.AGE,
DEM.HEIGHT,
DEM.WEIGHT,
DEM.BODY_MASS_INDEX,
DEM.BODY_HABITUS,
BODY_HABITUS_CODE.TERM_DESC AS BODY_HABITUS_DESC,

MAIN.RECORDYEAR,
MAIN.REC_TYPE_ACTION,
RECTYPEACTIONCODE.TERM_DESC AS RECTYPEACTION_DESC,
MAIN.RECORDREASON,
RECORDREASONCODE.TERM_DESC as RECORDREASON_DESC,
MAIN.MAJOR_INJURY_EVENT_NUM,
MAIN.TRAUMA_NETWORK_ID

FROM PATIENTS P
LEFT JOIN PATIENTS_NAME PN ON (P.PATIENT_ID = PN.PATIENT_ID)
LEFT JOIN PATIENTS_ADDRESS PA ON (P.PATIENT_ID = PA.PATIENT_ID)
LEFT JOIN PATIENTS_NOK PK ON (P.PATIENT_ID = PK.PATIENT_ID)
LEFT JOIN PATIENTS_GP GP ON (P.PATIENT_ID = GP.PATIENT_ID)
INNER JOIN IBID_MAIN MAIN ON (P.PATIENT_ID = MAIN.PATIENT_ID)
LEFT JOIN IBID_DEMOGRAPHY DEM ON (MAIN.IBID_MAIN_ID = DEM.IBID_MAIN_ID)

LEFT JOIN CODES SEXCODE ON (P.GENDER = SEXCODE.CONCEPT_CODE) AND (SEXCODE.CODE_LIST = 'IBID_SEX')
LEFT JOIN CODES RACECODE ON (P.RACE = RACECODE.CONCEPT_CODE) AND (RACECODE.CODE_LIST = 'IBID_RACE')
LEFT JOIN CODES ETHNICCODE ON (P.ETHNIC_CATEGORY = ETHNICCODE.CONCEPT_CODE) AND (ETHNICCODE.CODE_LIST = 'ETHNICCATEGORY')
LEFT JOIN CODES MARITALCODE ON (P.MARITAL_STATUS = MARITALCODE.CONCEPT_CODE) AND (MARITALCODE.CODE_LIST = 'MARITALSTATUS')
LEFT JOIN CODES RELATIONSHIPCODE ON (PK.RELATIONSHIP = RELATIONSHIPCODE.CONCEPT_CODE) AND (RELATIONSHIPCODE.CODE_LIST = 'IBID_NOK_RELATIONSHIP')
LEFT JOIN CODES RECORDREASONCODE ON (MAIN.RECORDREASON = RECORDREASONCODE.CONCEPT_CODE) AND (RECORDREASONCODE.CODE_LIST = 'IBID_RECORDREASON')
LEFT JOIN CODES RECTYPEACTIONCODE ON (MAIN.REC_TYPE_ACTION = RECTYPEACTIONCODE.CONCEPT_CODE) AND (RECTYPEACTIONCODE.CODE_LIST = 'IBID2_RECORDTYPE')
LEFT JOIN CODES LIVINGCIRCUMSTANCESCODE ON (DEM.LIVING_CIRCUMSTANCES=LIVINGCIRCUMSTANCESCODE.CONCEPT_CODE) AND (LIVINGCIRCUMSTANCESCODE.CODE_LIST = 'IBID_LIVINGCIRCUMSTANCES')
LEFT JOIN CODES SECCODE ON (DEM.SOCIO_ECONOMIC_CLASS = SECCODE.CONCEPT_CODE) AND (SECCODE.CODE_LIST = 'IBID_SOCIOECONCLASS')
LEFT JOIN CODES BODY_HABITUS_CODE ON (DEM.BODY_HABITUS = BODY_HABITUS_CODE.CONCEPT_CODE) AND (BODY_HABITUS_CODE.CODE_LIST = 'IBID_BODYHABITUS')
LEFT JOIN CODES COUNTRYCODES1 ON (PA.COUNTRY = COUNTRYCODES1.CONCEPT_CODE) AND (COUNTRYCODES1.CODE_LIST = 'ISO_COUNTRYCODES')

WHERE
(PA.RECORD_END_TIME IS NULL) AND
(PN.RECORD_END_TIME IS NULL) AND
(PK.RECORD_END_TIME IS NULL) AND
(GP.RECORD_END_TIME IS NULL);
GO

UPDATE [dbo].[DB_INFORMATION] SET CACHE_DATE = CURRENT_TIMESTAMP;

GO