CODEWITHSUNDEEP
sql-server
AnandMeena
AnandMeena

Reputation: 578

Sql query that not return null values in result

I am really newbie with sqlserver.

I am querying some data from database and it is also returning null values with result.

My query is;

select amount, distributorid from paymants

Some distributors has null values in amount column.

Please help!

Thanks

Upvotes: 6

Views: 66084

Answers (6)

Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

SOLUTION-1:

If you simply need to return values that are not null, then try the following:

select amount, distributorid 
from paymants
where amount is not null

SOLUTION-2:

If you not only need to return values that are not null, but also need to change the null values into empty strings or any other default value, then try the following:

Firstly, change all null values to empty strings or a default value, let's say, 0:

update paymants
set amount = ''
or amount = 0
where amount is null

Next, fetch the records:

select amount, distributorid
from paymants

Hope these solutions cleared your confusion.

Upvotes: 0

user2035827
user2035827

Reputation:

SELECT amount, distributorid FROM paymants WHERE amount IS NOT NULL

Upvotes: 0

Bjørn
Bjørn

Reputation: 1148

If you want the rows that has NULL values, but that the value should be 0 instead you can write:

SELECT ISNULL(amount,0), distributorid FROM paymants

Link with info about ISNULL -> http://technet.microsoft.com/en-us/library/ms184325.aspx

and as pointed out in many other answers, if you don't want those rows to be returned at all, you can simply write:

select amount, distributorid 
from paymants
where amount is not null

Upvotes: 3

Freud
Freud

Reputation: 11

You can filter out columns with null values for amount using 

SELECT amount, distributorid FROM paymants WHERE amount IS NOT NULL

However, if you're expecting amounts for all rows, then it may be a good idea to ask why those nulls are being inserted in the first place.

When you define the table, if there's a column in which you don't want to allow null values, you can specify the NOT NULL property

CREATE TABLE example ( someColumn INT NOT NULL )

Upvotes: 1

Kaf
Kaf

Reputation: 33809

You should use is null (or is not null) to filter null values.

select amount, distributorid 
from paymants
where amount is not null

If you need all records with null amount with another value (say, -1) you could use isnull or coalesce as below.

select coalesce(amount,-1) amount, distributorid 
from paymants

Or, if you need only amount null records, you could do;

select amount, distributorid 
from paymants
where amount is null

Upvotes: 13

Yosi Dahari
Yosi Dahari

Reputation: 6999

If you have no filter (WHERE clause) then rows won't be filtered.

SELECT amount, distributorid 
FROM paymants
WHERE amount IS NOT NULL

Upvotes: 0

Related Questions