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
 At a loss

Author  Topic 

Taragor
Starting Member

46 Posts

Posted - 2013-05-02 : 14:15:28

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

1974 Posts

Posted - 2013-05-02 : 14:19:19
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 - 2013-05-02 : 14:21:56
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

1974 Posts

Posted - 2013-05-02 : 14:23:08
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

30421 Posts

Posted - 2013-05-02 : 14:29:58
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 - 2013-05-02 : 14:40:06
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

1974 Posts

Posted - 2013-05-02 : 14:48:04
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 - 2013-05-02 : 15:08:05
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

1974 Posts

Posted - 2013-05-02 : 15:30:47
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 - 2013-05-02 : 16:19:26
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
   

- Advertisement -