topic Re: Function in function in SELECT of new SQL in Application Development and Automation Discussions

Function in function in SELECT of new SQL

ekekakos — Tue, 11 May 2021 08:34:51 GMT

Hello, I have the below select and I want to ask if it is possible to do the below

SELECT CASE WHEN bseg~koart = 'D' THEN right( kna1~stceg, length( kna1~stceg ) )
            WHEN bseg~koart = 'K' THEN right( lfa1~stceg, 9 )
            ELSE ' '   
       END AS stceg
FROM kna1 WHERE konnr between 1000 and 2000
INTO TABLE @DATA(it_vat).

Is it possible to pass the length dynamically or I must do it later with a loop.

Thanks

Re: Function in function in SELECT of new SQL

Sandra_Rossi — Tue, 11 May 2021 12:31:09 GMT

You mean there's the syntax error "In the function "RIGHT", the parameter number "2" must be an ABAP variable. This is not the case for the expression that starts with "LENGTH"", and you're asking for a workaround.

The message is clear, so you should update IT_VAT after the query.

NB: I don't understand what you want to achieve with the strange "right( kna1~stceg,length( kna1~stceg ))"

Re: Function in function in SELECT of new SQL

ekekakos — Tue, 11 May 2021 12:51:23 GMT

Hello Sandra, what I want is to extract only the numbers from the VAT Reg. No eg from the EL12345678912 to take/extract the 12345678912. And I want this to be done in the SELECT query and not later in a LOOP.

Re: Function in function in SELECT of new SQL

Sandra_Rossi — Tue, 11 May 2021 13:13:42 GMT

"strange" is not what I mean, I mean "useless/non-sense" code 😉

right( kna1~stceg , length( kna1~stceg ) )

is exactly equivalent to

kna1~stceg

Re: Function in function in SELECT of new SQL

rahulpippalla — Tue, 11 May 2021 13:45:08 GMT

Hi elkekakos,

The answer is no. You cannot use dynamic length in right function.

You Will get an error stating the the second parameter should contain an ABAP variable which should be of numeric type or a number as said by sandra.rossi.

there is no way other than looping and achieving what you what.

But appreciate your thought to use right function there.

Regards,

Rahul.

Re: Function in function in SELECT of new SQL

ekekakos — Tue, 11 May 2021 14:36:06 GMT

Sandra, sorry, you are right, I mean the following

right( kna1~stceg , length( kna1~stceg ) - 2 )

I am so stretched that I forgot it.

Re: Function in function in SELECT of new SQL

rahulpippalla — Tue, 11 May 2021 15:05:04 GMT

HI elkekakos,

If you exactly know how much length needs to be removed then why dont you use substring

SUBSTRING( kna1~stceg, 3 )

Regards,

Pavana Rahul.

Re: Function in function in SELECT of new SQL

ekekakos — Tue, 11 May 2021 15:16:20 GMT

Hi, this is not working because it needs all parameters. I tried it and came up with the follwing error:

The function "SUBSTRING" expects 3 parameters, but 2 parameters were specified.

Regards

Elias

Re: Function in function in SELECT of new SQL

ekekakos — Tue, 11 May 2021 15:32:11 GMT

Well I solved my problem and I found the way to gather the n digits from the field stceg.

I use replace because I noticed that it uses 3 expr as parameters. So the code is:

CASE WHEN bseg~koart = 'D' THEN replace( kna1~stceg, substring( kna1~stceg, 1, 2 ), ' ' ) )
                WHEN bseg~koart = 'K' THEN replace( lfa1~stceg, substring( lfa1~stceg, 1, 2 ), ' ' ) )
                ELSE '000000000'
           END AS stceg

Thanks a lot for your help.

Regards

Elias

Re: Function in function in SELECT of new SQL

Sandra_Rossi — Tue, 11 May 2021 17:44:13 GMT

Don't you get 2 leading spaces? (I don't know the result, it's just a question)

Re: Function in function in SELECT of new SQL

ekekakos — Tue, 11 May 2021 18:54:31 GMT

I check it Sandra and no there is no blank space in front or at the end. As I use the itab for filling a structure which I converted in JSON, I check also json and this field is OK (it is string without space). So I believe that it is OK.

Furthermore, we can use the LTRIM function and put the replace as argument. I saw that there is no error.

Best regards,