TSQl problem with filtering - 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

TSQl problem with filtering

Author

Topic

Emile
Starting Member

5 Posts

Posted - 2012-12-04 : 06:07:10

Hi?
My select, which I will make into a stored procedure.
Produces the correct number or rows,in the result table the UseId field and the IsEstimate field and NoteCode field are empty, now I want to just return those rows???
I tried using NULL in my condition but no rows were returned???

--- this is my query ---

declare @AreaId int, @SuburbId int, @CalcDate datetime
set @SuburbId = 8
set @AreaId = 1
set @CalcDate = '2012/11/01'

select UseId, S.SuburbId, count (*) as Tot,

M.LastCloseReading,
M.LastReadingDate,
M.LastUsage,
M.MeterNo,
S.Key21,

U.IsEstimate,
U.NoteCode,
U.AccessCode
from mlmStand S
join mlmMeter M
on S.StandId = M.StandId
and TypeId = 0
and Disabled = 0
and S.AreaId = @AreaId
and S.SuburbId = @SuburbId
left join mlmMeterUsage U
on M.MeterId = U.MeterId
and U.CalcDate = @CalcDate
/*
and U.IsEstimate = null
and U.NoteCode = null
and U.AccessCode = null
*/
group by S.SuburbId, LastCloseReading, LastReadingDate, M.LastUsage, M.MeterNo, S.Key21, U.IsEstimate, U.NoteCode, U.AccessCode, UseId

order by U.UseId

--- this is the result set ---

You'll notice the first 5 rows have no UseId, IsEstimate, NoteCode, AccessCode, I want to just return those five rows

UseId SuburbId Tot Reading Reading LastUsage MeterNo Key21 IsEstimate NoteCode AccessCode

