cancel
Showing results for 
Search instead for 
Did you mean: 

Lookup_ext function not working in validation transform

vijy_mukunthan
Active Contributor
0 Kudos

Dear Experts

   I have requirement to create multiple rules inside the validation transform. All the rules i need to create is a lookup_ext function with multiple column validation. I create the same logic in query transforms its works fine. But when i use the same logic inside the validation transform with custom function its giving me the error. Kindly help to achieve it correctly.

  The automatic code generated for lookup_ext

lookup_ext([SE1EXTR.SE1EXTR.SE_KNVV_TABLE1,'PRE_LOAD_CACHE','MAX'], [KUNNR],[NULL],[KUNNR,'=',Query."Sell-to Customer No_",VKORG,'=',Query.SALES_ORG,VTWEG,'=',Query.DIST_CHAN,SPART,'=',Query.DIVISION],[],[New_Kunnr]) SET ("run_as_separate_process"='no', "output_cols_info"='<?xml version="1.0" encoding="UTF-8"?><output_cols_info><col index="1" expression="no"/>

</output_cols_info>' )

The screen shot gives the correct error message.

Thanks and Regards

Vijay Mukunthan

Accepted Solutions (0)

Answers (3)

Answers (3)

vijy_mukunthan
Active Contributor
0 Kudos

Dear All

  Thanks for your valuable information. But i found the solution myself. Inside the validation transformation if you use lookup function it cannot return other than success or fail message or Null or NOT Null. In the quary transformation only the required output from the lookup function can be achieved. I resolved the issue by using the below code inside the validation transform.

ifthenelse(kunnr = qry.kunnr and sales_org = qry.sales_org and division = qry.division and dist_chan = qry.dist_chan,1,0) = 1

  This result will give 2 one for success and another for fail. Based on that i filled by template table. The issue got resolved.

Thanks and Regards

Vijay Mukunthan

Former Member
0 Kudos

Hi VijayKumar,

Seems like you are using a variable, 'New_Kunnr' in your custom validation though its not visible in the screenshot that you shared for lookup_ext in the mapping section of the Query transform. Remove this variable from the custom condition and I think, you are good to go.

Regards,

Tarun.

former_member200473
Contributor
0 Kudos

Hi Vijaya,

you have not passed any comparison operator after lookup_ext() in validation transform.

you syntax should be

lookup_ext([SE1EXTR.SE1EXTR.SE_KNVV_TABLE1,'PRE_LOAD_CACHE','MAX'], [KUNNR],[NULL],[KUNNR,'=',Query."Sell-to Customer No_",VKORG,'=',Query.SALES_ORG,VTWEG,'=',Query.DIST_CHAN,SPART,'=',Query.DIVISION],[],[New_Kunnr]) SET ("run_as_separate_process"='no', "output_cols_info"='<?xml version="1.0" encoding="UTF-8"?><output_cols_info><col index="1" expression="no"/>

</output_cols_info>' ) = is not null.

Validation transform is used for validating an imput but you are trying to mapping same as QUERY transform.

Please let us know if you still has question or my understanding was not  correct.

Regards,

Shiva Sahu

vijy_mukunthan
Active Contributor
0 Kudos

Hi Shiva

  I did as per your suggestion. But when i execute the job i am getting error. see the error message in the screen shot.

Thanks and Regards

Vijay Mukunthan

venkataramana_paidi
Contributor
0 Kudos

Hi Vijay ,

As per Shiva suggestion you need to change the validation syntax  as shown below

lookup_ext([SE1EXTR.SE1EXTR.SE_KNVV_TABLE1,'PRE_LOAD_CACHE','MAX'], [KUNNR],[NULL],[KUNNR,'=',Query."Sell-to Customer No_",VKORG,'=',Query.SALES_ORG,VTWEG,'=',Query.DIST_CHAN,SPART,'=',Query.DIVISION],[],[New_Kunnr]) SET ("run_as_separate_process"='no', "output_cols_info"='<?xml version="1.0" encoding="UTF-8"?><output_cols_info><col index="1" expression="no"/>

</output_cols_info>' ) is not null.


Just typo mistake  remove the "=" in the syntax before is not null.

But by seeing the above error  it looks like some fields are not available that you are using in the syntax.

Please provide the input fields , which field do you need to return  and  what is the validation rule you are going to apply . I will have a look into this .

Thanks & Regards,

Ramana.



former_member200473
Contributor
0 Kudos

Thank You @Venkata Ramana Paidi  sir




Vijayakumar, I totally agree with Venkat and sorry for creating confusion

Shiva Sahu

vijy_mukunthan
Active Contributor
0 Kudos

Hi Venkata

  Please find my screenshot. As you suspect some fields are missing. The fields in the look up and target both have same no of Column. Kindly suggest how to address this issue.

Thanks and Regards

Vijay Mukunthan

former_member198401
Active Contributor
0 Kudos

Hi Vijay,

Can you do the lookup before the validation transform, then you only need to check if the column KUNNR is null or not using the custom condition comparison  operators.

I believe that you are trying to complicate this and you should be just using the lookup_ext function within a query transform

Regards

Arun Sasi

vijy_mukunthan
Active Contributor
0 Kudos

Hi Arun

  I know how to do before the validation. In fact in the query transform i already did that its working fine. But i want to build the same logic in validation tranform as all similar rule in one place for easy maintenance. I am sure by some way this logic can be made to work in validation transform.

Thanks and Regards

Vijay Mukunthan

former_member198401
Active Contributor
0 Kudos

Hey Vijay,

I know you have already tried the lookup in the query transform. I was just asking you to simplify the design and did not have any idea about reusability approach.

Would it be possible to set up a variable to hold the output value from the Lookup function and then just return the output variable.


In this way you can maintain all the rules under one script by initializing them with the help of variables


Hope this helps


Regards

Arun Sasi

former_member211387
Contributor
0 Kudos

Hi,

In a validation transform you are validating by the field. So if you are validating that one column against a complex condition criteria involving other columns, it will be better to hold the output in the query transform and then validate it in the validation transform.

kind regards

Raghu