Help with WHERE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with WHERE

Author

Topic

furrelkt
Starting Member

49 Posts

Posted - 2005-10-04 : 15:26:31

I need some help please. I have a sql query that pulls ACTUAL_QTY and ETC_QTY hours from a view in that database.
SELECT
pf.ACTUAL_QTY,
pf.ETC_QTY
FROM
niku.table
WHERE
(ACTUAL_QTY IS NOT NULL AND ETC_QTY IS NOT NULL)

--this takes care of the nulls in the table, but what about the zero values? If both the actual_qty and etc_qty are zero, i don't want them pulled into the rows.
The results now are like this...
ACTUAL_QTY ETC_QTY
2.000000 .000000
.000000 .000000
.000000 .000000
.000000 .000000
.000000 1.00000
.000000 .000000
.000000 .000000
1.000000 .000000
.000000 .000000
1.000000 .000000
.000000 .000000
what i want is this, where the actual_qty and etc_qty TOGETHER are not zero.
ACTUAL_QTY ETC_QTY
2.000000 .000000
1.000000 .000000
1.000000 .000000
.0000000 1.00000

I have tried the ...
(ACTUAL_QTY <> 0 AND ETC_QTY <> 0)
this just results in nothing. Please, any help would be appreciated.

Keri

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-10-04 : 15:41:14

What is the data type used to store these values? if it is not Numeric type then CAST/CONVERt it.

Check is it ok for you....

select * from temp where (a <> 0 or b <> 0 )

With Regards
BSR

furrelkt
Starting Member

49 Posts

Posted - 2005-10-04 : 17:01:01

the data type is numeric, I have already tried to use the example you have given. I not sure what else to do.... thanks for your help. Do i need to post anything that might help to solve this? Thanks,
K

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-04 : 17:12:04

This should do it

SELECT
pf.ACTUAL_QTY,
pf.ETC_QTY
FROM
niku.table
WHERE
(ISNULL(ACTUAL_QTY, 0) <> 0 OR ISNULL(ETC_QTY, 0) <> 0)

Duane.

furrelkt
Starting Member

49 Posts

Posted - 2005-10-06 : 15:28:20

YOU ARE THE BEST!!~ Thanks Duane, this worked perfectly, after searching and asking for help for a few days. Again, your're great, thanks again.
Keri
:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources