Returns a NULL value - 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

General SQL Server Forums

New to SQL Server Programming

Returns a NULL value

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-09 : 06:41:07

Hi,

I havethe following query which returns one row of data, however, the MedicalcodeID is NULL.

SELECT     db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID, 
                      db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode, 
                      db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport, 
                      db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term, 
                      db1.dbo.Referral.SnomedCTConcept, db1.dbo.Referral.ACode, db1.dbo.Referral.ReadCode
FROM         db1.dbo.Referral WITH (tablock) INNER JOIN
                      dbo.patient ON db1.dbo.Referral.PatientGuidDigest = dbo.patient.PatientGuidDigest INNER JOIN
                      dbo.Consultation ON dbo.patient.PatientID = dbo.Consultation.PatientID INNER JOIN dbo.Staff ON dbo.Consultation.StaffID = dbo.Staff.StaffID LEFT Outer JOIN
                      dbo.lkupMedical ON db1.dbo.Referral.SnomedCTConcept = dbo.lkupMedical.Snomed AND 
                      db1.dbo.Referral.ACode = dbo.lkupMedical.ACode COLLATE SQL_Latin1_General_CP1_CS_AS AND 
                      db1.dbo.Referral.ReadCode = dbo.lkupMedical.ReadCode COLLATE SQL_Latin1_General_CP1_CS_AS AND 
                      db1.dbo.Referral.Term = dbo.lkupMedical.Term
WHERE dbo.patient.PatientID = 315434344 and  dbo.Consultation.ConsultationID = 14541 and db1.dbo.Referral.Term = 'Referral for further care' and db1.dbo.Referral.ACode IS NULL and 
             db1.dbo.Referral.ReadCode = '8H' and db1.dbo.Referral.SnomedCTConcept = 183444007

Then I checked from the dbo.lkupMedical table and the MedicalCodeID for the above combination is 33959.

Select * from    dbo.lkupMedical
 where snomed = 183444007 and ACode Is NULL and readcode = '8H' and term = 'referral for further care'

Results

MedicalCodeID = 33959
Snomed = 183444007
Acode = NULL
ReadCode = 8H
Term = Referral for further care

It is clear from teh above - that the expected MedicalCodeID = 33959 and NOT NULL. I dont understand why SQL added the COLLATE SQL_Latin1_General_CP1_CS_AS to the query - am working on a database developed by another person. Could it be the ACode and ReadCode in dbo.lkupMedical is not set up with SQL_Latin1_General_CP1_CS_AS.
I have no idea what it means and how to implement to LkupMedical table....

Also, trying to resolve the above query, I changed HIGHLIGHTED JOIN to Inner/Right but it never yielded any results, no record found..

Any help please..

Thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-09 : 06:59:38

You are using Case Sensitive Accent Sensitive Collation for Referral table. So when you specify

db1.dbo.Referral.Term = 'Referral for further care'

as condition, it will look for exact match which it doesn't find. As it is LEFT JOIN, column value is returned as NULL.

Whereas in the lkupMedical table query,

Select * from    dbo.lkupMedical where snomed = 183444007 and ACode Is NULL and readcode = '8H' and term = 'referral for further care'

You are using different case for Term column yielding you the result.

I will suggest you to remove collation temporarily and see if you get the correct output. If it is, then correct your WHERE clause to match the correct casing for the value.

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-09 : 07:09:36

Ok, I removed the Collation and still receive the same results..

MedicalCodeID = NULL

Why?

Thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-09 : 07:59:38

Ok to add on the above - I tested by removing the ACode & ReadCode from the query and presented it as;


SELECT     db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID, 
                      db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode, 
                      db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport, 
                      db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term, 
                      db1.dbo.Referral.SnomedCTConcept, db1.dbo.Referral.ACode, db1.dbo.Referral.ReadCode
FROM         db1.dbo.Referral WITH (tablock) INNER JOIN
                      dbo.patient ON db1.dbo.Referral.PatientGuidDigest = dbo.patient.PatientGuidDigest INNER JOIN
                      dbo.Consultation ON dbo.patient.PatientID = dbo.Consultation.PatientID INNER JOIN dbo.Staff ON dbo.Consultation.StaffID = dbo.Staff.StaffID LEFT Outer JOIN
                      dbo.lkupMedical ON db1.dbo.Referral.SnomedCTConcept = dbo.lkupMedical.Snomed AND 
                      db1.dbo.Referral.Term = dbo.lkupMedical.Term
