How to Change SQL in Webi Document Using Java RE SDK

This post briefly describes how to change SQL in Webi document. The following code will work for a Webi document with one simple query (say one object, without subqueries, combined queries, etc):

DocumentInstance widoc = wiRepEngine.openDocument(31223); // 31223 is ID of the webi document
SQLDataProvider sdp = (SQLDataProvider)(widoc.getDataProviders().getItem(0)); 
SQLSelectStatement sss = (SQLSelectStatement)(sdp.getSQLContainer().getChildAt(0));
sss.setSQL("SELECT * FROM (" + sss.getSQL() +") A");
sdp.validateSQL();
sdp.changeSQL();
widoc.save();
widoc.closeDocument();

If the document contained the only object "Resort" from universe "Island Resorts Marketing", the query was:

SELECT
  Resort.resort
FROM
  Resort

After applying the script the query will become:

Note that this is now a custom query.

Not clear?

Try to start from this: http://bukhantsov.org/2011/08/getting-started-with-businessobjects-java-sdk/ :)