Rebecca Wells wrote:
> The purpose of the procedure is to reorder time shet lines for proper display.
You really shouldn't be doing that by updating the database, but just by
applying the sort in the select that generates the display.
> *------
> CREATE OR REPLACE PROCEDURE
> TC_RESEQ_LINES(employee IN VARCHAR2, per_start IN VARCHAR2)
> AS
> CURSOR exting_lines IS
> SELECT LINE_ID, EMP_NO, PERIOD_DATE, LINE_SEQ, DESCRIPTION, WORK_ORDER,
> TASK_CODE, KOT
> FROM FN.TC_LINES
> WHERE EMP_NO = employee
> AND PERIOD_DATE = TO_DATE(per_start, 'MM/DD/YYYY')
> FOR UPDATE OF LINE_SEQ;
>
> seq_no NUMBER(3,0);
> exting_lines_rec exting_lines%ROWTYPE;
>
> BEGIN
> seq_no :=1;
> OPEN exting_lines;
> FETCH exting_lines INTO exting_lines_rec;
> WHILE exting_lines%FOUND LOOP
> UPDATE FN.TC_LINES
> SET LINE_SEQ = seq_no
> WHERE CURRENT OF EXTING_LINES;
> seq_no := seq_no + 1;
> FETCH exting_lines INTO exting_lines_rec;
> END LOOP;
> COMMIT;
> END;
> /
Convert the date calculation, fill out #sort# with the desired sort
order and #PK# with the primary key. And try this in a safe place first :)
UPDATE FN.TC_LINES
SET LINE_SEQ = self.seq_no
FROM
FN.TC_LINES INNER JOIN (
SELECT
#PK#,
ROW_NUMBER() OVER(ORDER BY #SORT# DESC) seq_no
WHERE
EMP_NO = employee
AND
PERIOD_DATE = TO_DATE(per_start, 'MM/DD/YYYY')
) self ON FN.TC_LINES.#PK# = self.#PK#
WHERE
EMP_NO = employee
AND
PERIOD_DATE = TO_DATE(per_start, 'MM/DD/YYYY')
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Macromedia ColdFusion MX7
Upgrade to MX7 & experience time-saving features, more productivity.
http://www.adobe.com/products/coldfusion?sdid=RVJW
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273886
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
