Merge replication join filter clause can be subquery, self join?

Hi,
I need to filter the replicated rows of a table by using a subquery that
references to itself, or by doing a self join. My filter for the table
called "aCrediAnti" looks like:
Select *
From aCrediAnti t1
Where t1.ImpSaldoAntiMonOri > 0
Or exists(select * from aCrediAnti t2
Where t2.ClaOperProvCrediAnti = t1.ClaOperCrediProv
And t2.FolOperProvCrediAnti = t1.FolOperCrediProv
And t2.ClaEmp = t1.ClaEmp
And t2.ImpSaldoAntiMonOri > 0
)
Or may be also in the self join syntax
Select *
From aCrediAnti t1
Left Join aCrediAnti t2
On t2.ClaOperProvCrediAnti = t1.ClaOperCrediProv
And t2.FolOperProvCrediAnti = t1.FolOperCrediProv
And t2.ClaEmp = t1.ClaEmp
Where t1.ImpSaldoAntiMonOri > 0
Or (
Not t2.FolOperProvCrediAnti Is Null
And t2.ImpSaldoAntiMonOri > 0
)
My problem is how to specify such a filter. For both I need to specify table
aliases (t1, t2) for using them on the join, but neither the Enterprise
Manager and sp_addmergefilter (script version) allows me to use alias. What
can I do?
Thanks in advance
Faustino Dina
If my email address starts with two 'f'
drop the first 'f' when mailing me.
I think a UDF would be ideal for something like this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"faustino Dina" <ffdina@.matusa.com.mx> wrote in message
news:O91w%238DkEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I need to filter the replicated rows of a table by using a subquery that
> references to itself, or by doing a self join. My filter for the table
> called "aCrediAnti" looks like:
> Select *
> From aCrediAnti t1
> Where t1.ImpSaldoAntiMonOri > 0
> Or exists(select * from aCrediAnti t2
> Where t2.ClaOperProvCrediAnti = t1.ClaOperCrediProv
> And t2.FolOperProvCrediAnti = t1.FolOperCrediProv
> And t2.ClaEmp = t1.ClaEmp
> And t2.ImpSaldoAntiMonOri > 0
> )
> Or may be also in the self join syntax
> Select *
> From aCrediAnti t1
> Left Join aCrediAnti t2
> On t2.ClaOperProvCrediAnti = t1.ClaOperCrediProv
> And t2.FolOperProvCrediAnti = t1.FolOperCrediProv
> And t2.ClaEmp = t1.ClaEmp
> Where t1.ImpSaldoAntiMonOri > 0
> Or (
> Not t2.FolOperProvCrediAnti Is Null
> And t2.ImpSaldoAntiMonOri > 0
> )
> My problem is how to specify such a filter. For both I need to specify
table
> aliases (t1, t2) for using them on the join, but neither the Enterprise
> Manager and sp_addmergefilter (script version) allows me to use alias.
What
> can I do?
> Thanks in advance
> --
> Faustino Dina
> If my email address starts with two 'f'
> drop the first 'f' when mailing me.
>
|||> I think a UDF would be ideal for something like this.
Thanks! Despite it took me hours to decode what "UDF" means ;-) it looks it
works
Faustino

Merge replication and DTS

Hello,
I would like to use merge replication to populate data
into a table based on criteria...
How do I incorporate a where clause in the replication?
Is there a way to incorporate DTS packages in replication?
Thanks,
niv
Niv,
transformable subscriptions are possible, however they are a big overhead
and may not be required for your needs. When you say a where clause, this
corresponds to a horizontal filter in replication terms. Have a look at the
filter rows tab in the publication properties and you'll have the option of
entering static of dynamic filters there.
HTH,
Paul Ibison
|||Testing, 1,2,3...
Paul
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!