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),
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!
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.