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 2000I have a table with monthly columns from Jan to DecThese 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 0QuerySelect Cola, Colb, Jan, Feb....from tablewhere (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 recordCan 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 tablewhere jan >0 OR feb >0 ......order by cola -Chad |
|
|
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 > 0Tar |
|
|
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 tablewhere jan != 0 OR feb != 0 ......order by cola -Chad |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-05-02 : 14:29:58
|
Select Cola, Colb, Jan, Feb, ...from dbo.Tablewhere 1 IN (jan, feb, mar, ...)order by cola N 56°04'39.26"E 12°55'05.63" |
|
|
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 |
|
|
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 @t1WHERE jan!=0 OR feb != 0 OR mar !=0Results:ID jan feb mar2 1.23 0.00 0.004 0.00 2.34 0.005 0.00 0.00 3.45-Chad |
|
|
Taragor
Starting Member
46 Posts |
Posted - 2013-05-02 : 15:08:05
|
SIGHChad: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 issueselect *from dbo.vw_espresso_GL_Masterwhere 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 codesecondaireand yes, the codeprimaire is 20011 not 2011 :)Tar |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-05-02 : 15:30:47
|
Can you post the results of:select *from dbo.vw_espresso_GL_Masterwhere substring(codeprimaire,3,5) = '20011'Thanks,-Chad |
|
|
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 isnot 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 spentTar |
|
|
|