WHERE dbo.patient.PatientID = 315434344 and  dbo.Consultation.ConsultationID = 14541 and db1.dbo.Referral.Term = 'Referral for further care' and db1.dbo.Referral.ACode IS NULL and 
             db1.dbo.Referral.ReadCode = '8H' and db1.dbo.Referral.SnomedCTConcept = 183444007

I received 6 records - also included the MedicalCodeID = 33959.

It is understadable to receive more than 1 record as I restricted the where clause to ONLY the Snomed and Term...

Now I can confirm the problem is when I am comparing the Acode and ReadCode in the where clause..

Any ideas how can resolve this issue?

MAny thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-09 : 09:22:20

This part of the query:


LEFT JOIN dbo.lkupMedical ON db1.dbo.Referral.SnomedCTConcept = dbo.lkupMedical.Snomed
	AND db1.dbo.Referral.Term = dbo.lkupMedical.Term

can return rows with null values from dbo.lkupMedical. that's what Left Join means. So, there are some rows in db1.dbo.Referral.SnomedCTConcept thAT have no matches in dbo.lkupMedical

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-09 : 10:15:57

I have tried to change the joins but still I dont get one value

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-09 : 10:25:16

OK -- at this point there's not much I can do without seeing the data. your query looks fine. getting nulls for some columns is normal and expected with left joins. If you don't want to see them, exclude them in the WHERE clause or make it an INNER JOIN instead

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-09 : 10:31:27

Ok Nearly there....

This is the code it gives the one line of results;


SELECT     db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID, 
                      db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode, 
                      db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport, 
                      db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term, 
                      db1.dbo.Referral.SnomedCTConcept, db1.dbo.Referral.ACode, db1.dbo.Referral.ReadCode
FROM         db1.dbo.Referral WITH (tablock) INNER JOIN
                      dbo.patient ON db1.dbo.Referral.PatientGuidDigest = dbo.patient.PatientGuidDigest INNER JOIN
                      dbo.Consultation ON dbo.patient.PatientID = dbo.Consultation.PatientID INNER JOIN
                      dbo.Staff ON dbo.Consultation.StaffID = dbo.Staff.StaffID Right Outer JOIN
                      dbo.lkupMedical ON db1.dbo.Referral.SnomedCTConcept = dbo.lkupMedical.Snomed AND 
                      db1.dbo.Referral.Term = dbo.lkupMedical.Term AND
                      db1.dbo.Referral.ACode = dbo.lkupMedical.ACode COLLATE SQL_Latin1_General_CP1_CS_AS WHERE dbo.patient.PatientID = 315434344 and  dbo.Consultation.ConsultationID = 14541 and db1.dbo.Referral.Term = 'Referral for further care' and db1.dbo.Referral.ACode IS NULL and 
             db1.dbo.Referral.ReadCode = '8H' and db1.dbo.Referral.SnomedCTConcept = 183444007

However, I just need to add the last where clause which is Acode and is NULL value in the database but in some situtaions its NOT NULL.

                      db1.dbo.Referral.ReadCode = dbo.lkupMedical.ReadCode COLLATE SQL_Latin1_General_CP1_CS_AS

Now, Together I have


SELECT     db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID, 
                      db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode, 
                      db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport, 
                      db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term, 
                      db1.dbo.Referral.SnomedCTConcept, db1.dbo.Referral.ACode, db1.dbo.Referral.ReadCode
