Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    12

    Unanswered: How to sort on different columns

    Hi,

    I have written a stored procedure in which I have to get the output sorted on different columns. I have written the following cursor

    DECLARE cursor_name CURSOR WITH RETURN FOR
    SELECT
    *
    FROM
    <table_name<
    ORDER BY IN_COLUMN_NM ASC ;

    I am providing column name as an input parameter on which I want to sort my result set.

    I am not getting any error. I am getting the same output for any value of IN_COLUMN_NM which is not sorted on any column.

    Thanks in advance

    Regards
    Arun

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You need dynamic SQL for that. I.e. build the statement as a string, then declare a cursor for it and open that cursor.

    The reason for dynamic is simply that DB2 (or any other serious DBMS) cannot generate a single access plan with different sort requirements in the picture. For example, if you have a simple query that selects from a table, and you order by a column on which an index exists, then DB2 can fetch based on the index and doesn't have to apply a SORT operator itself. If no such index is available, you have a high chance that the SORT operator is necessary. Depending on the size of the table, the SORT operator may be expensive if, for example, it has to spill to disk. So different plans for different sort criteria are usually a good idea.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    What about using a case statement in the order by? Something along the lines of:

    ORDER BY (CASE WHEN :IN_COLUMN_NM = ? THEN COL1
    WHEN :IN_COLUMN_NM = ? THEN COL2
    ELSE COL3
    END)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •