Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Nesting select queries in BPC 10 NW logic scripts

Hi,

I am working on a BPC 10.0 NW trying to dynamically updated a piece of script logic to seed data for forecasting, hardcoding the values in the logic script works, but I have got two issues, firstly I am writing a select query that I want to select records based on not having a null/blank property but I can not find the comand for NOT Null, I tried as follows,

*SELECT(%PACCT%,"[PLAN_PARENT]",ACCOUNT,"[PLAN_PARENT] NOT NULL")

But it does not like the syntax NOT NUll

This select statement drives a FOR statement that I want to use a varible in another select statement that drives a REC

The following is the syntax I curently have  that works

*FOR %FC% = %FCVERSION%

*FOR %ACCT% = %PACCT%

  *XDIM_MEMBERSET ACCOUNT = BAS(%ACCT%)

  *SELECT(%FCACCT%,"[ID]",ACCOUNT,"[PLAN_PARENT] = LI_FYPDA")

  *FOR %INPUTACCT% = %FCACCT%

       *REC(FACTOR = 1, ENTRYTYPE = SYS_CALC, VERSION = %FC%, ACCOUNT = %INPUTACCT%)

   *COMMIT

  *NEXT

*NEXT

Below is what I have tried that does not work

*FOR %FC% = %FCVERSION%

*FOR %ACCT% = %PACCT%

  *XDIM_MEMBERSET ACCOUNT = BAS(%ACCT%)

  *SELECT(%FCACCT%,"[ID]",ACCOUNT,"[PLAN_PARENT] = " %ACCT%)

  *FOR %INPUTACCT% = %FCACCT%

       *REC(FACTOR = 1, ENTRYTYPE = SYS_CALC, VERSION = %FC%, ACCOUNT = %INPUTACCT%)

   *COMMIT

  *NEXT

*NEXT

I would like the query restriction to be the variable from the for statement not a hardcoded value

I would appreciate and help in using a variable in an select statement

Thanks

Tags:
Former Member
replied

In general if you want to copy data from number of base accounts to some target base accounts you have 2 options:

1. Universal - for each source base account fill some property (TARGETACCT) with the required target base account. Then use simple *REC(...,ACCOUNT=ACCOUNT.TARGETACCT)

2. For the simple 2 level account hierarchy:

PARENT1

  BASE1

  BASE2

...

PARENT2

  BASE3

  BASE4

...

BASE_PARENT1_INPUT

BASE_PARENT2_INPUT

You can fill the property for target account only for parents.

Then:

*SELECT(%AC%,"[ID]",ACCOUNT,"[TARGETACCT]<>''") // parents will be selected

*SELECT(%TAC%,"[TARGETACCT]",ACCOUNT,"[TARGETACCT]<>''") // targets will be selected

*FOR %A%=%AC% AND %TA%=%TAC%

*XDIM_MEMBERSET ACCOUNT=BAS(%A%)

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%,ACCOUNT=%TA%

*ENDWHEN

*NEXT

The second approach is easier to maintain but slower (FOR/NEXT is slow!) and applicable to specific hierarchy.

Vadim

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question