Red Gate forums :: View topic - Questions about filtering
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

Questions about filtering

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
OBEXTO



Joined: 21 Jan 2010
Posts: 4

PostPosted: Thu Jan 21, 2010 6:59 am    Post subject: Questions about filtering Reply with quote

Hello,

Is it possible to filter verticaly and horizontally ?
Example : I want to include a column when the row is of type In1, but this column should be excluded from synchronization when the row is of type Different.
I see a way to do this by generating two execution blocks with two different SelectionDelegate (one for the In1 and one for the Different) so that I can modifiy the TableDifference.ResultStore.Fields between the two block generation. Is it correct to try this ?

Is it possible to modify the type of a row ?
Example : I get a row of type Different or Same but I want to delete it in the second base, according to external criterias.

Thank you for advice.
Back to top
View user's profile Send private message
OBEXTO



Joined: 21 Jan 2010
Posts: 4

PostPosted: Fri Jan 22, 2010 6:11 am    Post subject: Precisions Reply with quote

I tried what I mentionned before, but I have a serious problem.

I use this method http://labs.red-gate.com/index.php/Vertical_SQL_Filter to remove a FieldPair.

Why is this code nearly the same as here http://labs.red-gate.com/index.php/Vertical_Results_Filter ?
The links mention Fields.Remove and FieldMapping.Remove here http://labs.red-gate.com/index.php/Filtering_data_overview


Then I use this method to filter the rows : http://labs.red-gate.com/index.php/FilterSQLExample

Now the result I get.
If I have a table with 3 columns A, B, C and I want to exclude B from insertion scripts, the script generated is :
INSERT(A, C) -- OK here
VALUES(a, b, c) -- a problem here

For update, it is even worth because the values are shifted, and there is not necessraly an error :
UPDATE T
SET A = a,
B = c
...

Any advice and workaround ?


Thanks
Back to top
View user's profile Send private message
OBEXTO



Joined: 21 Jan 2010
Posts: 4

PostPosted: Fri Jan 22, 2010 9:33 am    Post subject: Reply with quote

Trying to generate my own scripts I think I pointed out something.
Removing a FieldPair from the StoreFields change the content of the reader (a row doesn't contain values for the removed KeyPair).
Unfortunatly, it seems that the OrdinalInResults1 are not relocated.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6646

PostPosted: Mon Jan 25, 2010 5:23 pm    Post subject: Reply with quote

Using the sample databases (WidgetStaging, WidgetProduction) and the "horizontal filter example" and adding the vertical filter by inserting the following lines after line #46
Code:
 TableDifference diff = m_TableDifferences["[dbo].[Widgets]"];
diff.ResultsStore.Fields.Remove(diff.ResultsStore.Fields["Description"]);

Before the change, the resulting script is:
INSERT INTO [dbo].[Widgets] ([RecordID], [Description], [SKU]) VALUES (8, 'New widget', 'NW')
After adding the code from the vertical filter example, the insert changes but the number of columns and the number of values is consistent:
INSERT INTO [dbo].[Widgets] ([RecordID], [SKU]) VALUES (8, 'New Widget')
I am not getting more values than columns in my case, but the "description" value has moved to the "SKU" column. Taking the SelectionDelegate out of the equation still yeilds the same result for row 8.

I'd assume this is a bug -- clearly the Engine is making an effort to remove the value from the results as well as the field name, but it's not removing the right value. I'll have to get back to you on this one.
Back to top
View user's profile Send private message
OBEXTO



Joined: 21 Jan 2010
Posts: 4

PostPosted: Mon Jan 25, 2010 5:35 pm    Post subject: Reply with quote

I probably made a mistake reporting the issue with Insert. I have about 50 columns so maybe I didn't see that one was removed at the end. I focused on the fact that values were shifted.

If there is a bug to correct, there is no emergency as I wrote my own scripts (quite simple scripts) to meet our requirements.

Thanks.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6646

PostPosted: Wed Jan 27, 2010 6:56 pm    Post subject: Reply with quote

Thanks! Our internal reference number for this bug (if it is a bug) is CSD-129.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group