Derive value of a characteristic at query runtime

[dsakq]

Hi experts,

in a aDSO I have to derive a characteristic A according to a complex logic within one row. Usually I'd do that within an endroutine or expert routine. But in my case, A will change in further loads onto this aDSO.

A is a component of another InfoObject B. This is because of a hierarchy within B.

So my problem is: Everytime new data is loaded, I'd have to loop through the entire active data and determine A's new value according to the new and old data. I wondered if there was a more simple way to do that by using something like a "virtual characteristic" whose value is determined at query runtime. Is there something like this or do I really have to loop?

Thanks

[Abhishek_Hazra]

Hi,

What is the base for your query? Do you have a composite provider or calculation view based on your aDSO?

Best Regards,
Abhi

[dsakq]

Hi Abhishek,

so far it's only an aDSO. Adding a Composite Provider is possible in my scenario.

[Jörg_Brandeis · Accepted solution]

Hi David,

you can calculate this with a SQL-Expression a composite provider, as long as this field could be calculated within the row and without accessing other tables. I wrote a blog post about SQL-Expressions in BW/4HANA Composite Providers, that could show you the possibilities at this place.

Can you tell us, how A is calculated?

Regards,
Jörg

[dsakq · Accepted solution]

Hi Jörg,

thanks for the blog post! This seems very helpful. A will be calculated in the following way:

if "MY_CHARACT1" = 'X' then

if "0AMOUNT" < 0 then

"A" = '1'

else

"A" = '2'

end if

else

"A" = 'X'

end if

I'm completely new to SQL Script so I'm not sure if if statements are possible in this way at all or if only case statements are allowed that you mentioned in your video. But "MY_CHARACT1" and "0AMOUNT" are available at the output part of the composite provider.

[Jörg_Brandeis · Accepted solution]

Hi dsakq ,

you can do this with the CASE Expression. It should look like like this:

CASE WHEN "MY_CHARACT1" = 'X' AND "0AMOUNT" < 0 THEN '1'
     WHEN "MY_CHARACT1" = 'X' AND "0AMOUNT" >0 = THEN '2'
     ELSE 'X' END

Regards,
Jörg

[dsakq · Accepted solution]

Thank you Jörg, this has solved my problem!

[Abhishek_Hazra]

Hi David,

Once you have your composite provider in place you can create a calculated column in the target structure as shown below :

And you can define an SQL expression to derive your logic for A there as mentioned by Jörg in earlier comment.

Have a good day! 🙂

Regards,
Abhi