Solved

remove NULL values from table

Posted on 2010-11-28
17
308 Views
Last Modified: 2012-08-14
Hi I have an issue i want to resolve, I have a temp table that looks like (see below) and I want to remove SOME of the NULL values, table looks like this..
client_id_1      client_id_2      yyyymm       total1        total2        total3
500      101784082      200907       400.00        400.00        400.00
500      101784082      200908       6.00        6.00        6.00
500      101784082      200909       400.00        400.00        400.00
500      101784082      200910       200.00        200.00        200.00
500      101784082      200911       300.00        300.00        300.00
500      101784082      200912       700.00        700.00        700.00
500      101784082      201001       00        00        00
500      101784082      201002       300.00        300.00        300.00
500      101784082      201003       200.00        200.00        200.00
500      101784082      201004       300.00        300.00        300.00
500      101784082      201005       100.00        100.00        100.00
500      NULL      201006       NULL        NULL        NULL
500      101784082      201007       10         20         30  
500      101784082      201008       250.00        1,000.00        800.00
500      101784082      201009       350.00        200.00        900.00
501      101890626      200907       49.80        49.80        49.80
501      101890626      200908       51.25        51.26        51.25
501      101890626      200909       51.68        51.69        51.68
501      101890626      200910       54.15        54.16        54.15
501      101890626      200911       53.15        53.16        53.15
501      101890626      200912       54.92        54.93        54.92
501      101890626      201001       53.57        53.57        53.57
501      101890626      201002       52.88        52.88        52.88
501      101890626      201003       52.58        52.59        52.58
501      101890626      201004       52.88        52.88        52.88
501      NULL      201005       NULL        NULL        NULL
501      NULL      201006       NULL        NULL        NULL
501      NULL      201007       NULL        NULL        NULL
501      NULL      201008       NULL        NULL        NULL
501      NULL      201009       NULL        NULL        NULL

I have 2 client ids in this example 500 & 501 (but hundreds in my temp table) and I only want to remove the NULLS when it falls in the middle of my client_id_1 i.e.  For client 500 in '201006' I have no value but I have a value in '201007', I want to remove the row at YYYYMM '201006'.
I do not want to remove any of the NULLS from client 501 as I need to populate these fields with '0.00'. Bit stuck so any assistance with be greatly appreciated.
0
Comment
Question by:twfw123
  • 8
  • 3
  • 2
  • +4
17 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 34227972
delete table where client_id_1 =  500  AND client_id_2    is NULL  AND total1 is NULL AND total2 is NULL AND total3 is NULL
0
 

Author Comment

by:twfw123
ID: 34227981
hi - no that will not work as I have 100's of customers in my table, so I can not select 1 at a time. So could have alot of situtations where there is 1 missing months data in the middle of a customers data.  
0
 
LVL 1

Expert Comment

by:Praveen_Venkat
ID: 34227997
Use isnull(clientId,'') in your database to remove the null values. You can also replace the database name
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 34228008
delete table1 where isnull(client_id_2,'')='' or  isnull(total1,'')='' or isnull(total2,'')='' or  isnull(total3,'')=''

try this
0
 

Author Comment

by:twfw123
ID: 34228017
hi - but will this not delete ALL my NULL customers – i only want to remove NULL values that fall in the middle of my client, i.e. 500 has a NULL value for  '201006' i want to delete this from the table but retain all of customer 501’s NULL values.
0
 
LVL 2

Expert Comment

by:svalekar
ID: 34228123
can you please exact scenario, what actually you want. Getting confused about your requirement
0
 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 34228405
try this one
delete table1 where isnull(client_id_2,'')='' and client_id_1 in (select distinct client_id_1 from table1 where  isnull(client_id_2,'')<>'')
0
 

Author Comment

by:twfw123
ID: 34228457
it is a little confusing, thats why im having a problem being able to do it. Basically i get monthly data - if I miss a month I do not want to report it and as roll and average forward - I only want to delete the NULLS from missing months of data - not when the NULLS are from months not reported, clear as mud? as stated above in the table above.
0
 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 34228462
wht abt my above query...u tried?
0
 

Author Comment

by:twfw123
ID: 34228876
hi priya -  i did but it deleted all the NULLS - problem is i need to retain the nulls that belong to client 501 and there will be others that are similar to this - thanks
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34229049
Hi,

This is your data


client_id_1      client_id_2      yyyymm       total1        total2        total3

500            NULL            201006       NULL        NULL        NULL
500            101784082       201007       10          20          30  
501            NULL            201006       NULL        NULL        NULL
501            NULL            201007       NULL        NULL        NULL


You just wanted to delete data marked in bold ONLY???

on which basis you wanted to???


Sory - I didnt understand from your post.
0
 

Author Comment

by:twfw123
ID: 34229068
hi birchsoft - yes that correct in your example only the data in bold - its quite difficult to explain in a paragraph. I only want to delete the rows with NULL when they fall in the middle of my client data. hope thats clear
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34229086
Hi


Sory again but what do you mean by when you said

"when they fall in the middle of my client data"

Can you please explain??


Thanks for quick feedback,I appreciates....
0
 

Author Comment

by:twfw123
ID: 34229112
if there there is no data for 1 reporting month i.e.

client_id_1      client_id_2      yyyymm       total1         total2          total3
500               101784082       201004       300.00        300.00        300.00
500               101784082       201005       100.00        100.00        100.00
500               NULL                201006       NULL             NULL        NULL
500              101784082       201007       10                 20               30  
500              101784082       201008       250.00          1,000.00      800.00
500              101784082       201009       350.00          200.00        900.00

in this example i have data for all months except for 201006 - and this is what i want to delete.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34229159
does this identify the ones to remove?


select * from yourtable
 where client_id_2 is null
   and exists (select client_id_1 from yourtable as x
        where x.client_id_1 = yourtable.client_id_1
          and x.client_id_2 is not null
          and x.yyyymm > yourtable.yyyymm)

if so use the delete...

confirm that the NULLS are actual database nulls not literals



delete from yourtable
 where client_id_2 is null
   and exists (select client_id_1 from yourtable as x
        where x.client_id_1 = yourtable.client_id_1
          and x.client_id_2 is not null
          and x.yyyymm > yourtable.yyyymm)

Open in new window

0
 

Accepted Solution

by:
twfw123 earned 0 total points
ID: 36205677
thanks for the assistance - but found another awnser, cheers
0
 

Author Closing Comment

by:twfw123
ID: 36235410
found another solution
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question