This discussion is archived
2 Replies Latest reply: Dec 10, 2012 10:29 AM by Stew Ashton RSS

Update statement, question

979030 Newbie
Currently Being Moderated
I’ve got a question which is probably very simple to you, but very difficult to me..

I’ve got the following (existing) update statement in an Oracle package:
-- update the aggregated batch fields (blocking flag is 1 if query fact exist with BF=1 or if no query facts have been generated and
-- for this group a query dimension record exist with blocking flag data or sql set to 1
update qf_f_batch bat
set (ts_ended_tz, ts_ended, nbr_error_msgs, blocking_flag) =
(select coalesce(max(sysdate), sysdate),
to_char(coalesce(max(sysdate), sysdate), 'DD/MM/YYYY HH24:MI:SS') ,
coalesce(sum(qryres.dummy_flag), 0),
case when
coalesce(count(qry.fk_query), 0)=0 and max(dqry.blocking_flag_data) > 0 or
coalesce(count(qry.fk_query), 0)=0 and max(dqry.blocking_flag_sql) > 0 or
coalesce(count(qry.fk_query), 0)>0 and sum (qry.blocking_flag) > 0
then 1 else 0 end as blocking_flag
from qf_d_query dqry
left outer join
( select fk_query, blocking_flag from qf_f_query
where fk_batch = varBatchKey) qry on (dqry.pm_primarykey = qry.fk_query)
left outer join
( select fk_query, dummy_flag from qf_f_query_result
where dummy_flag = 1
and fk_batch = varBatchKey) qryres on (qry.fk_query = qryres.fk_query)
where dqry.pm_current_flag = 1
and dqry.enabled = 1
and dqry.query_group = sGroup)
where bat.pm_primarykey = varBatchKey;


The update statement for nbr_error_msgs is incorrect (marked in bold, repeated here: coalesce(sum(qryres.dummy_flag), 0) )

The correct update statement for field NBR_ERROR_MSGS is :

UPDATE QF_F_BATCH b set B.NBR_ERROR_MSGS = (SELECT COUNT(F.ERROR_MESSAGE)
FROM QF_F_QUERY_RESULT FF
JOIN QF_F_QUERY F ON FF.FK_QUERY = F.FK_QUERY AND FF.FK_BATCH = F.FK_BATCH
WHERE F.FK_BATCH = B.PM_PRIMARYKEY
GROUP BY F.FK_BATCH
);

How can I integrate the second SQL statement into the first statement? Obviously, if I add the second SQL statement into the package, then the package will do an update of the complete QF_F_BATCH table, which contains thousands of records. Obviously I want to only update the current batch (using varBatchKey).
I am not an SQL expert (far from…) so for me this is extremely complicated. Can you help me?

Thanks in advance!
  • 1. Re: Update statement, question
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    welcome to the forum.

    Please read SQL and PL/SQL FAQ

    Additionally when you put some code or output please enclose it between two lines starting with {noformat}
    {noformat}
    
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    Your query is quite complex and without sample data (CREATE TABLE and INSERT statements) it is not easy to help you.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 2. Re: Update statement, question
    Stew Ashton Expert
    Currently Being Moderated
    I agree with Alberto: we need the information he requested in order to help you.

    May I point out a few things that seem strange to me already?

    1) "coalesce(max(sysdate), sysdate)" - that will always resolve to "sysdate", right?

    2) "coalesce(count(qry.fk_query), 0)" - that will always resolve to "count(qry.fk_query)", since count() never returns NULL, right?

    3) On the other hand, max() and sum() can return NULL so you should have COALESCE (or better NVL) around those - unless the original columns are defined as NOT NULL.

    4) You should really put a table alias in front of "varBatchKey", "fk_batch" and other columns so everyone knows where they comes from.

    5) If you run the update statement twice, it will update all the rows both times. If you used MERGE, you could restrict the update to just those rows where something needs to be changed.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points