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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 At a loss
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Taragor
Starting Member

46 Posts

Posted - 05/02/2013 :  14:15:28  Show Profile  Reply with Quote

Good afternoon all.

I'm trying to write what I would assume is an easy query but for some reason it's not working out the way it should be :)

SQL 2000

I have a table with monthly columns from Jan to Dec

These columns contain decimal type values.

What I'm trying to do is pull each record that has atleast 1 value within any of the month columns not equal to 0

Query

Select Cola, Colb, Jan, Feb....
from table
where (not jan = 0 or not feb = 0 or not mar = 0....)
order by cola

What is happening is as soon as it finds a value of 0 in any of the columns, it does not return the record

Can someone explain this to me cause I 'THOUGHT' I knew what I was doing sigh....


Thanks in advance,

Tar

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/02/2013 :  14:19:19  Show Profile  Visit chadmat's Homepage  Reply with Quote
Why are you tying to use NOT logic? Why not:

Select Cola, Colb, Jan, Feb....
from table
where jan >0 OR feb >0 ......
order by cola

-Chad
Go to Top of Page

Taragor
Starting Member

46 Posts

Posted - 05/02/2013 :  14:21:56  Show Profile  Reply with Quote
Chadmat:

Sorry I should have mentionned that the numbers can be negative, so I can't use > 0

Tar
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/02/2013 :  14:23:08  Show Profile  Visit chadmat's Homepage  Reply with Quote
Then you were on the right track.

Select Cola, Colb, Jan, Feb....
from table
where jan != 0 OR feb != 0 ......
order by cola

-Chad

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/02/2013 :  14:29:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Select Cola, Colb, Jan, Feb, ...
from dbo.Table
where 1 IN (jan, feb, mar, ...)
order by cola



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Taragor
Starting Member

46 Posts

Posted - 05/02/2013 :  14:40:06  Show Profile  Reply with Quote
Chadmat:

Still no go, I've tried with Not, I've tried with <> and now with != still won't work.

SwePeso:

Hmmm that won't work unless I have a column with a value of 1 in it no??? (Tried it, returns no rows)


Tar
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/02/2013 :  14:48:04  Show Profile  Visit chadmat's Homepage  Reply with Quote
declare @t1 table (ID int identity(1,1), jan decimal(3,2), feb decimal(3,2), mar decimal(3,2))

insert into @t1 values (0,0,0)
insert into @t1 values (1.23,0,0)
insert into @t1 values (0,0,0)
insert into @t1 values (0,2.34,0)
insert into @t1 values (0,0,3.45)

SELECT *
FROM @t1
WHERE jan!=0 OR feb != 0 OR mar !=0


Results:

ID jan feb mar
2 1.23 0.00 0.00
4 0.00 2.34 0.00
5 0.00 0.00 3.45

-Chad
Go to Top of Page

Taragor
Starting Member

46 Posts

Posted - 05/02/2013 :  15:08:05  Show Profile  Reply with Quote
SIGH

Chad:

Yes that works. But it doesn't work for my query ugh.

Ok, is it possible that the issue is that the query is going against a view rather than directly to a table? (Don't see why this should matter)

This is my actual query in case someone may think it's a syntax issue

select *
from dbo.vw_espresso_GL_Master
where substring(codeprimaire,3,5) = '20011'
and
(cy_b1b1_P01 != 0 or cy_b1b2_P01 != 0 or
cy_b1b1_P02 != 0 or cy_b1b2_P02 != 0 or
cy_b1b1_P03 != 0 or cy_b1b2_P03 != 0 or
cy_b1b1_P04 != 0 or cy_b1b2_P04 != 0 or
cy_b1b1_P05 != 0 or cy_b1b2_P05 != 0 or
cy_b1b1_P06 != 0 or cy_b1b2_P06 != 0 or
cy_b1b1_P07 != 0 or cy_b1b2_P07 != 0 or
cy_b1b1_P08 != 0 or cy_b1b2_P08 != 0 or
cy_b1b1_P09 != 0 or cy_b1b2_P09 != 0 or
cy_b1b1_P10 != 0 or cy_b1b2_P10 != 0 or
cy_b1b1_P11 != 0 or cy_b1b2_P11 != 0 or
cy_b1b1_P12 != 0 or cy_b1b2_P12 != 0 or
cy_b1b1_P13 != 0 or cy_b1b2_P13 != 0)
order by codesecondaire

and yes, the codeprimaire is 20011 not 2011 :)


Tar
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/02/2013 :  15:30:47  Show Profile  Visit chadmat's Homepage  Reply with Quote
Can you post the results of:

select *
from dbo.vw_espresso_GL_Master
where substring(codeprimaire,3,5) = '20011'

Thanks,

-Chad
Go to Top of Page

Taragor
Starting Member

46 Posts

Posted - 05/02/2013 :  16:19:26  Show Profile  Reply with Quote
Anyone have a few other choice variations of the word idiot? :(

The reason I kept saying there was an issue is that in 1 query I was pulling 3 summed fields and the other 4 summed fields. Well the totals weren't matching on 1 of the fields because the above query is excluding rows where 1 of the fields that isn't part of the exclusion is
not 0 while the exclusion fields are 0 so it would drop for 1, but not the other. #)($*($)#

Never mind, I'd say please forgive me for wasting peoples time as it's Friday, but since it's Thursday that just makes me feel like a schmuck!!!

Thanks everyone for your help though, I appreciate the effort and time spent

Tar
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000