ADF -- Extract from SAP with RFC table option, filter on 2 or more columns

Question

ADF -- Extract from SAP with RFC table option, filter on 2 or more columns

Yannick Seynhaeve 0

Hello

Is there any way we can use multiple conditions on the RFC Table Option field of a copy activity?

Something like this:

User's image

Doing this on 1 column works. However having multiple conditions doesn't seem to work. I've seen that 'AND' operator or 'OR' operator aren't supported.

What's the equivalent for these options in ADF/RFC?

Thanks in advance!

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-22T11:16:31.63+00:00
@Yannick Seynhaeve Thank you for reaching out to the Azure community forum. I understand that you're trying to use multiple conditions on the RFC Table Option field of a copy activity in Azure Data Factory (ADF), but it doesn't seem to work. The RFC Table Option field only supports a single condition at a time, and the 'AND' and 'OR' operators are not supported.

However, there are some workarounds that you can try:

Instead of using the RFC Table Option field, you can create a stored procedure in SAP that performs the required filtering and returns the filtered data to ADF. You can then use the stored procedure in ADF to extract the data using the Stored Procedure activity.

You can use the Custom query option in the Copy activity to specify a custom query that includes the required filtering conditions. This option allows you to use SQL-like syntax to filter the data.

References:

Microsoft Learn: Extract data from SAP using Azure Data Factory

Microsoft Q&A: RFC table options

Stack Overflow: Select distinct from a column using RFC Table Options ADF

Hope this info helps. Let us know how it goes.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-25T10:11:11.0166667+00:00

@Yannick Seynhaeve We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Yannick Seynhaeve 0 Reputation points

2023-09-25T11:14:38.4733333+00:00

@Smaran Thoomu Configuring Stored Procedures at SAP side doesn't seem ideal to us. We think that the custom query option you gave is the one we are using currently (does not support OR, AND). This only supports a few operators. Currently we are looking into using SAPS4HANA as an extractor instead in order to resolve our issue.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-26T17:17:51.85+00:00

@Yannick Seynhaeve I understand. It is not ideal to configure stored procedures at the SAP side for every use case.
I am glad that you are looking into using SAP HANA as an extractor instead. SAP HANA extractors are more flexible and support a wider range of operators than the custom query option.

To use SAP HANA as an extractor, you will need to create a data source in your SAP BW or Data Factory system. The data source will specify the SAP HANA extractor that you want to use.
Once done, you can create a data flow or ETL process to extract the data from SAP HANA. The data flow or ETL process will use the SAP HANA extractor to fetch the data and load it into your target system.
Yannick Seynhaeve 0 Reputation points

2023-09-27T05:48:12.2733333+00:00

@Smaran Thoomu Thanks for the feedback. Whilst looking into SAP HANA we decided to give the original extracter another go. Apparantly we have a workaround if we use 'AND' with 2 variables. Syntax would be as followed:

@ variable1.yourparameters here AND @variable2.yourparameters here

We are not exactly sure why this works, but it does seem to do the trick. In this case our variable would each have 1 column we want to filter.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-28T12:07:44.87+00:00

@Yannick Seynhaeve Thank you for sharing your workaround with us. I'm glad to hear that you were able to use the 'AND' operator with two variables to filter the data in the RFC Table Option field of the copy activity in Azure Data Factory.

The syntax you provided looks correct, you can reference the variables in the RFC call. The 'AND' operator is used to combine the two conditions, and the conditions are enclosed in parentheses.

It's possible that the previous issues you encountered were related to the syntax or formatting of the conditions. By using the correct syntax and formatting, you were able to successfully filter the data.
If you have any further questions or concerns, please feel free to ask.

2 answers

Your answer

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-22T11:16:31.63+00:00

@Yannick Seynhaeve Thank you for reaching out to the Azure community forum. I understand that you're trying to use multiple conditions on the RFC Table Option field of a copy activity in Azure Data Factory (ADF), but it doesn't seem to work. The RFC Table Option field only supports a single condition at a time, and the 'AND' and 'OR' operators are not supported.

However, there are some workarounds that you can try:

