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.

If you're obtaining user input you must be passing it to the query somehow? How are you getting this input, what data structures are you storing it in and how are you passing it to the query? — Ben, Nov 28 '12 at 14:21
Am obtaining it as String and Passing it to the query like: `select distinct SCHM_CODE, sum(DEP_AMT) as AMOUNT from DLY_DEP_VIEW where SCHM_CODE in('" + string + "') group by SCHM_CODE` — Stanley Mungai, Nov 28 '12 at 14:25
Are you looking for something like this http://stackoverflow.com/questions/13580245/sending-an-array-of-values-to-oracle-procedure-to-use-in-where-in-clause/13580606 — A.B.Cade, Nov 28 '12 at 14:37
@Stanley, are you aware that doing `select distinct SCHM_CODE, sum(DEP_AMT) as AMOUNT from DLY_DEP_VIEW where SCHM_CODE in('" + string + "') group by SCHM_CODE` is exposing your program to sql injection attacks ? you should be using a prepared statement — A.B.Cade, Nov 28 '12 at 14:54
@A.B.Cade Something like that in the llink But I need my query to Holds A list of Items and at times One Item As it is getting the input from Jcheckboxes and either one can be clicked or many of them. — Stanley Mungai, Nov 28 '12 at 15:06
@Stanley, well then the solutions described in that post will work for both a string with many values (separated by comma or whatever) or a string with one value — A.B.Cade, Nov 28 '12 at 15:17
@A.B.Cade That will mean I return the Values in a string array and Not Single String as I am doing right? — Stanley Mungai, Nov 29 '12 at 06:48
@Stanley, not necesserally, it can look like this `'a'` or like this `'a,b,c'` and both will work — A.B.Cade, Nov 29 '12 at 06:58
@Stanley, your java in sql codes are all mixed up... how does it look in the sql ? lets say java is like this `String c = "a,b";` and then you concatenate this string to the query (very bad!) then your sql should eventually look like this `... regexp_substr('a,b', '[^,]+',1,level) p ...` . Or maybe you're trying to use `+` to concatenate strings in sql ?? (should be `||` and with `'` instead of `"`) — A.B.Cade, Nov 29 '12 at 12:18

A.B.Cade · Accepted Answer · 2012-11-29T07:07:09.910

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

edited Nov 29 '12 at 07:07

answered Nov 28 '12 at 15:25

A.B.Cade

16,735
1
37
53

I am getting Inavalid identifier on `regexp_substr` and `regexp_count` – Stanley Mungai Nov 29 '12 at 06:39
@Stanley, Did you replace `:your_string` with your string ? – A.B.Cade Nov 29 '12 at 06:42
I replaced the Your String With My String...Even on Toad it still Returns invalid identifier. Is it the table structure or what By the WAY `DLY_DEP_VIEW` is a view – Stanley Mungai Nov 29 '12 at 07:32
I want Something like that on the Sqlfiddle But It is Not running With My VIEW – Stanley Mungai Nov 29 '12 at 07:59
@Stanley, the fact that it's a view shouldn't matter. Can you show what you're trying ? `Inavalid identifier` is usually when a column name is not recognized – A.B.Cade Nov 29 '12 at 09:36
See the Edits , that's direct from the Java Code that is returning Invalid Identifier – Stanley Mungai Nov 29 '12 at 10:40

Nick Krasnov · Answer 2 · 2012-11-28T15:00:37.427

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

Select From a Table where field in All fields

2 Answers2