What about trying subselect?
should be something like:
SQL SUBSELECT PRCTR FROM GLPCA WHERE PRCTR IN ( SELECT PRCTR FROM <<tableName>> WHERE PRCTR BETWEEN '0000100000' AND '0000300000' );
Thanks, that wont work. What you call <<tablename>> is a hierarchy obtained from the extractor, we don't have it in a SAP table. Also, the PRTCR are not secuential. But if we would have that hierarchy in a table we could do
SQL SUBSELECT PRCTR FROM GLPCA WHERE PRCTR IN ( SELECT PRCTR FROM <<tableName>> WHERE nodename3 = 'value1' or nodename3='value2';
That second table is the Organiational Structure, obtained with the SAP extractor. We have it on a QV table. But I don't think we can do a "where in" using a QV table when the values are over 5000.
Last sentence: indeed this may seem like a conflict of "geographically separated tables", but I think it can be done using $-sign substitution.
You should check it out, albeit with a limited set of values, say the first 1000 profit center codes. You can do that with a FOR loop that puts the first 1000 values in a comma-separated list and stores the list in a variable. Let's call the variable vProfitCenters.
Then simply use an OpenSQL statement like
SELECT GL_SIRID RCLNT FROM GLPCA WHERE PRCTR IN ($(vProfitCenters));
and give it a go. Problems you may face:
- Length limit of a SQL statement (8k for Oracle?)
- Awful performance when using long lists in WHERE clauses due to individual comparisons
Another trick to attempt may be the FOR ALL ENTRIES IN clause, but I'm not sure whether the Connector will accept that variant.