row filter in chiliren table

Yes - joins and filters are often used together,
especially in merge.
Rgds,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
hi paul:
so how about i set row fitlers at parent and set row filters at
children, does this work as well? what i thought is the children level
row fitler won't work if it's parent has row filters
, is that right?
Cheer
Nick
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:039a01c53504$51ba43b0$a401280a@.phx.gbl...
> Yes - joins and filters are often used together,
> especially in merge.
> Rgds,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Nick,
what you've hit on is v.interesting and is something that
I'll look into in more detail. A combination of parent
filter, child filter and join filter produces unexpected
results. I'll chase it up and post back when I've got
some more info, but if you look at the merge views, you
can see where the problem lies - there is a union
statement, and the filter only applies to one half of the
union.
Rgds,
Paul Ibison
|||Hi paul:
Firstly, do you mean it has a way to figure this problem out?
Secondly. where is merge views? is that a script of replication or
what?
Cheers
Nick
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:007701c535dc$9119c6b0$a501280a@.phx.gbl...
> Nick,
> what you've hit on is v.interesting and is something that
> I'll look into in more detail. A combination of parent
> filter, child filter and join filter produces unexpected
> results. I'll chase it up and post back when I've got
> some more info, but if you look at the merge views, you
> can see where the problem lies - there is a union
> statement, and the filter only applies to one half of the
> union.
> Rgds,
> Paul Ibison
>
|||Nick,
IMHO it is a bug.
If you have join filters, and static table filters on each table in the
join, the results are not filtered correctly, or at least not at all
intuitively.
eg, I have 2 tables: tParents and tChildren, joined by PK - FK relationship.
There is a filter clause on each table. The resulting (automatically
created) merge view for tParents is:
create view [ALNMerge_tParents_VIEW] as
select alias_A2E8A2147CA04D67BA17EF914868C4AA.* from [dbo].[tParents]
alias_A2E8A2147CA04D67BA17EF914868C4AA
where rowguidcol in ( select [tParents].rowguidcol from [dbo].[tParents]
[tParents] , [ALNMerge_tChildren_VIEW] [tChildren] where ( (
[tParents].[id] = [tChildren].[parentid]) ) and ({ fn
ISPALUSER('62E99837-892B-4157-82FF-793161691118') } = 1)
union
select [dbo].[tParents].rowguidcol from [dbo].[tParents]
where ( (tparents.id = 2) ) and ({ fn
ISPALUSER('62E99837-892B-4157-82FF-793161691118') } = 1) ) and ({ fn
ISPALUSER('62E99837-892B-4157-82FF-793161691118') } = 1)
The first part refers to the join, and the second refers to the filter, but
they are unioned, rather than the filter applying to each select. This means
that rows arriving as part of the join,
but which don't have an ID = 2 are returned!
Rgds,
Paul Ibison