FROM         db1.dbo.Referral WITH (tablock) INNER JOIN
                      dbo.patient ON db1.dbo.Referral.PatientGuidDigest = dbo.patient.PatientGuidDigest INNER JOIN
                      dbo.Consultation ON dbo.patient.PatientID = dbo.Consultation.PatientID INNER JOIN
                      dbo.Staff ON dbo.Consultation.StaffID = dbo.Staff.StaffID Right Outer JOIN
                      dbo.lkupMedical ON db1.dbo.Referral.SnomedCTConcept = dbo.lkupMedical.Snomed AND 
                      db1.dbo.Referral.Term = dbo.lkupMedical.Term AND
                      db1.dbo.Referral.ACode = dbo.lkupMedical.ACode COLLATE SQL_Latin1_General_CP1_CS_AS AND 
                      db1.dbo.Referral.ReadCode = dbo.lkupMedical.ReadCode COLLATE SQL_Latin1_General_CP1_CS_AS  
WHERE dbo.patient.PatientID = 315434344 and  dbo.Consultation.ConsultationID = 14541 and db1.dbo.Referral.Term = 'Referral for further care' and db1.dbo.Referral.ACode IS NULL and 
             db1.dbo.Referral.ReadCode = '8H' and db1.dbo.Referral.SnomedCTConcept = 183444007

This outputs no record..

It should still output the one record..

NULL = NULL ? Yes?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-09 : 11:29:43

N, Null <> Null. That's why we use "is NULL"

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-09 : 12:01:50

Ok - Finally I came up with


SELECT     db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID, 
                      db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode, 
                      db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport, 
                      db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term, 
                      db1.dbo.Referral.SnomedCTConcept, db1.dbo.Referral.ACode, db1.dbo.Referral.ReadCode
FROM         db1.dbo.Referral WITH (tablock) INNER JOIN
                      dbo.patient ON db1.dbo.Referral.PatientGuidDigest = dbo.patient.PatientGuidDigest INNER JOIN
                      dbo.Consultation ON dbo.patient.PatientID = dbo.Consultation.PatientID INNER JOIN
                      dbo.Staff ON dbo.Consultation.StaffID = dbo.Staff.StaffID LEFT OUTER JOIN
                      dbo.lkupMedical ON db1.dbo.Referral.SnomedCTConcept = dbo.lkupMedical.Snomed AND 
                      db1.dbo.Referral.Term = dbo.lkupMedical.Term AND
                      db1.dbo.Referral.ACode = dbo.lkupMedical.ACode COLLATE SQL_Latin1_General_CP1_CS_AS AND 
                      db1.dbo.Referral.ReadCode = dbo.lkupMedical.ReadCode COLLATE SQL_Latin1_General_CP1_CS_AS  
WHERE dbo.patient.PatientID = 315434344 and  dbo.Consultation.ConsultationID = 14541 and db1.dbo.Referral.Term = 'Referral for further care' and db1.dbo.Referral.ACode IS NULL and db1.dbo.Referral.ReadCode = '8H' and db1.dbo.Referral.SnomedCTConcept = 183444007

It gave me one expected record, ONLY with the MedicalCodeID to be NULL value. The value should be 33959.

N/B:

If I remove

db1.dbo.Referral.ACode = dbo.lkupMedical.ACode COLLATE SQL_Latin1_General_CP1_CS_AS

which has the NULL Value, I receive the same record but MedicalCodeID = 33959 (which correct!)

The issue is when ACode is NULL value in both tables then the MedicalCodeID is set to NULL...

Any work around?

Any help will be highly appreciated. Please assist!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-10 : 08:58:47

So, when ACode is NULL in both tables, db1.dbo.Referral.ACode = dbo.lkupMedical.ACode will be false. One way around it is:

(db1.dbo.Referral.ACode = dbo.lkupMedical.ACode OR db1.dbo.Referral.ACode IS NULL AND dbo.lkupMedical.ACode IS NULL)

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-10 : 09:25:28

Tried the above it brought 5 records....

How can I change the code below to work:


ISNULL (db1.dbo.Referral.ACode, '(novalue)') = ISNULL (dbo.lkupMedical.ACode , '(novalue)')

Assuming, ACode is bigint datatype....

I need to change the '(novalue)') to accomodate bigint rather than varchar.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-10 : 09:41:56

"I need to change the '(novalue)') to accomodate bigint rather than varchar. "

why? My earlier suggestion accomplishes the same thing. Otherwise, choose an invalid value for Acode, e.g

isnull(acode, -1) = isnull(acode, -1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources