topic Re: How to write SQL based on these conditions ? in Application Development and Automation Discussions

How to write SQL based on these conditions ?

Former Member — Wed, 06 Jan 2021 08:40:40 GMT

The conditions for selecting stock valuation data are as blew:

<Table>

MBEWH

MBEW

MBEWH-WEKEY = [Valuation Area] selected from 1.2 (T001W-BEKEY)

MBEWH-MATNR = [Material Number] selected from 3.2 (MARC-MATNR and MARC-MATNR)

MBEWH-LFGJA + MBEWH-LFMON >= [PERIOD](LDF_PERIOD gotten from function 'G_PERIOD_GET') + [YEAR](LDF_YEAR) -1

MBEW-WEKEY = [Valuation Area] selected from 1. 2

MBEW-MATNR = [Material Number] selected from 3. 2

<Field>

MBEW-LBKUM

MBEWH-LBKUM

MBEW-SALK3

MBEWH-SALK3

The details about <MBEWH-LFGJA + MBEWH-LFMON >= [PERIOD](LDF_PERIOD gotten from function 'G_PERIOD_GET') + [YEAR](LDF_YEAR) -1> would be like:

MBEWH~LFGJA + MBEWH~LFMON = Low~(Year+Period)-1

If there is no entry in MBEWH, copy the existed value from MBEWH where MBEWH~LFGJA + MBEWH~LFMON = Low~(Year+Period) + n(n>=0)

If there are no entries in MBEWH for all months, copy the existed value(LBKUM) from MBEW.

omg What should I do?

anyone here, expecting for your kind help.

Thank you all😭😭

Re: How to write SQL based on these conditions ?

FredericGirod — Wed, 06 Jan 2021 08:46:14 GMT

Your functional colleague should stop trying to write pseudo code and give you the expected result.

MBEWH is the history of the MBEW table. It contains the previous value by period of the MBEW. But, not all the period are recorded, just when there is a change.

I think your colleague tries to ask you to get the previous value of the Price valuation of the materials

Re: How to write SQL based on these conditions ?

Former Member — Wed, 06 Jan 2021 08:55:14 GMT

How to write the code of this:

MBEWH~LFGJA + MBEWH~LFMON = Low~(Year+Period)-1

If there is no entry in MBEWH, copy the existed value from MBEWH where MBEWH~LFGJA + MBEWH~LFMON = Low~(Year+Period) + n(n>=0)

If there are no entries in MBEWH for all months, copy the existed value(LBKUM) from MBEW.

Really confused...

Re: How to write SQL based on these conditions ?

Dominik_Tylczynski — Wed, 06 Jan 2021 09:03:55 GMT

Hello miyuriowo

Consider the note 193554 - Stock/valuation data of previous periods for detailed explanation of stock history tables logic.

If you are already on S/4HANA see the note 2600024 - Create a Material, updates History Tables, MCHBH, MARCH and MARDH, with past Years (LFGJA) and Periods (LFMON) entries that explains:

In ERP the history tables contain only an entry if there was a change within the related period. In S/4HANA the history tables contain an entry for each period (independent on whether there was a change or not).

Best regards

Dominik Tylczynski

Re: How to write SQL based on these conditions ?

FredericGirod — Wed, 06 Jan 2021 09:06:13 GMT

Forget this idea. Just make a selection with the Year & Month GE to the select-option. Make a Sort, and take the first entry.

Re: How to write SQL based on these conditions ?

Former Member — Wed, 06 Jan 2021 09:19:36 GMT

ok. try as you suggested.