AMDP in BW transformation: UP TO 1 ROWS

[yj]

Dear all,

in BW I have a transformation with an end routine in which I would like to select data from another table.

It is a table with material prices for each month/year, company code, plant and material.

How do I get only the latest price in my AMDP coding? E.g. I only need the price for September 2023 and not for all months.

This code does not work:

MAT_PRICES = SELECT

"CALMONTH",

"COMP_CODE",

"PLANT",

"PRICE_VAL"

UP TO 1 ROWS

FROM "/BIC/ACOPC_O2"

ORDER BY "CALMONTH" DESC

ENDSELECT;

I get the following error: SQLSCRIPT: sql syntax error: incorrect syntax near "TO"

Thanks & best regards,

Yvonne

[Torsten_ · Accepted solution]

Hi,

use TOP <n> in the SQL Select part.

See also: https://answers.sap.com/questions/10529983/selecting-top-n-rows-for-distinct-column-values-in.html

or

https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20fcf24075191014a89e9dc...

And remove the ENDSELECT from your statement!

Torsten