AMDP in End Routine in BW4HANA Transformation

[ramsthota1986]

Hi Gurus,

I' new to AMDP still exploring the area. I have a simple requirement that i can achieve through Formula in BW4HANA Transformation. But to explore i chose to create a AMDP in End routine.

My requirement is to check the length of a field, and then assign first two letters to another field in ADSO, then check length assign first 4 letters to another and so on. I have tried to write the Code, But i'm getting an syntax error which i don't understand at all. Requesting you to please help me on this. Below is the code in AMDP Method.

IT_TAB = SELECT "/BIC/ZPRD_HIER",

case

when length ( inTab."/BIC/ZPRD_HIER" ) >= 8 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,8 ) as /BIC/ZSG_BCAT1,

when length ( inTab."/BIC/ZPRD_HIER" ) >= 10 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,10 ) as /BIC/ZSG_BCAT2,

when length ( inTab."/BIC/ZPRD_HIER" ) >= 2 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,2 ) as /BIC/ZSG_BRAND,

when length ( inTab."/BIC/ZPRD_HIER" ) >= 4 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,4 ) as /BIC/ZSG_LINE,

when length ( inTab."/BIC/ZPRD_HIER" ) >= 6 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,6 ) as /BIC/ZSG_SLINE,

RECORD,

SQL__PROCEDURE__SOURCE__RECORD FROM :inTab;

Attached is the screen shot with syntax Error.

[lenastodal]

Hi and welcome to the SAP Community!

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members. For example, you can outline what steps you took to find answers (and why they weren't helpful) and share screenshots of what you've seen/done. The more details you provide, the more likely it is that members will be able to assist you.
Should you wish, you can still edit your question.
Finally, if you're hoping to connect with readers, please consider adding a picture to your profile as it encourages readers to respond to you. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS.

Best,
Lena
SAP Community Moderator

[Torsten_ · Accepted solution]

Hi,

you can not use one CASE statement for several columns. You need one CASe per column.

And the CASE syntax starts with CASE and ends with END.

Torsten

[Torsten_]

Sorry I'm to fast.

I'd thought there are couple of rows but it's only one 😉

The Syntax for case is:

CASE <expression>  
WHEN <expression> THEN <expression>  
[{ WHEN <expression> THEN <expression>}…]  
[ ELSE <expression>]  
END

That means for your routine coding:

tempTAB = SELECT "/BIC/ZPRD_HIER",
case when length ( inTab."/BIC/ZPRD_HIER" ) >= 8 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,8 ) END as /BIC/ZSG_BCAT1,
CASE when length ( inTab."/BIC/ZPRD_HIER" ) >= 10 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,10 ) END as /BIC/ZSG_BCAT2,
CASE when length ( inTab."/BIC/ZPRD_HIER" ) >= 2 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,2 ) END as /BIC/ZSG_BRAND,
CASE when length ( inTab."/BIC/ZPRD_HIER" ) >= 4 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,4 ) END as /BIC/ZSG_LINE,
CASE when length ( inTab."/BIC/ZPRD_HIER" ) >= 6 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,6 ) END as /BIC/ZSG_SLINE,
RECORD,
SQL__PROCEDURE__SOURCE__RECORD FROM :inTab;

Torsten

[ramsthota1986]

Hi Torsten,

That worked like magic. Thank you so much. But i'm wondering why we need to include End? because my statement is not ending with one "when" i have many to go, As per the syntax of Case "end" should be placed at the actual end. Am i missing anything?Please enlighten me

Regards,

Ram

CASE<expression>

WHEN<expression>THEN<expression>

[{WHEN<expression>THEN<expression>}…]

[ELSE<expression>]

END

[Torsten_]

Hi,

1. you must surround the column name by double quotes like

when length ( inTab."/BIC/ZPRD_HIER" ) >= 8 THEN SUBSTRING( inTab."/BIC/ZPRD_HIER",1,8 ) as "/BIC/ZSG_BCAT1",

2. writing the result back into th inTab is not a good idea in case you need intermediate results use a new name like tempTab or similar

Torsten

[ramsthota1986]

Hi Torsten Kessler,

Thank you so much for your response, Appreciate it. I have followed your suggestion but unfortunately it didn't work. Please look at the attachment. Not sure what am i missing.

.