Using an array inside of a select statement

Question

I'm currently trying to create a dynamic action inside of Oracle APEX using a PL/SQL script that counts the amount of Yes and N/A responses in a table and return a percentage of the progress towards every cell being a Yes or N/A. Since the table is pretty large I was thinking that putting all of the column headers into an array and running the select statement in a for loop would be the most efficient way to do this. Here is what I have so far:

DECLARE
  type array IS VARRAY(20) OF VARCHAR2(1000);
  columns_array array := array('...','...',...);
  arr_Size number(4, 2) := 20;
  Counter number(5, 2);
  Progress number(5, 2);

BEGIN

FOR i in 1..arr_Size
 LOOP
    select COUNT(columns_array(i))
    into Counter
    from Table
    where array(i) = 'Yes' OR array(i) = 'N/A';
    Progress := Progress + Counter;
END LOOP;

END;

The program compiles without any errors but the code outputs zero for progress after running when it shouldn't. I think the problem is that I used an array inside of the select statement, but I don't know any other way to accomplish what I'm trying to do.

What are the ... in the array elements - column names? Your code is comparing the element values with Yes or N/A - it is interpreting the array values as literals strings, not treating them as columns, if that is what you are trying to do. You would need to use dynamic SQL if the column you're referring to isn't a fixed value. With a fixed list of column names it would probably be easier to just do repeated conditional aggregation. — Alex Poole
– Alex Poole, Commented Sep 9, 2022 at 18:42
The ... array elements are column names however they are fixed values so I don't feel like that should be a problem. — Jason Bourne
– Jason Bourne, Commented Sep 9, 2022 at 18:47
It's a problem to treat them as column names in your static SQL. They are variable values so you could happily compare a column value to a value form your array, just like any other variable, but you can't use them as the column name. — Alex Poole
– Alex Poole, Commented Sep 9, 2022 at 18:52
Could you give me an example of how to use dynamic SQL in this instance? I'm pretty new to this programing language. — Jason Bourne
– Jason Bourne, Commented Sep 9, 2022 at 19:01
Just to comment, I'm a bit puzzled by arr_Size number(4,2) := 20; Why the two decimal places? 20 seems like an integer. And why not declare arr_Size first as a constant and use it as the array size, instead of repeating '20' in two places? — William Robertson
– William Robertson, Commented Sep 9, 2022 at 19:39

Alex Poole · Accepted Answer · 2022-09-09 19:40:16Z

You can't use a variable as an identifier - table name, column name, alias etc. - in a static SQL statement. At the moment you're comparing the value of the array element - which happens to be a column name but could be anything - with the fixed values 'Yes' and 'N/A', so unless you have column with those names (which is unlikely) it will not match.

You can compare a variable with another values (as you are doing here), but you can't use a variable as a column name (as you intended).

You could use dynamic SQL, constructing a statement by concatenating in the column names from the array; a basic version could be:

DECLARE
  type array IS VARRAY(20) OF VARCHAR2(1000);
  columns_array array := array('COL1','COL2','COL3');
  counter pls_integer;
  progress pls_integer;
  statement varchar2(4000);

BEGIN

  -- need to initialise this
progress := 0;

FOR i in 1..columns_array.count
 LOOP
    statement := 'select COUNT(' || columns_array(i) || ') '
    || 'from your_table '
    || 'where ' || columns_array(i) || ' = ''Yes'''
      || ' or ' || columns_array(i) || ' = ''N/A''';

    -- just to debug
    dbms_output.put_line(statement);
    execute immediate statement into counter;

    progress := progress + counter;
    dbms_output.put_line('Counter: ' || counter || ' Progress: ' || progress);
END LOOP;

dbms_output.put_line('Progress: ' || progress);

END;
/

db<>fiddle

Which is a bit inefficient as you repeatedly query the same table. You could make the dynamic more complicated and only hit the table once, or use an XML trick to avoid PL/SQL entirely (similar to this).

But it would be simpler and more efficient to just use conditional aggregation - added to this db<>fiddle

Collectives™ on Stack Overflow

Using an array inside of a select statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related