I have a coded value domain that is called fcDiameter, and it looks like this:
1 - 1
2 - 2
102375 - 2.375
48 - 48
42 - 42
0 - Unknown
100500 - 1.500
-1 - Unknown (Verified)
I would like to sort by description with number-like descriptions sorted numerically followed by text descriptions. You should add functionality to coded value domain management allowing for SQL statements to be used for changing the order where sorting by code or by description ASC or DSC alone is not providing the desired results.
For example, if I were to query the domain table in Oracle, a select query that would return the desired order would look like this:
SELECT * FROM CVD_VW_FCDIAMETER ORDER BY REGEXP_SUBSTR(DESCRIPTION, '^\D*') NULLS FIRST, TO_NUMBER(REGEXP_SUBSTR(DESCRIPTION, '\d+'))
And the results of the query would look like this:
1 - 1
100500 - 1.500
2 - 2
1002375 - 2.375
42 - 42
48 - 48
0 - Unknown
-1 - Unknown (Verified)
