NoRows Property in Matrix does not work

I wanted to set the NoRows property for a matrix on a report to inform
the user when this condition occurs. I run the report by supplying
parameters that I am sure returns no data. Specifically, I am
determining the table to query based on user inputs. I provide inputs
that will look for a non existent table. I do an existence check on
the table to see if it is there to prevent a sql server error. If it
finds no table the query is not run. However, given that nothing is
returned the norows property is ignored and I get the error "Invalid
attempt to read when no data is present."
Could it be because the matrix requires an empty dataset back?That is what it was. I now ensure I return an empty dataset if the
table does not exist.

Non-equi Joins using <>

Can we replace the where clause operator of '<>' with a join syntax condition.

Could someone explain me the dynamics behind the following query:

select a.b, c.d from a inner join c on a.b <> c.d

This could effectively replace the NOT IN and EXCEPT operators

No.. <> wont work as you expected. You query is similar to the following query,

select * from a cross join cWhere a.b <> c.d

Since it is set opertation your query will guide you to make a cross join and it only filter those values which are present on both the tables.

You have to use NOT IN or NOT EXISTS on your query. Join will be compared on both tables row by row. The IN & EXISTS will be verified with the one table value with other tables rows of value.

|||

Since, I am avoiding the use of NOT IN & NOT EXISTS due to its obvious perofrmance degradations, I also wouldn't use a Cross Join (which I think for tables with more rows would be terribly slow). Hence, would I get a Join query to have a performance upgrade over them.

|||

Hi there,

NOT IN and NOT EXISTS are no the same when it comes to performance. NOT EXISTS is much better in terms of performance especially if you're checking for a specific value as the query would return a TRUE value if a match is found and therefore avoid further iterations.

|||

I tend to use NOT EXISTS in these situations to take advantage of the semi-joins. Also, beware that the EXCEPT operator is typically slower.

Here are some previous threads that discuss NOT IN versus NOT EXISTS:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299702&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726903&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087482&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=637335&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654087&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=532892&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607796&SiteID=1

Here is a thread that discusses NOT IN versus EXCEPT:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1021998&SiteID=1