retrieve null values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

retrieve null values

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-05-18 : 09:31:42

i have patients that have documents that can specific stages for a patient (1,2,3 etc)the stage can have an associated value or it may not. i need to display the document stage even if there is no value.
i have attempted changing my joins but it doesnt work. i believe i need to account or state nulls in my sp. (see the tables in blue where the values come from) and (the field in red)

SELECT dbo.CV3ClientVisit.ClientDisplayName as Patient,
dbo.CV3ClientVisit.IDCode as MRN,
dbo.CV3ClientVisit.VisitIDCode as Encnumber,
dbo.CV3ClientVisit.AdmitDtm as Admit_date,
DBO.nyp_TF_GetLocationForEvent(dbo.CV3ClientDocument.clientVisitGUID,observationDocument.RecordedDtm) as Unit_note_written,
clientObsEntryItem.Site as Pressure_Ulcer_Location,
dbo.SCMObsFSRowComment.CommentText as Pressure_Ulcer_Aquired,
SCMObsFSListValues.Value AS Pressure_Ulcer_Stage
FROM
dbo.SCMObsFSListValues RIGHT OUTER JOIN
dbo.CV3ClientDocument
INNER JOIN
dbo.CV3ObservationDocument AS observationDocument WITH (NOLOCK) ON
observationDocument.OwnerGUID = dbo.CV3ClientDocument.GUID
INNER JOIN
dbo.CV3ObsCatalogMasterItem AS master WITH (nolock) ON observationDocument.ObsMasterItemGUID = master.GUID
and
(master.Name IN ('as_icu_skn_impair_prulcer_stg_I', 'as_icu_skn_impair_prulcer_stg_II', 'as_icu_skn_impair_prulcer_stg_III', 'as_icu_skn_impair_prulcer_stg_IIII',
'as_icu_skn_impair_prulcer_stg_IV','as_icu_skn_impair_prulcer_stg_Unstage'))
left outer JOIN
dbo.CV3Observation AS observation WITH (NOLOCK) ON observation.GUID = observationDocument.ObservationGUID
INNER JOIN
dbo.CV3ClientVisit ON dbo.CV3ClientDocument.ClientVisitGUID = dbo.CV3ClientVisit.GUID AND dbo.CV3ClientVisit.TypeCode = 'Inpatient'
and dbo.CV3ClientDocument.ClientGUID = dbo.CV3ClientVisit.ClientGUID AND
dbo.CV3ClientDocument.ChartGUID = dbo.CV3ClientVisit.ChartGUID and dbo.CV3ClientDocument.patcaredocguid ='4000001020202001'
INNER JOIN
dbo.CV3Location ON dbo.CV3ClientVisit.CurrentLocationGUID = dbo.CV3Location.GUID ON
dbo.SCMObsFSListValues.ParentGUID = observationDocument.ObservationDocumentGUIDand CV3Location.ParentGUID in ('2000001000061001','4000001145061001','8000001069061001')
LEFT OUTER JOIN
dbo.CV3ObservationXInfo AS obsXInfo WITH (NOLOCK) ON observation.GUID = obsXInfo.ObservationXInfoGUID
LEFT OUTER JOIN
dbo.SCMObsFSRowComment
RIGHT OUTER JOIN
dbo.CV3ClientObsEntryItem AS clientObsEntryItem WITH (NOLOCK) ON dbo.SCMObsFSRowComment.ParentGUID = clientObsEntryItem.GUID ON
observationDocument.ParameterGUID = clientObsEntryItem.GUID
WHERE (dbo.CV3ClientVisit.AdmitDtm BETWEEN @start_date AND @end_date)
and dbo.SCMObsFSRowComment.Active=1 and
clientObsEntryItem.Active =1 and
dbo.CV3ClientDocument.Active =1 and
observationDocument.Active =1 and
dbo.SCMObsFSListValues.Active =1 and
dbo.CV3ClientVisit.Active =1 and
master.Active =1

ORDER BY dbo.CV3ClientVisit.IDCode

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-18 : 09:42:30

You probably need to change the join to a left join - that will return a row even if there is no join row (delivering nulls).
Also probably need to coalesce any columns from that table in the result to replace null values with something.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-05-18 : 20:08:14

Full disclosure: This is a personal pet peeve of mine.

In the WHERE clause you are requiring that the OUTER JOIN table needs to have a specific value (dbo.SCMObsFSListValues.Active =1). This turns it into an INNER JOIN. Should that logic be part of the JOIN condition itself?

You are also requiring a value when joining the CV3Location table, which will also turn the original join into an INNER JOIN.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources