AnsweredAssumed Answered

SQL Order By in Gel Script

Question asked by Tim1302647 on Nov 11, 2015
Latest reply on Feb 9, 2016 by Tim1302647

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

FROM

     (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

 

123 Project

ABC Project

DEF Project

 

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:

 

ABC Project

DEF Project

123 Project

 

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?

Outcomes