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] && variables in SQL Flow
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] && variables in SQL Flow
 Login/Join
 
Platinum Member
posted
I am making several DM flows with && variables, which are set by a stored procedure which runs earlier in the Process Flow. This makes the flow more dynamic and reuseable.

I got this working well in a normal Flow by transforming it first to a virtual field in the Source Tranformations.
DEFINE EIGP_OMS/A80 MISSING ON=&&EIGP_OMS.EVAL; $

This does not work however for a DBMS SQL Flow where there are no Source Transformations.
I can use the && variable in de SQL, e.g.
WHERE periodelabel = &&BSBHIS_PERIODELABEL
This however gives a warning: Can not parse DBMS SQL (FOC295) A value is missing for &&BSBHIS_PERIODELABEL. Proceed anyway?

If I just proceed, the SLQ Flow still does work fine as intended. The problem however is that I can not access the Target Transformations via the DM screens anymore, as the Selected Columns are not retrieved from the SQL because of the parsing issue.
I can still work with the flow by using "Edit by Text", but I am not fully happy with this solution.

Does anyone have a suggestion how I can better work with && variables in DBMS SQL Flows?

Martin.

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


WebFocus 7.7.06M/8.2.03M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 149 | Registered: March 29, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
Have you tried setting a local variable to equal the global one and then using the local?


8.1.05M AIX Client Windows Tomcat
DB2, Terradata, SQL, Oracle
 
Posts: 52 | Location: Fort Worth, Texas USA | Registered: January 27, 2012Reply With QuoteReport This Post
Guru
posted Hide Post
I see in another post that you are using 7.6.4 vintage 2007. We've much improved support for variables, local and global, over the last five years and I strongly advise using the current production 7.7.05 release.

In any event DataMigrator needs a value for the global variable.

That does imply a workaround. Remove the WHERE condition from you SELECT statement. Then you can go to the target transformations panel and the source columns will appear. Once you have completed your work there return to the SQL object and replace the WHERE condition.

Then if you have not already done so, create and save a stored procedure, say SETVAR, that assigns a value to the variable.
-SET &&BSBHIS_PERIODELABEL = 'somevalue';

On your original DBMS SQL Flow click on the Process Flow tab, delete the line between Start and Data Flow, Drag SETVAR between them, and connect Start to SETAVAR and it to Data Flow. Save and RUN the flow.

Note that while you can run the flow you cannot Test SQL Statement because there is no value for the global variable in the current session. This is addressed in later releases.
 
Posts: 373 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Clif, thanks for your reply.
Actually this issue is in 7.7.03, 7.6.4 we also use and are slowly migrating.


WebFocus 7.7.06M/8.2.03M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 149 | 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] && variables in SQL Flow

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