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,

Have you tried setting a local variable to equal the global one and then using the local?

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.

Clif, thanks for your reply.
Actually this issue is in 7.7.03, 7.6.4 we also use and are slowly migrating.