Select From a Table where field in All fields

Question

I have to select values from a table in all rows like this:

select distinct SCHM_CODE, 
       sum(DEP_AMT) as AMOUNT 
  from DLY_DEP_VIEW  
 where Schm_code in (select SCHM_CODE 
                       from DLY_DEP_VIEW )
 group by schm_code

I will be taking input from user input, I do not want the select statement in the brackets, I need to return a value for all in there like:

select distinct SCHM_CODE, 
       sum(DEP_AMT) as AMOUNT 
  from DLY_DEP_VIEW  
 where Schm_code in (ALL_SCHM_CODES)
 group by schm_code

And this is Giving me Invalid Identifier: (EDITS)

select distinct SCHM_CODE, 
       sum(DEP_AMT) as AMOUNT 
  from DLY_DEP_VIEW  
 where Schm_code in (select regexp_substr('" + c + "', '[^,]+',1,level) p
          from dual t
       connect by level <= regexp_count('" + c + "', ',') + 1
)
 group by schm_code;

Since the value in the brackets keep changing in my application. What is the best way to achieve this? The query is inside Java Code.

Answer 1

You can try something like this:

select distinct SCHM_CODE, 
       sum(DEP_AMT) as AMOUNT 
  from DLY_DEP_VIEW  
 where Schm_code in (select regexp_substr(:your_string, '[^,]+',1,level) p
          from dual t
       connect by level <= regexp_count(:your_string, ',') + 1
)
 group by schm_code

:your_string is the string you got as input from the user which can contain one value or many (comma separated)

Here is a sqlfiddle demo

BTW, use a prepared statement with a bind variable, don't just concatenate the input string.
Read more here

Answer 2

You can use a nested table, as one of the methods:

• Create a nested table type. Assumption was made that the Schm_code is of number datatype.

  SQL> create or replace type t_list as table of number
    2  /
  
  Type created
  

• Rewrite the query as follows. If a list is a list of strings, then each element of the list must be enclosed with single quotation marks:

  select distinct SCHM_CODE, 
         sum(DEP_AMT) as AMOUNT 
    from DLY_DEP_VIEW  
    where Schm_code in (Select column_value
                          from table(t_list(<<your list of codes>>)))
    group by schm_code
  

In this example, for the sake of demonstration, Sql*plus has been used to execute a query and elements has been typed manually :

SQL> select first_name
  2       , last_name
  3    from employees t
  4   where t.employee_id in (select column_value
  5                             from table(t_list(&list))
  6                           );
Enter value for list: 100,200
old   5:                            from table(t_list(&list))
new   5:                            from table(t_list(100,200))

FIRST_NAME           LAST_NAME                                                  
-------------------- -------------------------                                  
111                  King                                                       
Jennifer             Whalen                                                     

SQL> select first_name
  2       , last_name
  3    from employees t
  4   where t.employee_id in (select column_value
  5                             from table(t_list(&list))
  6                           );
Enter value for list: 110,300,220
old   5:                            from table(t_list(&list))
new   5:                            from table(t_list(110,300,220))

FIRST_NAME           LAST_NAME                                                  
-------------------- -------------------------                                  
John                 Chen