Row Filter & Join Filter 
Author Message
 Row Filter & Join Filter

Hello

We are using merge replication to sinchronize the data between a SQL Server
CE device and our main SQL Server. When specifying the publication, I can
use row filters and join filters, but the problem is that it seems that join
filters exclude the row filters. From what I've read, I thoght that they
where complementary, that is, first, the row filter is executed, and after
that, the join filter comes to see if there are any other rows that must be
filtered.

Let's say I have this tables:

Clients
Id    Name    Age
--------------------
1    Jim            23
2    Tom          34

Orders
Id    ClientID    Quantity
-----------------------------
1        1                5
2        2                12
3        1                15

I want to get the rows that are of client 1, and have quantity bigger than
10. I put a row filter in Clients table, "WHERE Id=1". Then put a JOIN
filter with Orders, "(clients.id=orders.clientid)". So I get orders 1 and 3.
Besides, I want the orders that are bigger than 10, so I add a row filter to
Orders, "WHERE Quantity>10", but I keep getting orders 1 and 3 when I
synchronize. If I remove the JOIN filter, I get only order number 1.

Is this how is should work, or am I doing something wrong?

Thanks in advance

Igor Mendizabal
R.P.S.



Mon, 28 Feb 2005 19:44:15 GMT
 Row Filter & Join Filter

Did you recreate the snapshot before reinitializing after the filter
change??
EM normally checks for this kind of thing, but not always... (minor bug??)

Edmund.


Quote:
> Hello

> We are using merge replication to sinchronize the data between a SQL
Server
> CE device and our main SQL Server. When specifying the publication, I can
> use row filters and join filters, but the problem is that it seems that
join
> filters exclude the row filters. From what I've read, I thoght that they
> where complementary, that is, first, the row filter is executed, and after
> that, the join filter comes to see if there are any other rows that must
be
> filtered.

> Let's say I have this tables:

> Clients
> Id    Name    Age
> --------------------
> 1    Jim            23
> 2    Tom          34

> Orders
> Id    ClientID    Quantity
> -----------------------------
> 1        1                5
> 2        2                12
> 3        1                15

> I want to get the rows that are of client 1, and have quantity bigger than
> 10. I put a row filter in Clients table, "WHERE Id=1". Then put a JOIN
> filter with Orders, "(clients.id=orders.clientid)". So I get orders 1 and
3.
> Besides, I want the orders that are bigger than 10, so I add a row filter
to
> Orders, "WHERE Quantity>10", but I keep getting orders 1 and 3 when I
> synchronize. If I remove the JOIN filter, I get only order number 1.

> Is this how is should work, or am I doing something wrong?

> Thanks in advance

> Igor Mendizabal
> R.P.S.



Tue, 01 Mar 2005 18:48:39 GMT
 Row Filter & Join Filter
Hello, Edmund

Yes, I did recreate it after the change in the real application, and
besides, I made a smaller project with a smaller publication of the same
type to recreate the problem, and put both filters from the first time,and
still couldn't get it to work properly.

Any other ideas...

Anyways, am I the only person experiencing this?


Quote:
> Did you recreate the snapshot before reinitializing after the filter
> change??
> EM normally checks for this kind of thing, but not always... (minor bug??)

> Edmund.



> > Hello

> > We are using merge replication to sinchronize the data between a SQL
> Server
> > CE device and our main SQL Server. When specifying the publication, I
can
> > use row filters and join filters, but the problem is that it seems that
> join
> > filters exclude the row filters. From what I've read, I thoght that they
> > where complementary, that is, first, the row filter is executed, and
after
> > that, the join filter comes to see if there are any other rows that must
> be
> > filtered.

> > Let's say I have this tables:

> > Clients
> > Id    Name    Age
> > --------------------
> > 1    Jim            23
> > 2    Tom          34

> > Orders
> > Id    ClientID    Quantity
> > -----------------------------
> > 1        1                5
> > 2        2                12
> > 3        1                15

> > I want to get the rows that are of client 1, and have quantity bigger
than
> > 10. I put a row filter in Clients table, "WHERE Id=1". Then put a JOIN
> > filter with Orders, "(clients.id=orders.clientid)". So I get orders 1
and
> 3.
> > Besides, I want the orders that are bigger than 10, so I add a row
filter
> to
> > Orders, "WHERE Quantity>10", but I keep getting orders 1 and 3 when I
> > synchronize. If I remove the JOIN filter, I get only order number 1.

> > Is this how is should work, or am I doing something wrong?

> > Thanks in advance

> > Igor Mendizabal
> > R.P.S.



Tue, 01 Mar 2005 20:20:19 GMT
 Row Filter & Join Filter
Igor,

I created the tables and the publication as you described and it worked
fine - on SQL Server 2k sp2, not using CE.
If you state what versions are you running, someone may be able to help you
more easily.

regards,
Edmund.


Quote:
> Hello, Edmund

> Yes, I did recreate it after the change in the real application, and
> besides, I made a smaller project with a smaller publication of the same
> type to recreate the problem, and put both filters from the first time,and
> still couldn't get it to work properly.

> Any other ideas...

> Anyways, am I the only person experiencing this?



> > Did you recreate the snapshot before reinitializing after the filter
> > change??
> > EM normally checks for this kind of thing, but not always... (minor
bug??)

> > Edmund.



> > > Hello

> > > We are using merge replication to sinchronize the data between a SQL
> > Server
> > > CE device and our main SQL Server. When specifying the publication, I
> can
> > > use row filters and join filters, but the problem is that it seems
that
> > join
> > > filters exclude the row filters. From what I've read, I thoght that
they
> > > where complementary, that is, first, the row filter is executed, and
> after
> > > that, the join filter comes to see if there are any other rows that
must
> > be
> > > filtered.

> > > Let's say I have this tables:

> > > Clients
> > > Id    Name    Age
> > > --------------------
> > > 1    Jim            23
> > > 2    Tom          34

> > > Orders
> > > Id    ClientID    Quantity
> > > -----------------------------
> > > 1        1                5
> > > 2        2                12
> > > 3        1                15

> > > I want to get the rows that are of client 1, and have quantity bigger
> than
> > > 10. I put a row filter in Clients table, "WHERE Id=1". Then put a JOIN
> > > filter with Orders, "(clients.id=orders.clientid)". So I get orders 1
> and
> > 3.
> > > Besides, I want the orders that are bigger than 10, so I add a row
> filter
> > to
> > > Orders, "WHERE Quantity>10", but I keep getting orders 1 and 3 when I
> > > synchronize. If I remove the JOIN filter, I get only order number 1.

> > > Is this how is should work, or am I doing something wrong?

> > > Thanks in advance

> > > Igor Mendizabal
> > > R.P.S.



Tue, 01 Mar 2005 21:42:42 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Problem with filters and join filters in merge replication

2. joined dynamic filters VS dynamic filters [mergerepl]

3. Join Filters not filtering

4. Merge Replication with Dynamic Filter and Row Filter failure on two Deletes

5. Row filters don't filter enough

6. Merge Replication with join filters: Orhpaned rows at subscriber

7. transaction replication & filter row

8. Merge Row Filter Canceling Rows

9. How detect filter selected in filter axis?

10. How to filter many data in filter axis when using PiovtTable10

11. Filter Accross Date Range - Different Filter Value for Different Dates

12. Dynamic filter vs static filter...


 
Powered by phpBB® Forum Software