How to sort/re-order table values

Article:HOWTO82339  |  Created: 2012-11-20  |  Updated: 2012-11-20  |  Article URL http://www.symantec.com/docs/HOWTO82339
Article Type
How To



Warnings:

(1) There are no out of box abilities to resort tables or multi-rowed data classes. The following instructions are intended as a workaround for being able to sort/re-order table values, such as if records are desired to be alphabetized or otherwise appear differently than they do. This can be applied to tables for primary record values, or to multi-rowed data classes such as Cost Items, etc. While this process may work, it is not recommended to use this workaround, as issues may occur due to how the data was originally present in the table, which has now been changed. Because of this, Symantec Technical Support is unable to assist the user in implementing or troubleshooting this workaround.

(2) Before trying this workaround, it is recommended to test this thoroughly on a development server before trying it then on a production server. Also, the entire database should be backed up before trying it on a production server. If issues later occur, these can be restored.

(3) It is expected that the user is proficient with SQL and understands the following instructions. If not, it is recommended to discuss this with their database administrator (DBA) to help implement and troubleshoot it.

Question:

There are records that are desired to be sorted, such as alphabetically. There doesn't appear to be any way to do this in the user interface. Is there some way that this can be achieved nevertheless?

Answer:

Generally, yes. Tables can often be sorted in SQL to change their sort order. The following example demonstrates how to do this for a custom data class, which is a multi-rowed data class.
 

  1. Find the table to sort. If this is not known, the user must search through SQL to find the correct table. The search script found in the following KB can be used to help find the correct table:

    How to search a Microsoft SQL database for a data value
    http://www.symantec.com/business/support/index?page=content&id=HOWTO61242
     
  2. Once the table is found, a column in the table must be found to sort on. This may be a text (Name) or an ID (unique) value.
  3. Using a custom data class, for example, its table structure can appear like the following:

    USE Symantec_CMDB
    SELECT *
    FROM Inv_My_Custom_Dataclass

    _id      _ResourceGuid     Name
    1         <guid>                     Beta Test
    2         <guid>                     Alpha Test
     
  4. Once the table and sorting column are found, copy the table to a new table that will have the sorted data. The ORDER BY statement is used to sort the data as the user desires. In the following example, this sorts the data alphabetically based on the values from the Name column. Note: This will also copy all of the table's data to the new table.

    SELECT *
    INTO new_tablename -- For example, Inv_My_Custom_Dataclass2 (temporary).
    FROM old_tablename  -- For example, Inv_My_Custom_Dataclass (original).
    ORDER BY Name

    Warning: Any ID fields, in this case, "_id", retain their same values as the original table, even though they are now technically out of order. This may result in issues. If so, it is not recommended to use this workaround, as the above warnings state.

    The results are:

    SELECT *
    FROM Inv_My_Custom_Dataclass2 -- This is the name used for new_tablename in the above SQL query.

    _id      _ResourceGuid     Name
    2         <guid>                     Alpha Test
    1         <guid>                     Beta Test

    Alpha Test is now the first record, but its _id field is still numerically after Beta Test's.
     
  5. Delete the original table:

    DROP TABLE old_tablename
     
  6. Copy the new table's data to the old, thereby recreating it with the same schema and re-populating it with the sorted data:

    SELECT *
    INTO old_tablename
    FROM new_tablename
     
  7. Delete the new table that was used to sort to, as it is no longer needed:

    DROP TABLE new_tablename
     
  8. The original table is now restored, with the resorted data, and the temporary table ("new_tablename") has been removed. View/edit a resource that this affects. The data should now appear as resorted as the user defined by the ORDER statement.
     

Note: The ability to have out of box sorting abilities for multi-rowed data classes has been submitted as a feature enhancement request to the developers. This may or may not be added to a future version of CMDB Solution.



Article URL http://www.symantec.com/docs/HOWTO82339


Terms of use for this information are found in Legal Notices