Query - 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

General SQL Server Forums

New to SQL Server Programming

Query

Author

Topic

devisetti
Starting Member

30 Posts

Posted - 2008-05-15 : 00:10:00

I have the following table with sample data like this

REC1 REC2
----------------
111 666
222 777
333 888
000 766
000 866
456 000
678 000

I want a query with results that should ignore zeros in the two columns.

Desired output:

REC1 REC2
----------------
111 666
222 777
333 888

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-15 : 00:21:55

SELECT TOP 3 REC1,REC2 FROM <TABLENAME>

devisetti
Starting Member

30 Posts

Posted - 2008-05-15 : 00:25:04

I have large volume of data.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-15 : 00:28:29

TRY THIS ALSO IF COL1 AND COL2 ARE OF INT TYPE

DECLARE @TEST TABLE ( COL1 INT, COL2 INT)
INSERT INTO @TEST
SELECT 111,343 UNION ALL
SELECT 365,675 UNION ALL
SELECT 000,3453 UNION ALL
SELECT 895,000 UNION ALL
SELECT 000,000
SELECT * FROM @TEST WHERE COL1 <> 0 AND COL2 <> 0

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-15 : 00:44:04

hi
try this
declare @tb table(rec1 int,rec2 int)
insert @tb
select 111,666
union all select 222,777
union all select 333,888
union all select 000,766
union all select 000,866
union all select 456,000
union all select 678,000
select * from @tb
select rec1,rec2 from @tb where rec1 !=0 and rec2 !=0

ok tanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 01:21:47

SELECT REC1,REC2 FROM YourTable Where (CAST(REC1 as int) + CAST(REC2 AS int))> 0

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-15 : 02:10:02

hi visakh

ur query is not giving expected result...
i think it can be changed as follows
SELECT REC1,REC2 FROM @tb Where CAST(REC1 as int)>0 and CAST(REC2 AS int)> 0

ok
tanx..

devils3cups
Starting Member

23 Posts

Posted - 2008-05-15 : 11:59:13

Whats wrong with
Select * from table
where rec1 != 000 or rec2 != 000;

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-16 : 05:25:33

quote:
Originally posted by devils3cups

Whats wrong with
Select * from table
where rec1 != 000 or rec2 != 000;

hi

no it wont give ur result
use below one
bcos u want to exclude 000 value from both columns
then only proper selection will be done
Select * from @tb
where rec1 != 000 and rec2 != 000
hopes it will clarify u

ok

devils3cups
Starting Member

23 Posts

Posted - 2008-05-16 : 09:51:05

quote:
Originally posted by soorajtnpki

quote:
Originally posted by devils3cups

Whats wrong with
Select * from table
where rec1 != 000 or rec2 != 000;

hi

no it wont give ur result
use below one
bcos u want to exclude 000 value from both columns
then only proper selection will be done
Select * from @tb
where rec1 != 000 and rec2 != 000
hopes it will clarify u

ok

Stupid mistake. I'm trying to get involved in the forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources