2 Replies Latest reply: Dec 10, 2012 12:29 PM by Stew Ashton RSS

    Update statement, question

    979030
      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
          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
            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.