Hello,

Requirement - Initialization or rerun flow anytime with passing runtime parameters.

Created the procedure which has routine will will work according to parameter passed. But wondering is it possible to call data flow inside the routine each time (for example need to run for the 35 days) each day need to call the data flow.
Also question about - Is anyway can setup the transformation in data flow which can read the values set up in the procedure & how?

Any ideas will be great help !
Thanks in advance for your suggestions.

DM - 7.1

When you say 'procedure', if you mean a Data Migrator stored procedure, then I believe so. What I've done is call a stored procedure from another stored procedure, but I would think you could call a data flow too, or even process flow for that matter. And I also believe you can pass paramaters set in the stored procedure into the data flow and use them in your target transformations. For example, I have the months to be processed stored in a text file. I have a loop that reads through that text file and executes another stored procedure with the parms being passed in:

-*- the month(s) to be loaded are stored in this file
TABLE FILE LOAD_MONTHS
PRINT LOAD_MONTH
ON TABLE SAVE AS MONTH_SAVE
END
-RUN

-*- loop thru the values in the save file and run the process for each
-LOOP_START
-READ MONTH_SAVE NOCLOSE &LOAD_YYM.A6.
-IF &IORETURN EQ 1 GOTO END_LOOP;

-TYPE LOAD_YYM: &LOAD_YYM
-DEFAULT &&LOAD_YYM='&&LOAD_YYM'

EX CMASAP REQ_NAME=LOAD_MONTHS, CM_ASYNC=OFF, PARMS="&&LOAD_YYM=&LOAD_YYM"
-RUN

-GOTO LOOP_START

-END_LOOP


Then in the target transformations in your data flow, let's say you have a field in the target called LOAD_MONTH and you want the value of &&LOAD_YYM to go into that field. If it's defined as a numeric, you can set the target transformation expression for that field to &&LOAD_YYM. If it's alpha, then create a temporary field first that is numeric (we'll call it TEMP_LOAD_YYM), and make sure it's listed before LOAD_MONTH by moving it up in the list. Then set the target transformation expression for LOAD_MONTH to EDIT(TEMP_LOAD_YYM).

Good luck.

Hi Jessica,

Thanks for your response.
Basically I want the similar one but instead of calling procedure need to call the dataflow.
Here is the procedure I have

-SET &&DAYSBACK = 35;
-SET &FDAYBACK = -&DAYSBACK;
-SET &TDAYBACK = -&DAYSBACK + 1;
-SET &CUTOFFDAYS = -&DAYSBACK;

-START

-* Get the current date
-SET &YR = EDIT(&DATEYYMD,'9999') ;
-SET &MO = EDIT(&DATEYYMD,'$$$$$99') ;
-SET &DD = EDIT(&DATEYYMD,'$$$$$$$$99') ;

-* Compute for the To Date
-SET &TOX1 = &YR || &MO || &DD ;
-SET &TOX = AYMD(&TOX1 , &TDAYBACK, 'I8YYMD');
-SET &TY = EDIT(&TOX,'9999');
-SET &TM = EDIT(&TOX,'$$$$99');
-SET &TD = EDIT(&TOX,'$$$$$$99');
-SET &TOX2 = &TM || &TD || &TY ;
-SET &TODATE = EDIT(&TOX2, '99/99/9999 ') | EDIT('06:00:00','99:$99:$99');

-* Compute for the From Date
-SET &FOX = AYMD(&TOX1 , &FDAYBACK, 'I8YYMD');
-SET &FY = EDIT(&FOX,'9999');
-SET &FM = EDIT(&FOX,'$$$$99');
-SET &FD = EDIT(&FOX,'$$$$$$99');
-SET &FOX1 = &FM || &FD || &FY ;
-SET &FROMDATE = EDIT(&FOX1, '99/99/9999 ') | EDIT('06:00:00','99:$99:$99');

-SET &CUTOFFDT = AYMD(&YYMD , &CUTOFFDAYS, 'I8YYMD');
-TYPE &CUTOFFDT

-SET &CUTOFFDAYS = &CUTOFFDAYS + 1;
-SET &FDAYBACK = &FDAYBACK + 1;
-SET &TDAYBACK = &TDAYBACK + 1;
-IF &CUTOFFDAYS NE 0 GOTO START ELSE GOTO EXITPROC;

