PL/SQL Update Complex Statement

Date-Completed-Script-for-item-n.txtI need to perform a complex update statement in Oracle where each record that needs to be updated can have a different date.   I tried the straight SQL route using a single update statement that is not loop based.  I think it could work but the statement alone took 1 hr to run.

I am looking for a better solution using PL/SQL Loop and may have it but not sure and hope someone can review my code for errors.
gilnariAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
>>I use distinct because I have tried it without and get duplication of values
distinct is useless if you are using group by
group by (by definition) produces unique rows, it has to do this so that the aggregate functions work. distinct should NEVER be used with group by.

There is a tendency for people to use the word distinct in a non-technical way, distinct does absolutely nothing that group by does not do. If you used a group by query that produced "unwanted repitition" then there are probably too many fields in the group by clause.

>>I do need to define the date as mm/dd/yyyy
why?
dates are NOT stored as strings, in Oracle dates are in fact stored as a set of 7 integers.
mm/dd/yyyy is a format mask for presentation of dates to humans

plus:
mm/dd/yyyy is an ambiguous format
yyyy-mm-dd at least is not ambiguous

>>there are issues with the date comparison if I don't use TO_CHAR
The only date comparison is below and that does not use to_char()
AND aa.TIMESTAMP <> dd.date_completed

If the need is to update dd.date_completed to a whole date, without time, then you should use TRUNC()
TRUNC(MAX(a.TIMESTAMP)) AS maxdate

>>the update need to update
>>1234AB to 03/04/2012
>>4455CC to 06/07/2012


then that's what should be in the query, just cc.item_number & max(aa.timestamp)

>> Don't you need a loop to check for the specific record
NOT IF THIS THE JOIN IS ACCURATE

WHERE cc.item_number = dd.item_id;

anyway, where's the loop? all you have between that begin/end is one update and one dbms_output, it's not looping. It relies on the join to match the records.

BEGIN
UPDATE user.sample dd
SET dd.date_completed = c_select_task_complete_date.maxdate
WHERE cc.item_number  = dd.item_id;

DBMS_OUTPUT.put_line (SQL%ROWCOUNT);

END;

that begin/end is much the same as this:
WITH SQ as (
            SELECT
                  cc.item_number
                , TRUNC(MAX(aa.TIMESTAMP)) AS maxdate
            FROM instance_history@location.com aa
                , user.result_mapping cc
                , user.sample dd
            WHERE aa.object_id = cc.old_itemnumber
                AND cc.item_number = dd.item_id
                AND aa.TIMESTAMP <> dd.date_completed
                AND aa.final_state = 'COMPLETE'
                AND dd.STATUS IN ('A','C')
            GROUP BY cc.item_number
            )
UPDATE user.sample dd
SET dd.date_completed = SQ.maxdate
WHERE SQ.item_number = dd.item_id;

Open in new window

You may choose to use a cursor, and that's fine, but using the cursor isn't going to improve performance. Tuning that query will have a bigger impact.
0
 
PortletPaulfreelancerCommented:
You do not need, and should not use, 'distinct' in a query that is using 'group by'
distinct is completely useless with a group by

Don't use to_char() on those date fields

You seem to have too many fields in the query and the group by for the needs of the update
e.g.
SELECT
      cc.item_number
    , MAX(aa.TIMESTAMP) AS maxdate
FROM instance_history@location.com aa
    , user.result_mapping cc
    , user.sample dd
WHERE aa.object_id = cc.old_itemnumber
    AND cc.item_number = dd.item_id
    AND aa.TIMESTAMP <> dd.date_completed
    AND aa.final_state = 'COMPLETE'
    AND dd.STATUS IN ('A','C')
GROUP BY cc.item_number;

UPDATE user.sample dd
SET dd.date_completed = c_select_task_complete_date.maxdate
WHERE cc.item_number = dd.item_id;

Open in new window

How long does it take to run just the query above? Don't see how using a cursor is going to enhance performance.
0
 
gilnariAuthor Commented:
there are issues with the date comparison if I don't use TO_CHAR.   I can try it without but I do need to define the date as mm/dd/yyyy.  As you can see I am calling from other database to get to get the old date and its not in the same format as the date in the new system.


I use distinct because I have tried it without and get duplication of values.  Long story on that one.  If I can leave it let's.

The date for each record where cc.item_number  = dd.item_id can be different and/or I have multiply dates and I need the max date for each record where cc.item_number  = dd.item_id

As example
1234AB can have a date of 03/04/2012
1234AB can have a date of 02/15/2012
4455CC can have a date of 06/07/2011
4455cc can have a date of 04/04/02012

the update need to update
1234AB to 03/04/2012
4455CC to 06/07/2012

The query does work as original built, my bigger concern is the update statement executing across all the records correctly  apply the date unique to that record.   The changes above I see took out the loop.  Don't you need a loop to check for the specific record date value and apply it and is the PL/SQL statement that original had will it work?
0
 
PortletPaulfreelancerCommented:
gilnari,
Hi. just wondering, have you been able to perform that update now?
is this question complete?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.