Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSED] DM76 SQL flow JOIN with a UNION
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] DM76 SQL flow JOIN with a UNION
 Login/Join
 
Platinum Member
posted
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,


WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 8 | Registered: May 24, 2012Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSED] DM76 SQL flow JOIN with a UNION

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.