Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: conditional sorting in a procedure

    I have a procedure running in our warehouse database that resorts the products within a task on alfabetical order. This is quit straightforward.

    The new requirement is to sort the picking lines in a more economical way, so depending on a condition part of the products in the task should be sorted based on another field and the remaining ones on alphabetical order.

    My gut-feeling is that this is not possible, cause reading the data into the cursor by using SQL will only allow a simple 'order by' sorting.

    The current procedure is as follows:


    PROCEDURE MODIFY_LINES_DIRECT(v_MOV_ID VARCHAR)AS
    CURSOR item_cursor IS SELECT PRODUCT FROM TABLE
    WHERE TABLE.MOV_ID = v_MOV_ID
    ORDER BY PRODUCT FOR UPDATE;
    I_item TABLE.PRODUCT%TYPE;
    I_new_row_number NUMBER;
    BEGIN

    OPEN item_cursor;
    LOOP
    FETCH item_cursor INTO I_item;
    EXIT WHEN item_cursor%NOTFOUND;
    I_new_row_number := item_cursor%ROWCOUNT;
    UPDATE TABLE
    SET MOV_LINE = I_new_row_number + 1000
    WHERE CURRENT OF item_cursor;
    END LOOP;
    CLOSE item_cursor;
    -- Update TABLE table; reset mov_line with - 1000
    UPDATE TABLE
    SET TABLE.MOV_LINE = TABLE.MOV_LINE - 1000 WHERE TABLE.MOV_ID = v_MOV_ID;
    COMMIT;
    END;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could use CASE or DECODE within the ORDER BY, e.g.
    Code:
    ORDER BY
       CASE WHEN x < 52 THEN y ELSE 999 END,
       product

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    We are still with version 7.3.4, so Case is not an option. The condition itself would also be more complicated, for instance involving a check whether a product would appear more than once in the task......
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    7.3.4? That is old!

    Well, that leaves DECODE then. Though whether 7.3.4 could handle the complexity you require within a DECODE I don't know. Something like:

    ORDER BY DECODE ( (SELECT COUNT(*) FROM xxx WHERE ...), 1, 'x', 'y')

    The SIGN function often comes in handy with DECODE. For example, to check whether sal > 1000:

    DECODE( SIGN(sal-1000), 1, 'yes', 'no')

Posting Permissions

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