A little script help

[chudson007]

Using the data below as an example I am looking for help with script
that will return all rows of data where neither Field A or B are not 0
or Null
Name A B
John 2
John 1
John 0
John
Ste 1
Ste
Paul 5
Paul
Paul 0
Regards,
Ciarán

[David Portas]

Do you really mean where EITHER A or B are not 0 or not NULL? Try:

WHERE A>0 OR B>0

conversely:

WHERE NULLIF(A,0) IS NULL AND NULLIF(A,0) IS NULL

--
David Portas
SQL Server MVP
--

[chudson007]

I tried your suggestions without success.
Using the data above, I want to return.

Name A B
John 2
John 1
Ste 1
Paul 5
Regards,
Ciarán

[Thomas R. Hummel]

The following should work:

WHERE ISNULL(A, 0) <> 0 OR ISNULL(B, 0) <> 0

-Tom.

[David Portas]

This is where it helps if you include CREATE TABLE and INSERT
statements with your question. The following works for me:

CREATE TABLE YourTable (name VARCHAR(10), a INTEGER NULL, b INTEGER
NULL /* PRIMARY KEY ??? UNSPECIFIED */)

INSERT INTO YourTable (name,a,b)
SELECT 'John', 2 , NULL UNION ALL
SELECT 'John', NULL, 1 UNION ALL
SELECT 'John', 0 , NULL UNION ALL
SELECT 'John', NULL, NULL UNION ALL
SELECT 'Ste', NULL, 1 UNION ALL
SELECT 'Ste', NULL, NULL UNION ALL
SELECT 'Paul', 5 , NULL UNION ALL
SELECT 'Paul', NULL, NULL UNION ALL
SELECT 'Paul', NULL, 0

SELECT name, a, b
FROM YourTable
WHERE A>0 OR B>0

Result:

name a b
---------- ----------- -----------
John 2 NULL
John NULL 1
Ste NULL 1
Paul 5 NULL

What did you do differently and what result did you get?

Does this table have a primary key? It should do, and it helps if you
specify the key when you post a question.

--
David Portas
SQL Server MVP
--

[chudson007]

Perfect.
Cheers

[GeoSynch]

For clarity sake try:

WHERE ISNULL(A,0) <> 0
AND ISNULL(B,0) <> 0
GeoSynch

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Do you really mean where EITHER A or B are not 0 or not NULL? Try:

WHERE A>0 OR B>0

conversely:

WHERE NULLIF(A,0) IS NULL AND NULLIF(A,0) IS NULL

--
David Portas
SQL Server MVP
--