470,619 Members | 1,704 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,619 developers. It's quick & easy.

A little script help

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

Jul 23 '05 #1
6 1151
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
--

Jul 23 '05 #2
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

Jul 23 '05 #3
The following should work:

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

-Tom.

Jul 23 '05 #4
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
--

Jul 23 '05 #5
Perfect.
Cheers

Jul 23 '05 #6
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
--

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ron Stephens | last post: by
2 posts views Thread by Rech | last post: by
2 posts views Thread by Jai | last post: by
7 posts views Thread by Keyed4U | last post: by
1 post views Thread by Sander de Maaijer | last post: by
reply views Thread by nintesa | last post: by
8 posts views Thread by vanald04 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.