Solved

Using aggregate functions in data source designer

  • 6 March 2022
  • 4 replies
  • 475 views
J
Rank
Badge +2

Trying to develop data sources for Lobby elements to use for day to day business management. 

I am trying to ascertain the next awaiting step in the approval process for a purchase requisition line so my data source so need the minimum step ID where the Signature field is null. See snapshot below;

 

essentially my SQL will look like;

SELECT COLUMNS, MIN(ROUTE)

FROM &AO.PURCH_REQ_LINE_APPROVAL

WHERE SIGNATURE_ID IS NULL

GROUP BY COLUMNS:

as per the image above the aggregate function appears to not be working as there are multiple steps (Route column) still displayed, IE 10, 20 and 30. whereas there should only be the minimum route number displayed.

what am i missing…...

 

icon

Best answer by Dilani 8 March 2022, 06:11

View original
This topic has been closed for comments

4 replies

Mathias Dahl
Rank
Userlevel 7
Badge +30

Moved to Framework section.

/Mathias
Dilani
Rank
Userlevel 3
Badge +4

Hi,

In the screenshot shared, that group by statement is based on so many columns. Therefore, it could be that for 3 different route IDs one of other columns also has different values (this will create multiple groupings and the min will be selected from those).

Base on that details in screenshot is not enough to conclude aggregate is not working.

Lobby data source is creating a select statement base on the information we provided. Therefore, per my understanding this aggregation is from sql itself and it should work. 

Hence, if you want to take minimum route ID you have to make sure all other columns in the group by statement has same values so it will be considered into a one group with only a varying route ID. 

Thanks & Best Regards,
Dilani

J
Rank
Badge +2

Hey Dilani,

Thanks for the reply, you were correct in your response. However now I am trying to achieve an equivalent query to below however I don’t understand the IFS SQL Data Source Design Interface. how do i transfer a straight SQL statement which i do know into an IFS equivalent in the SQL Data source design tool? IE recreate the below into the SQL data source tool.

 

Dilani
Rank
Userlevel 3
Badge +4

Hi James,

Per my understanding in Lobby Data Source Designer, you cannot specify this kind of join conditions directly (in segment for view, only one view can be defined).

For this kind of scenario, I have used one of below two options.

Option 01:

Create own view with this join condition in the database with a different name. Then refer to that as view in data source designer.

Option 02:

Change the SQL query somehow to achieve same without the join, using different where conditions and group by.

Not sure how feasible this for your situation. But this is the only ways I know. Hope this helps.

 

PS:

Also, SQL query construction from above Data Source Designer will looks like below

SELECT

    {Everything in columns section with each column name coma separated}

FROM  {the view defined in view section}

WHERE  {the conditions defined in the condition section}

GROUP BY  {all the columns in group by section}

ORDER BY  {all the columns in the order by section}

May be you already knew this. Just added for the clarity :-)

 

Cheers,

Dilani

Terms & ConditionsCookie settings