Hi,

In DataMigrator 7.7 a UNION was introduced, which was not yet available in 7.6. We still have some some flows on DM 769 which have for several reasons not yet migrated to higher version.

In DM 76 it is possible to do a UNION select by using a DBMS SQL Flow, but it seems I hit on some limitations.

The following simple union works:

SELECT a, b, c FROM table1 UNION SELECT a, b, c FROM table2  

If I want to join this Union select to another table I need to make the following construction:

SELECT T1.d, T2.a, T2.b
FROM table3 T1
LEFT OUTER JOIN
  (SELECT a, b, c FROM table1 UNION SELECT a, b, c FROM table2) AS T2 
ON T1.c = T2.c 

This works when used directly in the database, but DMC won't accept.
I have tried both in DB2 and SQL Server.
Has anyone ever managed to get this kind of SQL construct working in DM Flow 7.6?

thanks, Martin.

This message has been edited. Last edited by: Martin vK,

I've done many very complex SQL statements within the DBMS flow 'box', but not one exactly as you described. So I created an SQL statement similar to yours back in the DM 7.6 version, and, as you said, it didn't like it (FYI - it does work OK in DM 7.7.6).
I then defined the SQL as a stored procedure in the database. Within D.M., in an RDMS Flow, you can then put in an EXEC statement for your stored procedure; e.g.:

EXEC SP_TEST

which will execute the query and return the results for use by your flow.

NOTE: The DBMS flow will need to use an adapter that has read-write capabilities; otherwise you will get a permission error when you try to run it.

I've not used this direct exection of a Stored Proc from within a DBMS flow before, but it seems to work with the 'TEST' button.

This might be an option for you.

Hi Sharon,

Thanks for your reply and solution. I hoped there would be some way to do it in DM 7.6 but apparantly not.
I have made a view in the RDBMS and then it works fine.

Martin.