8 1 0 2012/11/29 0 BPRA9683 F00300030002627300000
8 1 1403 2012/05/08 5 BZAC650 F00300030002627600000
8 1 5683 2012/08/10 208 9711427 F00300030002626400003
8 1 7278 2012/10/05 924 8940731 F00300030002626200000
8 1 102967 2012/10/05 3696 9310759 F00300030002626200000
4297308 8 1 23078 2012/11/27 36 542160 F00300030000275300021 0 0 0
4297314 8 1 12998 2012/11/27 0 CLJA340 F00300030000275300023 0 0 0
4325458 8 1 3948 2012/11/29 98 2003780 F00300030002627900000 0 0 0
4326045 8 1 8894 2012/11/29 358 600248 F00300030002629000000 1 0 9
4326046 8 1 2085 2012/11/29 363 8752 F00300030002629000000 1 0 9
4326047 8 1 5615 2012/11/29 48 900000146 F00300030002629900000 1 0 20
4326048 8 1 8521 2012/11/29 111 319296 F00300030002629900000 1 0 20
4326049 8 1 6503 2012/11/29 78 9900000125 F00300030002629800000 1 0 20
4326050 8 1 2863 2012/11/29 41 9000041 F00300030002630000000 0 0 0
4326051 8 1 4808 2012/11/29 108 825828 F00300030002630000000 1 0 20
4326052 8 1 9355 2012/11/29 49 9605533 F00300030002630400000 0 0 0
4326053 8 1 9107 2012/11/29 50 9605244 F00300030002630400000 0 0 0
4326054 8 1 3900 2012/11/29 22 713110 F00300030002627800000 0 0 0
4326055 8 1 4829 2012/11/29 100 CPHB427 F00300030002627800000 0 0 0
4326057 8 1 6702 2012/11/29 99 9000000148 F00300030002626600000 1 0 20
4326058 8 1 5629 2012/11/29 48 605532 F00300030002630500000 1 0 20
4326059 8 1 9015 2012/11/29 101 605317 F00300030002630500000 1 0 20
4326060 8 1 1947 2012/11/29 28 825979 F00300030002665900000 0 15 0
4326061 8 1 1665 2012/11/29 51 4167 F00300030002628600000 0 15 0
4326062 8 1 2061 2012/11/29 64 800445 F00300030002628400000 0 0 0
4326063 8 1 12041 2012/11/29 528 287020 F00300030002628300000 1 0 20
4326064 8 1 1086 2012/11/29 185 CSAB0731 F00300030002628200000 0 0 0
4326065 8 1 229 2012/11/29 62 826802 F00300030002628100000 1 0 20
4326067 8 1 7759 2012/11/29 58 826706 F00300030002628100000 0 0 0
4326068 8 1 971 2012/11/29 8 2003698 F00300030002628000000 0 0 0
4326069 8 1 9645 2012/11/29 82 605549 F00300030002628000000 1 0 20
4326070 8 1 5934 2012/11/29 56 600863 F00300030002629300000 0 0 0
4326071 8 1 5036 2012/11/29 16 99900368 F00300030002629400000 0 0 0
4326072 8 1 6171 2012/11/29 41 99900365 F00300030002629400000 0 0 0
4326073 8 1 2370 2012/11/29 38 20044841 F00300030002627700000 0 15 0
4326074 8 1 5589 2012/11/29 35 100294 F00300030002627500000 0 0 0
4326075 8 1 5102 2012/11/29 44 300463 F00300030002627400000 0 0 0
4326076 8 1 4692 2012/11/29 103 713 F00300030002627400000 0 15 0
4326077 8 1 6458 2012/11/29 34 700602 F00300030002629500000 0 0 0
4326078 8 1 5652 2012/11/29 34 500477 F00300030002629600000 0 0 0
4326079 8 1 6860 2012/11/29 32 600452 F00300030002629700000 0 0 0
4326080 8 1 538 2012/11/29 0 CRAF6733 F00300030002628700000 0 15 0
4326081 8 1 356 2012/11/29 133 200475 F00300030002627100000 1 0 20
4326083 8 1 8673 2012/11/29 59 100958 F00300030002627000000 0 0 0
4326084 8 1 5127 2012/11/29 56 713925 F00300030002626900000 0 0 0
4326085 8 1 8484 2012/11/29 118 826904 F00300030002626700000 1 0 9
4326086 8 1 4684 2012/11/29 0 200923 F00300030002628800000 0 15 0
4326087 8 1 4073 2012/11/29 22 8000359 F00300030002628900000 0 15 0
4326088 8 1 4018 2012/11/29 53 100749 F00300030002628900000 1 0 6
4326089 8 1 5344 2012/11/29 145 600333 F00300030002626500000 1 0 20
4326153 8 1 8455 2012/11/29 207 200286 F00300030002629100000 1 0 9
4326155 8 1 1545 2012/11/29 52 9204 F00300030002629200000 1 0 9
4326156 8 1 6924 2012/11/29 85 1902 F00300030002629800000 1 0 9
4326157 8 1 1241 2012/11/29 21 8000817 F00300030002630800000 1 0 9
4326158 8 1 8906 2012/11/29 66 825990 F00300030002630800000 1 0 9
4326159 8 1 9239 2012/11/29 189 595754 F00300030002630900000 1 0 9
4326160 8 1 4247 2012/11/29 -236 100598 F00300030002627200000 1 0 9
4326161 8 1 4525 2012/11/29 42 9000201 F00300030002627300000 1 0 9
4326162 8 1 7155 2012/11/29 89 940825821 F00300030002631000000 1 0 9
4326164 8 1 451 2012/11/29 132 790 F00300030002631000000 1 0 20
4326165 8 1 9782 2012/11/29 87 595723 F00300030002631100000 1 0 20
4326166 8 1 3370 2012/11/29 258 595720 F00300030002631100000 1 0 20
4326167 8 1 7374 2012/11/29 134 595622 F00300030002631200000 1 0 20
4326168 8 1 8021 2012/11/29 91 595716 F00300030002631200000 1 0 20
4326169 8 1 6526 2012/11/29 60 595717 F00300030002631300000 1 0 20
4326170 8 1 1852 2012/11/29 29 595721 F00300030002631300000 1 0 20
4326171 8 1 6518 2012/11/29 67 25771
1 0 20
4326172 8 1 5983 2012/11/29 69 25770 F00300030002631400000 1 0 20
4326173 8 1 6565 2012/11/29 90 825827 F00300030002631500000 1 0 20
4326174 8 1 6392 2012/11/29 71 595745 F00300030002631500000 1 0 20
4326175 8 1 1959 2012/11/29 133 4165 F00300030002628600000 1 0 9
4326176 8 1 2817 2012/11/29 109 284703 F00300030002628500000 1 0 9
4326177 8 1 11931 2012/11/29 206 1105944 F00300030002628300000 1 0 9
4352858 8 1 40514 2012/11/29 356 NWA915 F00300030002626400001 0 0 0
4352859 8 1 2577 2012/11/29 12 9090101063 F00300030002626400002 0 15 0
4352860 8 1 6525 2012/11/29 4652 090101063 F00300030002626400002 1 0 9
4388155 8 1 8244 2012/11/29 333 800037 F00300030002627700000 0 0 0

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-12-04 : 06:43:01

You can't use = null, but you can use:

WHERE UseId IS NULL