Improved Coded Value Domain Sorting Using SQL Statements

Idea created by ord5206 on Aug 17, 2017
    New
    Score10
    • ord5206

    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)