SQL Server Forums
Profile | Register | 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
 New Topic  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
30249 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000