Questions about filtering

OBEXTOOBEXTO Posts: 5
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.

Comments

  • 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/Vert ... lts_Filter ?
    The links mention Fields.Remove and FieldMapping.Remove here http://labs.red-gate.com/index.php/Filt ... a_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
  • 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.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Using the sample databases (WidgetStaging, WidgetProduction) and the "horizontal filter example" and adding the vertical filter by inserting the following lines after line #46
    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.
  • 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.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Thanks! Our internal reference number for this bug (if it is a bug) is CSD-129.
Sign In or Register to comment.