CODEWITHSUNDEEP
sqloracle-databasenestedsql-update
Huskie69
Huskie69

Reputation: 865

Update Oracle table based on nested select incl. package function

I have a query which calls a function in a package to create a percentage value as a sum of about 30 columns.

What I'd like to do is update each row based on the "sum of counted columns" as a percentage.

The select query is:

SELECT
checklist_id,
row_status,
eba_cm_checklist_std.get_row_percent_complete(pc.id,pc.checklist_id,pc.max_col_num) AS percent_complete
FROM
(
    SELECT
        (
            SELECT
                COUNT(id)
            FROM
                eba_cm_checklist_columns
            WHERE
                checklist_id = r.checklist_id
        ) AS max_col_num,
        r.*
    FROM
        eba_cm_checklist_rows r
    ORDER BY
        r.row_order,
        r.name
) pc

and the package.function that creates the percentage is "eba_cm_checklist_std.get_row_percent_complete".

The query outputs the following:

 checklist_id                           row_status  percent_complete
 97176759931088640236098007249022291412 Red         0
 97176759931071715274623402440576404948 Red         0
 97176759931071715274623402440576404948 Red         0
 97176759931071715274623402440576404948 Red         0
 97176759931088640236098007249022291412 Red         0
 97176759931088640236098007249022291412 Red         0
 97176759931081386681180319473974054356 Grey        100
 97176759931051163535689953744606399956 Grey        100

The difficulty I'm having is that the expression is using a nested select statement based on the output of a function and I can't get my head around how to update the physical "eba_cm_checklist_rows" table based on the output of the query.

Basically, I want to do the following:

 update set row_status = 'Green' where percent_complete = 100

Upvotes: 1

Views: 214

Answers (3)

Rusty
Rusty

Reputation: 2138

Another option is to use bulk select/update:

declare
  type t_rid_arr is table of rowid index by pls_integer;
  type t_row_status_arr is table of eba_cm_checklist_rows.row_status%type index by pls_integer;
  type t_percent_complete_arr is table of number index by pls_integer;

  l_rid_arr t_rid_arr;
  l_row_status_arr t_row_status_arr;
  l_percent_complete_arr t_percent_complete_arr;
begin
  SELECT
    rid,
    row_status,
    eba_cm_checklist_std.get_row_percent_complete(pc.id,pc.checklist_id,pc.max_col_num) AS percent_complete
  bulk collect into 
    l_rid_arr,
    l_row_status_arr,
    l_percent_complete_arr
  FROM (
    SELECT r.rowid as rid, 
           r.*,
           count(*) over(partition by checklist_id) AS max_col_num
      FROM eba_cm_checklist_rows r
     ) pc;

  forall i in 1..l_rid_arr.count 
     update eba_cm_checklist_rows
        set row_status = case when l_percent_complete_arr(i) = 100 then 'Green' else row_status end
      where rowid = l_rid_arr(i);
end;
/

Upvotes: 1

Rusty
Rusty

Reputation: 2138

Try something like this:

merge into eba_cm_checklist_rows trg
using (
SELECT
rid,
checklist_id,
row_status,
eba_cm_checklist_std.get_row_percent_complete(pc.id,pc.checklist_id,pc.max_col_num) AS percent_complete
FROM
(
    SELECT r.rowid as rid, 
           r.*,
           count(*) over(partition by checklist_id) AS max_col_num
      FROM eba_cm_checklist_rows r
) pc
) src
on (trg.rowid = src.rid)
when matched then update set row_status = 'Green' where percent_complete = 100;

I don't like the idea to use function inside the SQL - it's probably could be replaced with normal SQL.

Upvotes: 1

Victor Guan
Victor Guan

Reputation: 126

you could try to use CTE and SUM() OVER() function, hope you find it useful.

Upvotes: 1

Related Questions