By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

How to Eliminate NULL values from the SQL queries

P: 46
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.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @REGION char(1)
  2. SET @REGION = 'A'
  3.  
  4. DECLARE @PRAC_ID varchar(5)
  5. SET @PRAC_ID = '11111'
  6.  
  7.  
  8.         SELECT  Max(D.DATAAGE) DATAAGE, 
  9.                 ds.LINK_ID, 
  10.                 D.PAT_ID, 
  11.                 ds.FName1, 
  12.                 ds.Surname, 
  13.                 D.PRAC_ID, 
  14.                 D.DataSource_ID, 
  15.                 DS.DataSource_Description, 
  16.                 DS.Primary_Care_System , 
  17.                 @REGION as Region
  18.         FROM         Demography D, 
  19.                 Demography_Summary ds, 
  20.                 DataSource_IDs DS
  21.         WHERE         ds.LINK_ID = D.LINK_ID
  22.                 AND ds.LINK_ID IN
  23.                 (
  24.         SELECT         LINK_ID
  25.         FROM         Demography_Summary 
  26.         WHERE         DEAD =  1
  27.                 AND PRAC_ID = @PRAC_ID
  28.                 AND Status < 2
  29.                 )
  30.         AND D.DataSource_ID = DS.DataSource_ID
  31.         AND (DS.Primary_Care_System = 1 OR DS.Secondary_Care_Clinic = 1)        
  32.         AND D.DataSource_ID <> 36 
  33.         AND D.DEAD = 0
  34.         AND D.Status < 2        
  35.         GROUP BY ds.LINK_ID, 
  36.                 D.PAT_ID, 
  37.                 ds.FName1, 
  38.                 ds.Surname, 
  39.                 D.PRAC_ID,
  40.                 D.DataSource_ID, 
  41.                 DS.DataSource_Description, 
  42.                 DS.Primary_Care_System 
  43.         ORDER BY ds.Surname, ds.FName1 
  44.  
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
May 7 '08 #1
Share this Question
Share on Google+
1 Reply


P: 46
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.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @REGION char(1)
  2. SET @REGION = 'A'
  3.  
  4. DECLARE @PRAC_ID varchar(5)
  5. SET @PRAC_ID = '11111'
  6.  
  7.  
  8.         SELECT  Max(D.DATAAGE) DATAAGE, 
  9.                 ds.LINK_ID, 
  10.                 D.PAT_ID, 
  11.                 ds.FName1, 
  12.                 ds.Surname, 
  13.                 D.PRAC_ID, 
  14.                 D.DataSource_ID, 
  15.                 DS.DataSource_Description, 
  16.                 DS.Primary_Care_System , 
  17.                 @REGION as Region
  18.         FROM         Demography D, 
  19.                 Demography_Summary ds, 
  20.                 DataSource_IDs DS
  21.         WHERE         ds.LINK_ID = D.LINK_ID
  22.                 AND ds.LINK_ID IN
  23.                 (
  24.         SELECT         LINK_ID
  25.         FROM         Demography_Summary 
  26.         WHERE         DEAD =  1
  27.                 AND PRAC_ID = @PRAC_ID
  28.                 AND Status < 2
  29.                 )
  30.         AND D.DataSource_ID = DS.DataSource_ID
  31.         AND (DS.Primary_Care_System = 1 OR DS.Secondary_Care_Clinic = 1)        
  32.         AND D.DataSource_ID <> 36 
  33.         AND D.DEAD = 0
  34.         AND D.Status < 2        
  35.         GROUP BY ds.LINK_ID, 
  36.                 D.PAT_ID, 
  37.                 ds.FName1, 
  38.                 ds.Surname, 
  39.                 D.PRAC_ID,
  40.                 D.DataSource_ID, 
  41.                 DS.DataSource_Description, 
  42.                 DS.Primary_Care_System 
  43.         ORDER BY ds.Surname, ds.FName1 
  44.  
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
May 7 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.