(Less Than) vs. (Less Than Or Equal To)

Assume ID is an Integer field, which is faster (or is there any difference at all)?

Code Snippet

select * from <MyTable> where ID < 51

--or

select * from <MyTable> where ID <=50

You should get the same perf. It's just a constant filter.

|||Thanks much. That's been my experience, but wanted to make sure I wasn't leaving some performance on the table.

"where" statement negates join

it seems when i use a udf within a where clause my left join statement is ignored. for example...

Code Snippet

select errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description, count(cartonerrorandstatus.cartonentrynumber) as [count]

from errorsandstatus

join errorandstatustypes on errorsandstatus.typeid = errorandstatustypes.typeid

left outer join cartonerrorandstatus on errorsandstatus.typeid = cartonerrorandstatus.errorandstatustypeid and errorsandstatus.value=cartonerrorandstatus.errorandstatusvalue

left outer join cartonhistory on cartonerrorandstatus.cartonentrynumber = cartonhistory.entrynumber

where errorandstatustypes.availforrpt=1

group by errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description

order by errorsandstatus.typeid, errorsandstatus.value

returns

id name value desc count 1 Induction 0 Inducted 140 1 Induction 1 Too Close 0 1 Induction 2 Too Small 1 1 Induction 3 Too Big 0 2 Tracking 0 Tracked 141 2 Tracking 4 Missing 0 3 Destinize 1 Valid Barcode 3 3 Destinize 2 InValid Barcode 0

now when i use the exact same query only this time include 3 udf's within the where clause the left join seems to be ignored

Code Snippet

select errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description, count(cartonerrorandstatus.cartonentrynumber) as [count]

from errorsandstatus

join errorandstatustypes on errorsandstatus.typeid = errorandstatustypes.typeid

left outer join cartonerrorandstatus on errorsandstatus.typeid=cartonerrorandstatus.errorandstatustypeid and
errorsandstatus.value=cartonerrorandstatus.errorandstatusvalue

left outer join cartonhistory on cartonerrorandstatus.cartonentrynumber = cartonhistory.entrynumber

where errorandstatustypes.availforrpt=1 and dbo.udf_IsDateInRange(CartonHistory.Completed, @.DateRangeStart, @.DateRangeEnd) = 1 AND dbo.udf_IsDayInShift(CartonHistory.Completed, @.ShiftDaysOfWeek) = 1 AND dbo.udf_IsTimeInShift(CartonHistory.Completed, @.ShiftTimeStart, @.ShiftTimeEnd) = 1

group by errorsandstatus.typeid, errorandstatustypes.name, errorsandstatus.value, errorsandstatus.description

order by errorsandstatus.typeid, errorsandstatus.value

returns

id name value desc count 1 Induction 0 Inducted 26 2 Tracking 0 Tracked 26 3 Destinize 1 Valid Barcode 3 3 Destinize 4 No Read 4 4 Divert 1 Validated 6 4 Divert 3 Lane Full 1 5 WtStatus 1 Valid Weight 2 7 Barcode 1 Valid Read 3 7 Barcode 2 No Read 4

why does it ignore the left join when i evaluate the udf's in the where clause?

thanks!

Try to move parts of your WHERE statement into ON clause of desired left join|||

amazing...moved it into the second join and presto! thank you!

select errorsandstatus.typeid, errorandstatustypes.[name], errorsandstatus.value, errorsandstatus.[description],
count(cartonerrorandstatus.cartonentrynumber) as [count]
from errorsandstatus
join errorandstatustypes on errorsandstatus.typeid = errorandstatustypes.typeid
left outer join cartonerrorandstatus on errorsandstatus.typeid=cartonerrorandstatus.errorandstatustypeid and
errorsandstatus.value=cartonerrorandstatus.errorandstatusvalue
left outer join cartonhistory on cartonerrorandstatus.cartonentrynumber = cartonhistory.entrynumber
and dbo.udf_IsDateInRange(CartonHistory.Completed, @.DateRangeStart, @.DateRangeEnd) = 1
AND dbo.udf_IsDayInShift(CartonHistory.Completed, @.ShiftDaysOfWeek) = 1
AND dbo.udf_IsTimeInShift(CartonHistory.Completed, @.ShiftTimeStart, @.ShiftTimeEnd) = 1
where errorandstatustypes.availforrpt=1
group by errorsandstatus.typeid, errorandstatustypes.[name], errorsandstatus.value, errorsandstatus.[description]
order by errorsandstatus.typeid, errorsandstatus.value

|||The key to remember is that anytime you have a field from the right table in the WHERE clause, you effectively convert into an 'equi-JOIN'. But if what you are really attempting to do is filter the right table, do so in the JOIN conditions.|||thanks for the clarification arnie!sql

"Index was out of range" when two cells are merged

I receive this error during rendering when I have two cells merged together:

Error Snippet

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

When I "Split Cells" on the offending cells, it starts to work. This report renders without error when run on the RS server. This error only occurs when running the report locally on the "Preview" tab in the report designer.

I have closed the IDE and deleted the *.data files and restarted with the same results. Is there anyway to get more information about the error to help debug the problem?

Thanks!

~Jon

I would try copying the report content from your current report to the clipboard, deleting all of the content, and then pasting it back. This should allow the text boxes and such to be rebuilt and may clear up the issue. Keep a copy of the original RDL and compare the new (hopefully working) to the old.

Larry

|||

I found something...

It appears that I cannot merge cells in the top row in the Table Header. I can have merged cells in rows below the top row. Anytime I merge the cells on the top header row I get this index out of range error. I have no idea why. I tried it with another report and it works fine. It is just this particular report. The report is pretty simple. It does call a couple of subreports that are in the header. The crazy thing is that it renders with no problem on the RS server.

For now I just added a row at the top that doesn't contain any merged cells so that I can Preview locally as I work on the report.

~Jon