Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] DM76 SQL flow JOIN with a UNION
 Login/Join
 
Martin vK
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 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
Sharon R.
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, 2012Report This Post
Martin vK
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 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders