I will try to explain this the best that I can, I have an array of products ['VALUE1','VALUE2']

my table has this data as an example

lets say the values are

How can I build a select statement to check the table if product_id equals VALUE1 and VALUE2 but if the array contains VALUE3 it returns false else it's true I know using a function would be better.

CodePudding user response：

This is how I understood it.

SQL> set serveroutput on;

Sample table:

SQL> select * from product;

PRODUC  ORDER_QTY
------ ----------
VALUE1          5
VALUE2          3

PL/SQL code; it intersects values from the table with contents of collection. If that result is equal to number of values in collection, result is TRUE:

SQL> declare
  2    l_arr sys.odcivarchar2list := sys.odcivarchar2list();
  3    l_cnt number;
  4  begin
  5    l_arr.extend;
  6    l_arr(1) := 'VALUE1';
  7    l_arr.extend;
  8    l_arr(2) := 'VALUE2';
  9
 10    select count(*)
 11      into l_cnt
 12      from (select product_id from product
 13            intersect
 14            select column_value
 15            from table(l_arr)
 16           );
 17    dbms_output.put_line(l_cnt);
 18    if l_cnt = l_arr.count then
 19       dbms_output.put_line('Result is TRUE');
 20    else
 21       dbms_output.put_line('Result is FALSE');
 22    end if;
 23  end;
 24  /
2
Result is TRUE

PL/SQL procedure successfully completed.

What if we add VALUE3 to collection? Result is then FALSE.

SQL> declare
  2    l_arr sys.odcivarchar2list := sys.odcivarchar2list();
  3    l_cnt number;
  4  begin
  5    l_arr.extend;
  6    l_arr(1) := 'VALUE1';
  7    l_arr.extend;
  8    l_arr(2) := 'VALUE2';
  9    l_arr.extend;
 10    l_arr(3) := 'VALUE3';
 11
 12    select count(*)
 13      into l_cnt
 14      from (select product_id from product
 15            intersect
 16            select column_value
 17            from table(l_arr)
 18           );
 19    if l_cnt = l_arr.count then
 20       dbms_output.put_line('Result is TRUE');
 21    else
 22       dbms_output.put_line('Result is FALSE');
 23    end if;
 24  end;
 25  /
Result is FALSE

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

SELECT
   CASE WHEN
      COUNT(CASE WHEN PRODUCT_ID IN ('VALUE1','VALUE2') THEN 1 END) = COUNT(*)
    THEN 'TRUE' ELSE 'FALSE'
   END CHK
FROM T;

or you can just compare collections (NESTED TABLE):

create table t_product as 
select 'VALUE1' product_id from dual union all
select 'VALUE2' product_id from dual union all
select 'VALUE3' product_id from dual;


declare
  type string_table is table of t_product.product_id%type;
  arr_input string_table:=string_table('VALUE1','VALUE2');
  tab_data string_table;
begin
  select product_id bulk collect into tab_data from t_product;
  if tab_data = arr_input 
    then
      dbms_output.put_line('yes');
    else
      dbms_output.put_line('no');
  end if;
end;
/

Or:

select 
    case 
        when sys.ku$_vcnt('VALUE1','VALUE2') = X 
            then 'true'
            else 'false'
    end chk
from (select cast(collect(cast(product_id as varchar2(4000))) as sys.ku$_vcnt) x from t_product);

NB: it's better to create and use own type instead of sys.ku$_vcnt, for example:

create or replace type varchar2_table as table of varchar2(4000);