on 03-04-2014 3:59 AM
Hi,
There is a custom function defined and it is called in the query transform as function call output.
Custome Function.
IF ( $PREV_NAME IS NULL )
begin
$PREV_NAME = $CURR_NAME ;
end
else
begin
$PREV_NAME = $PREV_NAME || ',' || $CURR_NAME ;
end
Return $PREV_NAME ;
Am getting the output always only $Curr_name parameter value, here Prev_name parameter is initialized NULL in work flow stage.
please advise here,why else part is not working and let me know if you need any further information.
Thanks.
Best Regards,
Edu.....
How do you call your function? What are the values passed for your parameters?
Assuming $PREV_NAME and $CURR_NAME are both input parameters (and not global variables), and you always pass a null value for $PREV_NAME, it's obvious the else-part of your logic will never be executed, isn't it?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Thanks for your response, the thing is that as a new mumber, am practicing some of the custom functions and scenario's. as such ,I had issue with the below scenario.
Here I had declared the input parameter initail value as NULL in work flow , not in the job level because unable to assign at job level and executed the data flow as a result I am getting
output is only currecnt values ie not appending the previous values..else is not working.
Consider the following Source data in a flat file:
DEPTNO | ENAME |
20 | G |
10 | A |
10 | D |
20 | E |
10 | B |
10 | C |
20 | F |
20 | H |
Scenario 5: Lets try to load the target table data as below:
DEPTNO | ENAME_LIST |
10 | A |
10 | A,B |
10 | A,B,C |
10 | A,B,C,D |
20 | A,B,C,D,E |
20 | A,B,C,D,E,F |
20 | A,B,C,D,E,F,G |
20 | A,B,C,D,E,F,G,H |
Solution:
At the Job level i.e. Context JB_SCENARIO_5, we initialize the Parameter $PREV_NAME using the Calls tab. We set the Agrument value to NULL.
Within the Custom Function Smart Editor, first we Insert two Parameters, namely $CURR_NAME and $PREV_NAME with Data types as varchar(20) and varchar(100) respectively. Their Parameter type being Input and Input/Output respectively.
Also we modify the Return Parameter Data type to varchar(100).
if ( $PREV_NAME IS NULL )
$PREV_NAME = $CURR_NAME;
else
$PREV_NAME = $PREV_NAME || ',' || $CURR_NAME;
Return $PREV_NAME;
The purpose of defining the Parameter and Custom Function is to perform Parameter Short-circuiting. Here within the function, we basically set the $PREV_NAME Parameter of type Input/Output to concatenate all employee names till the current processing row. Since it is of type Input/Output the concatenated string value is passed back into the Dataflow Parameter. So by using Custom Function we can modify and pass values to a Dataflow Parameter. Hence the Parameter defined at Dataflow level is short-circuited with the Input/Output Parameter of the Custom Function.
Next we specify a New Function Call in Schema Out of the Query transform. Choose the Custom Functions from the Function categories and select the Function name CF_CONCAT_ENAME.
Next we Define Input Parameters. We specify the inputs as below:
$CURR_NAME = QRY_SORT.ENAME
$PREV_NAME = $PREV_NAME
Select the Return column as the Output Parameter.
Please advise am I done mistake here..
Best Regards,
Edu
Hi Edukondalu ,
For your requirement custom function is very simple .
If you write the custom function as shown below
$L_DEPTNO = $P_DEPTNO;
$L_PREV_DEPTNO = $P_PREV_DEPTNO ;
$L_ENAME = $P_ENAME ;
IF( $L_DEPTNO <> $L_PREV_DEPTNO OR $L_PREV_DEPTNO is null )
begin
$L_ENAMEOUT = '';
$L_ENAMEOUT = $L_ENAME;
end
else
begin
$L_ENAMEOUT = $L_ENAMEOUT || ','|| $L_ENAME ;
end
Return $L_ENAMEOUT;
Your output will be
DEPTNO | PREV_DEPT | ENAME | ENAME_OUT |
10 | NULL | A | A |
10 | 10 | B | A,B |
10 | 10 | C | A,B,C |
10 | 10 | D | A,B,C,D |
20 | 10 | E | E |
20 | 20 | F | E,F |
20 | 20 | G | E,F,G |
20 | 20 | H | E,F,G,H |
If you change the custom function as shown below
$L_DEPTNO = $P_DEPTNO;
$L_PREV_DEPTNO = $P_PREV_DEPTNO ;
$L_ENAME = $P_ENAME ;
IF( $L_PREV_DEPTNO is null )
begin
$L_ENAMEOUT = '';
$L_ENAMEOUT = $L_ENAME;
end
else
begin
$L_ENAMEOUT = $L_ENAMEOUT || ','|| $L_ENAME ;
end
Return $L_ENAMEOUT;
Your output will become
DEPTNO | PREV_DEPT | ENAME | ENAME_OUT |
10 | NULL | A | A |
10 | 10 | B | A,B |
10 | 10 | C | A,B,C |
10 | 10 | D | A,B,C,D |
20 | 10 | E | A,B,C,D,E |
20 | 20 | F | A,B,C,D,E,F |
20 | 20 | G | A,B,C,D,E,F,G |
20 | 20 | H | A,B,C,D,E,F,G,H |
I hope you understand .
Thanks & Regards,
Ramana.
Hi,
The inital argument input parameter $PREV_NAME = NULL is declared at work flow level so the
below fuction is calling for others rows then it is alwasys taking the NULL value
Custom Function
if ( $PREV_NAME IS NULL )
$PREV_NAME = $CURR_NAME;
else
$PREV_NAME = $PREV_NAME || ',' || $CURR_NAME;
Return $PREV_NAME;
Output for this custom function:
10 | A |
10 | B |
10 | C |
10 | D |
20 | E |
20 | F |
20 | G |
20 | H |
parameter should be initailse as a starting otherwise getting an errors.
Best Regards,
Edu
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.