-EXITPROC

-EXIT

---------------
From above procedure Can I use the &CUTOFFDT and &FROMDATE and &TODATE in dataflow in source transformation which can be call inside this routine and do you what will be code looks like to call the dataflow ?

Like I said, while what I did called a stored procedure, I believe you can use the same technique and call a data flow instead. Where I have

-DEFAULT &&LOAD_YYM='&&LOAD_YYM'

EX CMASAP REQ_NAME=LOAD_MONTHS, CM_ASYNC=OFF, PARMS="&&LOAD_YYM=&LOAD_YYM"
-RUN


Copy that and change it around for what you need. Where I only had one variable, you will have three, and where you see 'REQ_NAME=', put your data flow name there. Then give it a shot.

Good Luck.

Thanks Jessica,

I tried and it works for calling the dataflow.
But had question for parameter CUTOFFDT is the parameter passed in the dataflow and it shows the value but only last value of my loop. For example I have two cutoffdt '20070911' and '20070912' in my loop. I gets the data in my target for only '20070912' any idea where to look for that ?

-TYPE CUTOFFDT: &CUTOFFDT
-DEFAULT &&CUTOFFDT='&&CUTOFFDT'

EX CMASAP REQ_NAME=FOCUS_TO_ORACLE, CM_ASYNC=OFF, PARMS="CUTOFFDT=&CUTOFFDT"
-RUN

-SET &CUTOFFDAYS = &CUTOFFDAYS + 1;
-SET &FDAYBACK = &FDAYBACK + 1;
-SET &TDAYBACK = &TDAYBACK + 1;
-IF &CUTOFFDAYS NE 0 GOTO START ELSE GOTO EXITPROC;

-EXITPROC

-EXIT

Assuming I'm following your correctly, then look back at my original post. If you have a loop where &CUTOFFDT gets set differently with each loop, then you'll need to execute your data flow within the loop, just like my example. For you, &CUTOFFDT gets set to 20070911, execute your data flow, loop around, set &CUTOFFDT to 20070912, execute your data flow again, etc., etc., until you've exhausted all the values that &CUTOFFDT can be set to. If you're executing your data flow outside the loop, then you will only get the last value of &CUTOFFDT. But if what you need is to have both 20070911 and 20070912 passed into a single execution of your data flow, then you'll need two variables, like &CUTOFFDT1 and &CUTOFFDT2.

I also noticed that your EX CMASAP doesn't look quite right. You have:

EX CMASAP REQ_NAME=FOCUS_TO_ORACLE, CM_ASYNC=OFF, PARMS="CUTOFFDT=&CUTOFFDT"
-RUN

I believe the PARMS= should be PARMS="&&CUTOFFDT=&CUTOFFDT"

Also make sure what you're referencing in your data flow is &&CUTOFFDT and not &CUTOFFDT.

This message has been edited. Last edited by: Jessica Bottone,

Viral

quote:

-* Get the current date
-SET &YR = EDIT(&DATEYYMD,'9999') ;
-SET &MO = EDIT(&DATEYYMD,'$$$$$99') ;
-SET &DD = EDIT(&DATEYYMD,'$$$$$$$$99') ;

-* Compute for the To Date
-SET &TOX1 = &YR || &MO || &DD ;

I saw this part of your procedure and I wondered why you do this.

The first input you start with is a value like '20070915' (today), then you split it in a year value '2007' a month value '09' and a day value '15'.
the last step is concatenate it in the same order so the end result is '20070915'.
I'm sure it works, but it was not needed.
If you take it out, the report wont be faster (not that anybody can measure it) but effective programming is important...

the other thing that I see is that you first put your daysback value into a '&&' constant. the double should only be used if you want it global used and not just in this program. (it might be a typo).

Hi Frank,

Yeah you are right to compute Todate and daysback initially use 35 days &daysback , but later want use as default and prompt for it so can override so just change to &&daysback.

Thanks,
Viral.

Hi All,

Able to resolve the issue. On my dataflow target was new.So every time when call the dataflow from the procedure it drop and create the target. Change to existing target and it works.

Thanks all again for your inputs.