How to write a SELECT statement based on the year and period of Parameters

[Former Member]

Nice to meet you, experts

What kind of logic should be used when the program uses the fiscal year and fiscal period of PARAMETERS and narrows down the data to be acquired by the SELECT statement?

(I want to obtain only the latest data for the year and period entered in PARAMETERS.)

For example, the following values exist in the table.

No. LFGJA LFMON DMBTR

1 2019 2 1000

2 2018 12 500

3 2018 10 300

At PARAMETERS

Year: 2019

Period: 1

If entered, No.2 (2018 12 500)

Only get and

Year: 2019

Period: 3

If entered, No.1 (2019 1 1000)

I want to write logic such as getting only.I tried to get all the data older than the year/period entered in PARAMETERS, SORT in descending order, and try to delete duplicates, but the number of records is too large, so I get a short dump in the SELECT statement.

Above, thank you.

Sato

[DanBB]

It's quite impossible that using the Open SQL in ABAP Application.
But it can be done using the native SQL.
Check if you're using the HANA DB or not.

1. If yes, you can use AMDP that using function Rank() over ().
Something like this
LT_BSEG_RANK = SELECT gjahr, poper, RANK( ) OVER ( ORDER BY ABS(days_between( :i_fdate , t3.BUDAT )) ) AS rank;
LT_BSEG = SELECT * From :LT_BSEG WHERE rank = 1;

2. IF not, try using native sql like this
TYPES:

BEGIN OF ty_acdoca,

gjahr TYPE acdoca-gjahr,

poper TYPE acdoca-poper,

rank TYPE i,

END OF ty_acdoca.

DATA: wa_acdoca TYPE ty_acdoca.

EXEC SQL.

SELECT gjahr, poper, rank( ) over( order by abs( DATS_DAYS_BETWEEN( BUDAT, '20190202' ) ) ) as rank into :wa_acdoca FROM acdoca limit 1

ENDEXEC.

[Former Member]

Nice to meet you Dan Bui Ba

Thank you for your reply.

You can do it with native SQL.

I didn't know because I had only used Open SQL.

However, since the use of native SQL is prohibited by the project development rules, it cannot be solved by the method proposed.

Is there no way to solve it using Open SQL?

Thank you for your cooperation.

[pfefferf]

It is not true, that this is impossible with ABAP SQL, it just depends on the version you are using. Window expressions are supportd with the ABAP Platform 1909 -> Window Expressions in ABAP SQL

[Former Member]

Florian Pfeffer
Thank you for your reply.

Since the current version is 1809, the method you taught me cannot be used.

Thank you very much.

Thank you for your cooperation.

[DanBB]

Hi Florian Pfeffer !I've checked the your suggestion. WOW didn't notice until you told me! However it is still limited some function. I would rather using AMDP and ABDC than using Open SQL like this. Anyway, thanks so much for your information! @Fumitoshi Sato, try to advance into the future men, don't hesitate to use something new. Indeed, it might be much faster than classic Open SQLDan

[Former Member]

Dan Bui Ba Thank you.
Native SQL is prohibited now, but I would like to suggest a method to use it as one of the countermeasures.

Thank you for your cooperation.

[Former Member]

Dan Bui Ba

I would like to realize it using AMDP, so could you elaborate on the above coding if you would like?

You need to define the class in if_amdp_marker_hdb as well as the part you wrote, right?

I've never used AMDP, so I'm not sure how to code it.

Thank you for your cooperation.

[Sandra_Rossi]

Former Member Native SQL is a generic term to say it's not Open SQL i.e. any SQL sent directly to the database. Consequently, AMDP is also native SQL.And since you use S/4HANA, SAP don't test it for other databases so I don't see any reason why you should keep the rule to write only Open SQL.

[MateuszAdamus]

Hello Former Member

Try this.

  SELECT lfgja, lfmon, dmbtr
    FROM table
    WHERE ( ( gjahr = @p_gjahr AND perio <= @p_perio ) OR gjahr < @p_gjahr )
    ORDER BY gjahr DESCENDING perio DESCENDING
    INTO TABLE @DATA(lt_table)
    UP TO 1 ROWS.

Kind regards,
Mateusz

[Former Member]

Nice to meet you Mateusz

Thank you for your reply.