Instead of using the RFC Table Option field, you can create a stored procedure in SAP that performs the required filtering and returns the filtered data to ADF. You can then use the stored procedure in ADF to extract the data using the Stored Procedure activity.

You can use the Custom query option in the Copy activity to specify a custom query that includes the required filtering conditions. This option allows you to use SQL-like syntax to filter the data.

References:

Microsoft Learn: Extract data from SAP using Azure Data Factory

Microsoft Q&A: RFC table options

Stack Overflow: Select distinct from a column using RFC Table Options ADF

Hope this info helps. Let us know how it goes.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-25T10:11:11.0166667+00:00

@Yannick Seynhaeve We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Yannick Seynhaeve 0 Reputation points

2023-09-25T11:14:38.4733333+00:00

@Smaran Thoomu Configuring Stored Procedures at SAP side doesn't seem ideal to us. We think that the custom query option you gave is the one we are using currently (does not support OR, AND). This only supports a few operators. Currently we are looking into using SAPS4HANA as an extractor instead in order to resolve our issue.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-26T17:17:51.85+00:00

@Yannick Seynhaeve I understand. It is not ideal to configure stored procedures at the SAP side for every use case.
I am glad that you are looking into using SAP HANA as an extractor instead. SAP HANA extractors are more flexible and support a wider range of operators than the custom query option.

To use SAP HANA as an extractor, you will need to create a data source in your SAP BW or Data Factory system. The data source will specify the SAP HANA extractor that you want to use.
Once done, you can create a data flow or ETL process to extract the data from SAP HANA. The data flow or ETL process will use the SAP HANA extractor to fetch the data and load it into your target system.
Yannick Seynhaeve 0 Reputation points

2023-09-27T05:48:12.2733333+00:00

@Smaran Thoomu Thanks for the feedback. Whilst looking into SAP HANA we decided to give the original extracter another go. Apparantly we have a workaround if we use 'AND' with 2 variables. Syntax would be as followed:

@ variable1.yourparameters here AND @variable2.yourparameters here

We are not exactly sure why this works, but it does seem to do the trick. In this case our variable would each have 1 column we want to filter.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-28T12:07:44.87+00:00

@Yannick Seynhaeve Thank you for sharing your workaround with us. I'm glad to hear that you were able to use the 'AND' operator with two variables to filter the data in the RFC Table Option field of the copy activity in Azure Data Factory.

The syntax you provided looks correct, you can reference the variables in the RFC call. The 'AND' operator is used to combine the two conditions, and the conditions are enclosed in parentheses.

It's possible that the previous issues you encountered were related to the syntax or formatting of the conditions. By using the correct syntax and formatting, you were able to successfully filter the data.
If you have any further questions or concerns, please feel free to ask.

Answer 1

The filter syntax used in that field is not standard SQL syntax. As per the feedback you provided, it seems that using multiple conditions with standard SQL operators such as 'AND' or 'OR' doesn't work.

If you can't filter on multiple columns simultaneously, consider performing multiple copy activities in sequence, each with a filter on a different column. This may not be efficient and might add complexity to your pipeline, but it's a workaround.

If you are able to perform a more general extraction from SAP (with broader criteria) and use subsequent activities in Azure Data Factory to further filter and refine the data go for it . You can do it using Data Flow or Data Bricks activities, depending on the volume and complexity of the transformations required.

If possible (I don't have a big knowledge of SAP Hana), you may think about encapsulating the complex logic/filter on the SAP side, like creating a custom RFC or a view in SAP that encapsulates this logic and then copying the data from this custom entity.

Answer 2

MADHUSUDAN PANWAR 101

Hi,
I am able to use 'AND and 'OR' operator in RFC Function module. As an example, you can simply say- GJAHR EQ '2024' AND BUKRS IN ('VALUE1', 'VALUE2')

GJAHR EQ '2024' OR BUKRS IN ('VALUE1', 'VALUE2')

Let me know if it helped or you found any alternative solution.

Thanks,
Maddy

Share via

ADF -- Extract from SAP with RFC table option, filter on 2 or more columns

2 answers

Your answer