SAP Business Intelligence Query Builder

[Former Member]

Hi, I am running the below query to get the objects from Favorites Folder. I have almost 20000 reports in Favorites.

SELECT TOP static, relationships, SI_PARENT_FOLDER_CUID, SI_OWNER, SI_PATH FROM CI_INFOOBJECTS,CI_APPOBJECTS,CI_SYSTEMOBJECTS WHERE DESCENDENTS("SI_NAME='Folder Hierarchy'", "(SI_KIND='FavoritesFolder')")

So I want to split up the query in 2 parts. First part will extract first 10000 results and the other part will extract another 10000 results.I have to divide the query otherewise query is getting hanged.

First set of results I can get by adding Top 10000 after Select clause.

But how I will get the next 10000 results ? I tried with Bottom 10000 but no Luck.

[Former Member]

Hi Sameer,

For the first set, you can use the first query and capture the last SI_ID value

SELECT TOP 10 SI_ID, relationships, SI_PARENT_FOLDER_CUID, SI_OWNER, SI_PATH FROM CI_INFOOBJECTS,CI_APPOBJECTS,CI_SYSTEMOBJECTS WHERE DESCENDENTS("SI_NAME='Folder Hierarchy'", "(SI_KIND='FavoritesFolder')")

Then use the below query. assuming the top10th SI_ID value is 29975.

SELECT top 10 SI_ID, relationships, SI_PARENT_FOLDER_CUID, SI_OWNER, SI_PATH FROM CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS WHERE DESCENDENTS("SI_NAME='Folder Hierarchy'", "(SI_KIND='FavoritesFolder')") and SI_ID>'29975'

you can pick top 10, 100 or 1000 of your wish, hope this helps.

[Former Member]

Hi Santosh,

Thanks for your suggestion.

Yes, now I am able to break the results in to 2 parts based on the SI_ID number.

Appreciating your help.:)

[Former Member]

Thanks for the replies guys.

No idea about the SDK. How to use SDK in SAP BI.

@ Prithviraj:

Order by clause can't work in my scenario. Still the query will run for 20000 rows, and it will make the system hang.

Actually, I am creating BIAR files through LCM CLI(Command Line Interface). So I am using Queries in the Properties file. If i will use Orderby clause, still the query will run for 20000 records and the system will be hanged. So I need to split the queries by 10000 records.

[Former Member]

If you want to split the query in to two parts only, you can use the order by clause and change the sort order to desc to get the bottom 10000 results.

(For example, I have a query returning 100 rows, So the query for dividing it in 2 halves of 50 would be like

select top 50 column1,column2 from table order by SI_ID, would give top 50 rows.

select top 50 column1,column2 from table order by SI_ID desc, would give bottom 50 rows.)

Message was edited by: Prithviraj Shekhawat

[former_member187093]

not sure on the AdminTools... we get these kind of details via SDK.. 

Query : we use 'SI_ID' parameter in where clause to limit (<10000 , > and <20000), we will execute the queries till max(SI_ID)