Question Re: Custom Function related issue in Technology Q&A

Custom Function related issue

Former Member — Tue, 04 Mar 2014 03:59:07 GMT

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.....

Re: Custom Function related issue

former_member187605 — Tue, 04 Mar 2014 07:34:15 GMT

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?

Re: Custom Function related issue

Former Member — Tue, 04 Mar 2014 11:29:16 GMT

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:

1. Let us first define the Source File Format. This same file format will be reused for the next set of the scenario questions.

2. Next we use the same Batch Job, JB_SCENARIO_DS. Within the Job we create a Data Flow, say DF_SCENARIO_5.
3. At the Data flow level i.e. Context DF_SCENARIO_5, we Insert a new Parameter using the Definitions tab. Lets name it as $PREV_NAME with Data type varchar(100) and Parameter type as Input.

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.

4. Next we create a New Custom Function from the Local Object Library. Lets name it CF_CONCAT_ENAME.

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).

5. Next we define the custom function as below and Validate the same.

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.

6. Lets go back and design the Data flow. First of all we take the File Format defined earlier, from the Local Object Library as Source.

7. Next we place a Query transform, say QRY_SORT. First we select the columns DEPTNO and ENAME from the Schema In of the Query transform and Map to Output. Specify the ORDER BY on DEPTNO and ENAME in Ascending type.

8. Next we place a Query transform, say QRY_CONCAT_NAME. First we select the columns DEPTNO from the Schema In of the Query transform and Map to Output.

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.

9. Next we place a Query transform, say QRY_FORMAT. First we select the columns DEPTNO and Return from the Schema In of the Query transform and Map to Output. Rename the Return column to ENAME_LIST.
10. Finally we place a Template Table as Target in the Target Datastore.

Please advise am I done mistake here..

Best Regards,

Edu

Re: Custom Function related issue

former_member187605 — Tue, 04 Mar 2014 12:54:31 GMT

At first sight, should work. If you've followed those steps carefully.

Can you check your field lengths? Steps 3 and 4.

Also I do not immediately understand why you are unable to assign the paramter value at job level. Should not be a problem at all.

Re: Custom Function related issue

venkataramana_paidi — Tue, 04 Mar 2014 13:32:03 GMT

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.

Re: Custom Function related issue

Former Member — Tue, 04 Mar 2014 13:52:36 GMT

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