I'm sorry.There is also an item (MATNR) in this table, and I would like to get one data item for each item.

I forgot to write in the first question.

If you use UP TO 1 LOWS, I think SQL will end with the first case.

If you have many items to acquire, how can you achieve this?

Please let me know if there is a good way.

Thank you for your cooperation.

[former_member1716]

Former Member,What table you are trying to get data from?What is the code you have written?What dump/issue you are receiving?Regards!

[Former Member]

Satish Kumar Balasubramanian

The short dump outputs the following message.

SQL error "SQL code: 2048" occurred while accessing table "MCHBH"

The data is acquired from the table: MCHBH.

Thank you for your cooperation.

[DanBB]

If I get you correctly, you want to fetch the best match with the given period not the latest, doesn't it?

if it is true, it is quite impossible using open SQL.
If you want to get the latest data, you might try this way:

1. Get the latest fiscal year and period using where conditions as mateuszadamus suggestion

2. Get all related data based on fiscal year and period

[MateuszAdamus]

Hello Former Member

I don't think it's possible with current Open SQL, at least not on the system I'm testing it. There are some things missing, which prohibit from doing that.

For example, it could be done with a CAST and MAX statements (I presume), but unfortunately the CAST does not convert character to numerical values. So below code does not work.

SELECT matnr, MAX( CAST( concat( lfgja, lfmon ) AS int4 ) ) AS max_year_period
  FROM mchbh
  WHERE ( ( lfgja = '2020' AND lfmon <= '01' ) OR lfgja < '2020' )
  GROUP BY matnr
  INTO TABLE @DATA(lt_table).<br>

The best I could come up with is as follows, but it can hardly be called a good solution.

SELECT matnr, MAX( lfgja ) AS lfgja
  FROM mchbh
  WHERE ( ( lfgja = '2020' AND lfmon <= '01' ) OR lfgja < '2020' )
  GROUP BY matnr
  INTO TABLE @DATA(lt_max_year).

LOOP AT lt_max_year REFERENCE INTO DATA(ld_max_year).
  SELECT matnr, lfgja, lfmon
    FROM mchbh
    WHERE matnr = @ld_max_year->matnr
      AND lfgja = @ld_max_year->lfgja
      AND ( ( lfgja = '2020' AND lfmon <= '01' ) OR lfgja < '2020' )
    ORDER BY lfmon DESCENDING
    APPENDING TABLE @DATA(lt_results)
    UP TO 1 ROWS.
ENDLOOP.<br>

I hard-coded the year and period parameters in my examples. You should put your program's parameters there.

Kind regards,

Mateusz

Edit: Oh, and there's Florian's comment.

[Former Member]

Mateusz Adamus

Thank you for teaching.
If it's the latest version, you can use the source code presented first.

Now it's 1809, so I'd like to try the method presented by the second.
By the way, because there is MAX in the SELECT statement, For All Entries cannot be used due to the condition, right?

Thank you for your cooperation.

[MateuszAdamus]

Hi Former Member

Yes, FOR ALL ENTRIES cannot be used with MAX function.

Kind regards,
Mateusz

[gasparerdelyi]

Actually what is possible -- since NW 7.40 SP05 if I remember well -- is to wrap SUM( ) and GROUP BY into a CDS view, and have a SELECT FOR ALL ENTRIES statement with the CDS view as data source.

Before ABAP CDS, you had to code a lot to achieve the same result...

[Former Member]

Mateusz Adamus

Thank you very much.

After all it is not usable.

Gaspar Zoltan Erdelyi

Can CDS View do the same thing as For All Entries?

I didn't know because I have never used CDS View.

Thank you for your cooperation.

[gasparerdelyi]

Fumitoshi Sato,

They definitely do not do the same thing. There is of course some very basic SQL in the intersection of features.
In certain situations -- like having a driver table for the keys for some aggregated values -- the features of the two nicely complement each other.

[former_member1716]

Former Member,

Give us the code what you have written and also detail us about the Dump you are getting.

Regards!

[Sandra_Rossi]

Please use the button CODE to align columns:

No.  LFGJA  LFMON  DMBTR
1    2019   2      1000
2    2018   12     500
3    2018   10     300

[venkateswaran_k]

Hi

Do you have the posting date in the table?

Based on the parameter you can evaluate the previous posting date and select data.