I have a gel script which we use to update projects' priority ranking attribute via XOG. The SQL query in the script first sorts the projects by a number attribute's value and then by Name in a subquery. I then use rownum to assign each project's priority rank. When I run the query in Oracle SQL Developer, the sort order matches that of an Excel file that is currently used to update the priority ranking. However, when I use the gel script and update the priority ranking attribute via XOG, the sort order is thrown off by the presence of numbers.
SELECT rownum priority_ranking, x.project_name, x.project_id
(SELECT i.name project_name, i.code project_id
FROM inv_investments i, odf_ca_project p
WHERE i.id = p.id
ORDER BY p.custom_number_attribute, REPLACE(REPLACE(LOWER(i.name),'-',''),'–','')) x
If three projects have the same value for custom_number_attribute, I expect them to be ordered as such
That is how they would be ordered in Excel and in SQL Developer using the query above. However, when I run the gel script, the resulting order is:
I noticed that if I order by Project Name on the Projects View, it also results sorts alphabetic values prior to numeric values. Does anyone have any recommendations for getting the sort order to place numerical values prior to alphabetic values in the gel script?