How to Eliminate NULL values from the SQL queries

[sreemati]

ssue:
------
I am working on a health care system and currently facing problem with NULL values being introduced as part of my SQL query.

What is the query trying to do?
The query is trying to retrieve ids from summary table for a practice where the patient are dead and find out if there are same patients in other systems like clinics or hospitals where this patient might still be showing as alive. This is basically a data cleansing query.

DECLARE @REGION char(1)
SET @REGION = 'A'
 
DECLARE @PRAC_ID varchar(5)
SET @PRAC_ID = '11111'
 
 
        SELECT  Max(D.DATAAGE) DATAAGE, 
                ds.LINK_ID, 
                D.PAT_ID, 
                ds.FName1, 
                ds.Surname, 
                D.PRAC_ID, 
                D.DataSource_ID, 
                DS.DataSource_Description, 
                DS.Primary_Care_System , 
                @REGION as Region
        FROM         Demography D, 
                Demography_Summary ds, 
                DataSource_IDs DS
        WHERE         ds.LINK_ID = D.LINK_ID
                AND ds.LINK_ID IN
                (
        SELECT         LINK_ID
        FROM         Demography_Summary 
        WHERE         DEAD =  1
                AND PRAC_ID = @PRAC_ID
                AND Status < 2
                )
        AND D.DataSource_ID = DS.DataSource_ID
        AND (DS.Primary_Care_System = 1 OR DS.Secondary_Care_Clinic = 1)        
        AND D.DataSource_ID <> 36 
        AND D.DEAD = 0
        AND D.Status < 2        
        GROUP BY ds.LINK_ID, 
                D.PAT_ID, 
                ds.FName1, 
                ds.Surname, 
                D.PRAC_ID,
                D.DataSource_ID, 
                DS.DataSource_Description, 
                DS.Primary_Care_System 
        ORDER BY ds.Surname, ds.FName1 
 

Result shown:
2007-02-26 00:00:00.000 201295648 1402203330 Testing Test 22222 50 clinic1, Primary Care 1 T
2008-05-06 15:53:40.077 201295648 1402203330 Testing Test NULL 50 clinic1, Primary Care 1 T

Now I don't want the second row that is showing a NULL value for Practice ID (as they are not a valid value for practice id ) and only the first row needs to be shown.

I have tried using D.PRAC_ID is NOT NULL but it doesnt seem to work. I have even tried a sub query to eliminate PRAC_IDs with NULL but the problem is that the value that needs to be considered has same LINK_ID as that of PRAC_ID = null. Can anyone help with this please.

Thanks in advance,
Sree

[sreemati]

ssue:
------
I am working on a health care system and currently facing problem with NULL values being introduced as part of my SQL query.

What is the query trying to do?
The query is trying to retrieve ids from summary table for a practice where the patient are dead and find out if there are same patients in other systems like clinics or hospitals where this patient might still be showing as alive. This is basically a data cleansing query.

DECLARE @REGION char(1)
SET @REGION = 'A'
 
DECLARE @PRAC_ID varchar(5)
SET @PRAC_ID = '11111'
 
 
        SELECT  Max(D.DATAAGE) DATAAGE, 
                ds.LINK_ID, 
                D.PAT_ID, 
                ds.FName1, 
                ds.Surname, 
                D.PRAC_ID, 
                D.DataSource_ID, 
                DS.DataSource_Description, 
                DS.Primary_Care_System , 
                @REGION as Region
        FROM         Demography D, 
                Demography_Summary ds, 
                DataSource_IDs DS
        WHERE         ds.LINK_ID = D.LINK_ID
                AND ds.LINK_ID IN
                (
        SELECT         LINK_ID
        FROM         Demography_Summary 
        WHERE         DEAD =  1
                AND PRAC_ID = @PRAC_ID
                AND Status < 2
                )
        AND D.DataSource_ID = DS.DataSource_ID
        AND (DS.Primary_Care_System = 1 OR DS.Secondary_Care_Clinic = 1)        
        AND D.DataSource_ID <> 36 
        AND D.DEAD = 0
        AND D.Status < 2        
        GROUP BY ds.LINK_ID, 
                D.PAT_ID, 
                ds.FName1, 
                ds.Surname, 
                D.PRAC_ID,
                D.DataSource_ID, 
                DS.DataSource_Description, 
                DS.Primary_Care_System 
        ORDER BY ds.Surname, ds.FName1 
 

Result shown:
2007-02-26 00:00:00.000 201295648 1402203330 Testing Test 22222 50 clinic1, Primary Care 1 T
2008-05-06 15:53:40.077 201295648 1402203330 Testing Test NULL 50 clinic1, Primary Care 1 T

Now I don't want the second row that is showing a NULL value for Practice ID (as they are not a valid value for practice id ) and only the first row needs to be shown.

I have tried using D.PRAC_ID is NOT NULL but it doesnt seem to work. I have even tried a sub query to eliminate PRAC_IDs with NULL but the problem is that the value that needs to be considered has same LINK_ID as that of PRAC_ID = null. Can anyone help with this please.

Thanks in advance,
Sree

Hi all,

The issue was with data, it was TEXT NULL and not a actual NULL as such and hence it was not getting eliminated through IS NULL or IS NOT NULL. Changed the data and its works beautifully.

Cheers,
Sree