How to compute/calculate based on String?

[former_member187400]

Hi All -

I am working for BPC Embedded Planning BW 7.5.

We have z-table having formula maintained (table name: Formula Builder)

Account (Output)	Sequence	Operator	Account Ref.
100101	1	START	30101
100101	2	PLUS	30102
100101	3	MINUS	30103
100101	4	PLUS	30104
100101	5	END

and we have transaction data (from other table - table name: Transaction Data table):

Account	Year	Amount
30101	2016	10
30102	2016	5
30103	2016	20
30104	2016	30

The requirement is to have AMDP with SQL Script to compute Account: 100101 transaction data based on Formula Builder table.

And, I have a plan : to create 1 string, and put all the amount including with the arithmetic (plus, minus, multiply, bracket, etc) into the string, and let standard SQL Function to compute, or handle it.

e.g. the Account 100101 will be computed: 

100101 = 10 + 5 - 20  + 30

100101 = 25

The question is what the standard Function Module, or SQL Command/Syntax to do the computation based on string that I can use in AMDP?

Kindly please ask your thought, and advice,

Many thanks,
Niel

[former_member187400]

Hi All -

I am working for BPC Embedded Planning BW 7.5.

We have z-table having formula maintained (table name: Formula Builder)

Account (Output)	Sequence	Operator	Account Ref.
100101	1	START	30101
100101	2	PLUS	30102
100101	3	MINUS	30103
100101	4	PLUS	30104
100101	5	END

and we have transaction data (from other table - table name: Transaction Data table):

Account	Year	Amount
30101	2016	10
30102	2016	5
30103	2016	20
30104	2016	30

The requirement is to have AMDP with SQL Script to compute Account: 100101 transaction data based on Formula Builder table.

And, I have a plan : to create 1 string, and put all the amount including with the arithmetic (plus, minus, multiply, bracket, etc) into the string, and let standard SQL Function to compute, or handle it.

e.g. the Account 100101 will be computed: 

100101 = 10 + 5 - 20  + 30

100101 = 25

The question is what the standard Function Module, or SQL Command/Syntax to do the computation based on string that I can use in AMDP?

Kindly please ask your thought, and advice,

Many thanks,
Niel

[Former Member]

Hi

try this sql as in the image.you can add the case for div and multiplication also.

[former_member187400]

Arun Kumaar Menon wrote:

Hi

try this sql as in the image.you can add the case for div and multiplication also.

Hi Arun -

Thanks for the quick response. However, I found this way is difficult due I will have bracket (  ) , and If statement, and other things. I might go to this way for the last resort.

Hence, I am thinking to put all those amount into 1 string or nvarchar, and need 1 SQL Command to handle the computation.

If you have any thought on this - what is the SQL Command/Syntax, and the sample code, kindly please let me know.

Many thanks,
Daniel.