Question

Lobby | List View | Top 15 rows should be seen in list view

  • 12 January 2022
  • 9 replies
  • 231 views

Userlevel 3
Badge +6

In the list view of lobby we want to show only top 15 row of the table. Is this possible ?


9 replies

Userlevel 7
Badge +21

Hi @ATZSUMIT 

yes, it is possible. You have to a create a new data source and put there your SQL query.

Did you create the SQL query? You need the conditons for the top 15 rows.

 

Kind regards

Userlevel 3
Badge +6

I already created but do not know where to write top 15 in the data source designer.

in sql query it`s working fine but where to implement in data source design do not know.

Userlevel 7
Badge +21

Great.

Then you can create as following a new data cource:

 

Then you can create a new element and connect it with the created data source:

 

Kind reagrds

Userlevel 5
Badge +12

Hi @ATZSUMIT ,

I think you are trying to achieve the following SQL query. 

In SQL server - 
SELECT TOP 15 *
FROM REQUEST;

In Oracle Server 
SELECT *
FROM REQUEST
WHERE ROWNUM <= 15;

As per my understanding the data source cannot be customized to reflect the SELECT TOP 15 *  query. Since I have a SQL instance I could not try the Oracle statement. If you have an Oracle server try using ROWNUM <= 15  for the condition value. 

Hope this answer helps.

Best Regards,
Atheeq

Userlevel 3
Badge +6

you use Select top 15 * query so this statement top 15 where i have to use in data source designer ?

Userlevel 5
Badge +12

Hi @ATZSUMIT ,

In SQL server you CANNOT achieve the  TOP 15 requirement. Since the Data source designer can be used to customize the FROM (view), WHERE (condition), GROUP BY (group by) and ORDER BY (order by). The SELECT statement is customized using the columns and its not possible to add TOP 15  to the SELECT query. 

But in the Oracle server the top 15 can be achieved by customizing the WHERE (condition) statement. Since I don't have an Oracle instance I could not validate this behavior.  If you have Oracle server, for the condition include as  ROWNUM <15. This might work. 

Hope this answer helps.

Best Regards,
Atheeq

Userlevel 7
Badge +21

Hi all,

I have tested it as following:

...

ORDER BY column_name
FETCH FIRST 15ROWS ONLY

In data source it doesn’t work.

 

But this should work:

select * from i_xml_asap
where status = '07'
and process_start ...
and rownum <= 15
order by order_no

 

Kind regards

Userlevel 6
Badge +14

Hi @ATZSUMIT ,

Using rownum like that is not reliable.

You can create a IAL and use that as data source:

 

 

 

Userlevel 6
Badge +26

Hi @ATZSUMIT 

Here is a solution that works for me.

Create a view

create view TOP_15_TASK_VIEW_01 as (
select
top 15 *
from
task
order by
plan_start_dttm desc
)
  • Instead of select *, select only relevant fields for better performance.

 

Add the relevant fields to custom metadata

  • For this example I added only 2 fields - task_id & description. Only the added fields will be available in the designer screen.

Create a new data source

 

Create a new element

 

Add element to lobby screen

 

Cheers!

Reply