Question

Sort order in API output for table functions

  • 19 November 2021
  • 8 replies
  • 64 views

Userlevel 3
Badge +14

How can the sort order of of items for a table function be set?

I am using Indicium 2021.2 (Universal)


8 replies

Userlevel 2
Badge +2

Inside the function template code?

Userlevel 3
Badge +14

I have done this, but the API engine seems to order on the values ofthe first column of the output table

Userlevel 5
Badge +9

I was able to sort the data by sorting it in the table function. How do you sort the data in the function?

Userlevel 3
Badge +14
;WITH a AS (
SELECT wrl_guid, ass_guid
FROM work_location
LEFT JOIN asset ON ass_adr_guid = wrl_adr_guid
WHERE wrl_guid = @wrl_guid
)
INSERT INTO @work_location_entity (ent_guid, ett_id, ent_description, sort_order)
SELECT x.ent_guid, ett_id, ent_description = e.ent_dx_default, sort_order = ROW_NUMBER() OVER (ORDER BY sort_order, ent_description)
FROM (
SELECT ent_guid = w.wrl_guid, ett_id = 'wrl', ent_description = wrl_name, sort_order = 0, update_date_time = ISNULL(wrl_update_date_time, wrl_insert_date_time) FROM work_location w JOIN a ON a.wrl_guid = w.wrl_guid
UNION
SELECT ent_guid = act_guid, ett_id = 'act', ent_description = act_custom_id, sort_order = 1, update_date_time = ISNULL(act_update_date_time, act_insert_date_time) FROM activity JOIN a ON a.ass_guid = act_belongs_to_ass_guid
UNION
SELECT ent_guid = acx_guid, ett_id = 'acx', ent_description = acx_custom_id, sort_order = 1, update_date_time = ISNULL(acx_update_date_time, acx_insert_date_time) FROM action JOIN a ON a.ass_guid = acx_belongs_to_ass_guid
UNION
SELECT ent_guid = isu_guid, ett_id = 'isu', ent_description = isu_custom_id, sort_order = 1, update_date_time = ISNULL(isu_update_date_time, isu_insert_date_time) FROM issue JOIN a ON a.ass_guid = isu_belongs_to_ass_guid
UNION
SELECT ent_guid = obs_guid, ett_id = 'obs', ent_description = obs_custom_id, sort_order = 1, update_date_time = ISNULL(obs_update_local_date_time, obs_insert_local_date_time) FROM observation JOIN a ON a.ass_guid = obs_belongs_to_ass_guid
) x
JOIN entity e ON e.ent_guid = x.ent_guid
ORDER BY sort_order

RETURN

This is the template of the function, copy + paste into SSMS for a clear picture

Userlevel 5
Badge +9

In order to sort the data inside the table valued function. Set a primary key on the sort column, you might also want to make this the first column in the table.. 

 

 

Userlevel 3
Badge +14

Ok, clear for this scenario it will work, but it will not always be like this. 

Would it not be possible to change the API engine so that it uses the same order as the output of the SP?

 

Userlevel 5
Badge +9

The API is giving the same output as the function. So calling the function from the database without a sort order would give the same result. For a future release the development team will look into a possibility to sort on an API call. For now the best option is to add the PK to force the sorting. 

Userlevel 3
Badge +14

I have changed the primay key, but it worked only after making the column also the first column

Reply