How can i get count of column from table by using my code?-CodePudding

---These columns in v_array has counts in it but when i run this code ,its returns zero counts for all columns , where is my mistake ,please help me in it ----

        set serveroutput on
     declare 
            type t_list is table of varchar(50) index by PLS_INTEGER;
            r_emp t_list;
            type e_list is varray(4) of varchar(50);
            v_array e_list;
            begin
            v_array:=e_list('CLAIRTY_ID','SERV_ACCT_NUMBER','BB_ACQUISITION_TYPE','ONT_ACQ_TYPE');
            for i in 1..4 loop
            select  sum(case when v_array(i) is null then 1 else 0 end) into r_emp(i) from SZ_GOODS_DETAIL;
             dbms_output.put_line( r_emp(i));
             end loop;
             end;



--------------output--------------------
0
0
0
0


PL/SQL procedure successfully completed.

CodePudding user response：

You seem to be trying to count the number of null values in each of the columns included in your collection. When you do:

select  sum(case when v_array(i) is null then 1 else 0 end) into r_emp(i) from SZ_GOODS_DETAIL;

you are counting how many rows in the table the collection element v_array(i) is null for - which is either going to always be zero (if the collection element is not null, as all your are) or the number of rows in the table (if the element is null). Whether that element is null is nothing to do with the data in the table.

If you want to evaluate the column value corresponding to the value of that collection element for each row in the table then you would either need evaluate the element name with something like:

    select sum(case
      when v_array(i) = 'CLAIRTY_ID' and CLAIRTY_ID is null then 1
      when v_array(i) = 'SERV_ACCT_NUMBER' and SERV_ACCT_NUMBER is null then 1
      when v_array(i) = 'BB_ACQUISITION_TYPE' and BB_ACQUISITION_TYPE is null then 1
      when v_array(i) = 'ONT_ACQ_TYPE' and ONT_ACQ_TYPE is null then 1
      else 0
    end)
    into r_emp(i)

... which makes the collection a bit pointless and probably isn't what you were thinking of, or use dynamic SQL to embed the element value as the column name:

declare 
  type t_list is table of number index by PLS_INTEGER;
  r_emp t_list;
  type e_list is varray(4) of varchar2(50);
  v_array e_list;
  v_stmt varchar2(255);
begin
  v_array:=e_list('CLAIRTY_ID','SERV_ACCT_NUMBER','BB_ACQUISITION_TYPE','ONT_ACQ_TYPE');
  for i in 1..4 loop
    v_stmt := 'select sum(case when "' || v_array(i) || '" is null then 1 else 0 end) from SZ_GOODS_DETAIL';
    -- just for debugging
    dbms_output.put_line(v_stmt);
    execute immediate v_stmt into r_emp(i);
    dbms_output.put_line(r_emp(i));
  end loop;
end;
/

Each iteration of the loop will construct and execute a dynamic statement like:

select sum(case when "CLAIRTY_ID" is null then 1 else 0 end) from SZ_GOODS_DETAIL

... which you can see in the debug output (which you can obviously remove once it's working).

You could also do the calculation using count(), subtracting the number of non-null values form the number of rows:

v_stmt := 'select count(*) - count("' || v_array(i) || '") from SZ_GOODS_DETAIL';

Or you could avoid PL/SQL and use an XML trick:

select column_value,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(case when "' || column_value || '" is null then 1 end) as c '
      || 'from SZ_GOODS_DETAIL'))
  returning content)) as c
from sys.odcivarchar2list('CLAIRTY_ID','SERV_ACCT_NUMBER','BB_ACQUISITION_TYPE','ONT_ACQ_TYPE')

fiddle

CodePudding user response：

What do you expect as a result? What does SZ_GOODS_DETAIL table have to do with it? I guess you wanted to compare collection contents to values stored in that table, but - your code doesn't do that.

Here's what I think you want (which doesn't have to be true):

Table contents:

SQL> SELECT * FROM sz_goods_detail;

        ID NAME
---------- -------------------
         1 CLAIRTY_ID
         2 CLAIRTY_ID
         3 BB_ACQUISITION_TYPE

SQL> SET SERVEROUTPUT ON

Modified procedure (see line #19):

SQL> DECLARE
  2     TYPE t_list IS TABLE OF VARCHAR (50)
  3        INDEX BY PLS_INTEGER;
  4
  5     r_emp    t_list;
  6
  7     TYPE e_list IS VARRAY (4) OF VARCHAR (50);
  8
  9     v_array  e_list;
 10  BEGIN
 11     v_array :=
 12        e_list ('CLAIRTY_ID',
 13                'SERV_ACCT_NUMBER',
 14                'BB_ACQUISITION_TYPE',
 15                'ONT_ACQ_TYPE');
 16
 17     FOR i IN 1 .. 4
 18     LOOP
 19        SELECT SUM (CASE WHEN v_array (i) = s.name THEN 1 ELSE 0 END)
 20          INTO r_emp (i)
 21          FROM sz_goods_detail s;
 22
 23        DBMS_OUTPUT.put_line (v_array(i) ||': '|| r_emp (i));
 24     END LOOP;
 25  END;
 26  /

Result:

CLAIRTY_ID: 2
SERV_ACCT_NUMBER: 0
BB_ACQUISITION_TYPE: 1
ONT_ACQ_TYPE: 0

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

If you want to use dynamic column names then you need to use dynamic SQL:

DECLARE
  r_emp   SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();
  v_array SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
     'CLAIRTY_ID','SERV_ACCT_NUMBER','BB_ACQUISITION_TYPE','ONT_ACQ_TYPE'
  );
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR i IN 1..v_array.COUNT LOOP
    r_emp.EXTEND;
    EXECUTE IMMEDIATE
      'SELECT SUM(CASE WHEN "' || v_array(i) || '" IS NULL THEN 1 ELSE 0 END) FROM SZ_GOODS_DETAIL'
      INTO r_emp(i);
    dbms_output.put_line(v_array(i) || ': ' || r_emp(i));
  END LOOP;
END;
/

Which, for the sample data:

CREATE TABLE SZ_GOODS_DETAIL (CLAIRTY_ID, SERV_ACCT_NUMBER, BB_ACQUISITION_TYPE, ONT_ACQ_TYPE) AS
SELECT 1, 1,    1,    1    FROM DUAL UNION ALL
SELECT 1, 1,    1,    NULL FROM DUAL UNION ALL
SELECT 1, 1,    NULL, NULL FROM DUAL UNION ALL
SELECT 1, NULL, NULL, NULL FROM DUAL;

Outputs:

CLAIRTY_ID: 0
SERV_ACCT_NUMBER: 1
BB_ACQUISITION_TYPE: 2
ONT_ACQ_TYPE: 3

fiddle