CODEWITHSUNDEEP
sqlsql-serverview
Raymond
Raymond

Reputation: 63

SQL Server - Excluding records from a view if two certain fields are both null

I'm writing a view in SQL Server and I need the results filtered so that if two particular fields on a record have null values then that record is excluded.

As an example a table Customer has fields Code, Name, Address, Payment, Payment_Date.

If both Payment & Payment_Date are null then exclude the record from the result set, however if just one is null (or any other field) then it's fine to return the record.

Is this functionality possible in SQL Server?? Any help would be appreciated.

Upvotes: 2

Views: 2993

Answers (3)

user593806
user593806

Reputation:

You can do this in the where clause, simply turn it around and use an OR instead:

WHERE
(
  PAYMENT IS NOT NULL
  OR
  PAYMENT_DATE IS NOT NULL
)
AND
  -- ...rest of where clause here...

Upvotes: 0

Ralph Shillington
Ralph Shillington

Reputation: 21098

Take the coalesceof the two fields, and check that value for null

select * from yourtable where coalesce(field1, field2, field3, etc, etc,) is not null

this is somewhat easier on the eyes than a string of OR clauses (imho)

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425441

SELECT  *
FROM    mytable
WHERE   other_conditions
        …
        AND (payment IS NOT NULL OR payment_date IS NOT NULL)

Upvotes: 4

Related Questions