Hi folks,
I'm doing calculations based on data in a table, but the data has some
zeros in the field I'm dividing by. I'm trying to write a script to
replace any field with 0 or null with 1, but it's not working. HEre's
what I've got:
Update A Set A.deptcode = A.deptcode,
A.type = A.Type,
A.Volume = (case A.Volume
When Null Then 1
When 0 then 1
Else A.Volume
End)
From Data_Unsorted A Join Data_Unsorted B On
A.deptcode = B.deptcode and A.type = B.Type
My table is data_unsorted and deptcode and type are my primary keys
Volume is the item I want to put 1 if null or zero, and I'd thing the
above statement would work, but it doesn't. This table has 383 rows,
and it says it updates 383 rows, but when I run the following query to
test:
select a.deptcode, a.type, a.volume
from data_unsorted a
where a.AveMonthVolume = 0 or a.AveMonthVOlume is null
It didn't work... still TONS of nulls and zero's. Is there a trick to
this???
Thanks,
Alex. 2 3866
Alex,
Try this:
update YourTable
set Col = 1
where Col = 0 or Col is null
Shervin
"Alex" <al**@totallynerd.com> wrote in message
news:2b*************************@posting.google.co m... Hi folks,
I'm doing calculations based on data in a table, but the data has some zeros in the field I'm dividing by. I'm trying to write a script to replace any field with 0 or null with 1, but it's not working. HEre's what I've got:
Update A Set A.deptcode = A.deptcode, A.type = A.Type, A.Volume = (case A.Volume When Null Then 1 When 0 then 1 Else A.Volume End) From Data_Unsorted A Join Data_Unsorted B On A.deptcode = B.deptcode and A.type = B.Type
My table is data_unsorted and deptcode and type are my primary keys Volume is the item I want to put 1 if null or zero, and I'd thing the above statement would work, but it doesn't. This table has 383 rows, and it says it updates 383 rows, but when I run the following query to test:
select a.deptcode, a.type, a.volume from data_unsorted a where a.AveMonthVolume = 0 or a.AveMonthVOlume is null
It didn't work... still TONS of nulls and zero's. Is there a trick to this???
Thanks,
Alex.
Alex (al**@totallynerd.com) writes: Update A Set A.deptcode = A.deptcode, A.type = A.Type, A.Volume = (case A.Volume When Null Then 1 When 0 then 1 Else A.Volume End) From Data_Unsorted A Join Data_Unsorted B On A.deptcode = B.deptcode and A.type = B.Type
You compare A.Volume to NULL, but NULL is never equal to NULL or
anything else. Write the CASE expresssion as.
CASE WHEN volume IS NULL THEN 1
WHEN volume = 0 THEN 1
ELSE volume
END
or
CASE coalesce(volume, 0) WHEN 0 THEN 1 ELSE volume END
The coalesce function returns the first non-NULL value in the list.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Trevor Best |
last post by:
Is there a *simple* way to change a collumn from allowing null to not
null?
I just unchecked "allow nulls" in EM and the SQL it generates to do
this one thing is astonishing, create table, drop...
|
by: tpcolson |
last post by:
I have a fairly large access 2003 table (200,000) records. Field 'X' is
a text field, and contains either no value, 4 digits, 5 digits, or 6
digits. What I need to do is to add two zeros to the...
|
by: jkearns |
last post by:
Hello,
I made a report from a crosstab query following the steps onlined in
MSDN's Solutions.mdb example. I now have a dynamic crosstab report
(great!), but with one minor problem. I cannot get...
|
by: Chris |
last post by:
I know the reason why I get an error when the fields are set to null, but I'm working with old data that contains them. Any quick fixes? was hoping to avoid writing out every field in sql statement...
|
by: Rico |
last post by:
I'm moving some queries out of an Access front end and creating views out of
them in SQL Server 2005 express. In some of the numeric fields, I use nz
quite often, ( i.e. nz(,0)) to return a zero...
|
by: john |
last post by:
SELECT DISTINCT Ap2.test1, Count(Ap2.test1) AS AantalVantest1
FROM Ap2
GROUP BY Ap2.test1;
gives me a record count per unique value of test1 field. How do I include
the count of all the records...
|
by: rcamarda |
last post by:
I have a field that may be null which is valid, and I am finding
something I didnt expect when working with nulls.
SELECT NULL/4.0 will return NULL (which I expect), however, when I test
it with a...
|
by: Mark Dickinson |
last post by:
I get the following behaviour on Python 2.5 (OS X 10.4.8 on PowerPC,
in case it's relevant.)
(0.0, 0.0)
(-0.0, -0.0)
I would have expected y to be -0.0 in the first case, and 0.0 in the...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| | |