SQL How to only show fields from a list of numbers

Question

I have a table of items, each with an item ID(int). I want to only display items with item ID's based on a string. For example I want to display items "12, 1005, 2". This string is different every time. How would I do this?

So does the string contain comma separated ints and you want to find all records with those ID's? — Tim Schmelter, Aug 20 '14 at 15:02
if it's passed in as a string, you might need to either parse out into a table or use dynamic sql — gloomy.penguin, Aug 20 '14 at 15:06
I should've mentioned, it's for Oracle Reports builder. The string is a User Parameter. — Kevin Schildhorn, Aug 20 '14 at 15:10
This is a common problem and question (stackoverflow has many related questions). Look at this, http://stackoverflow.com/questions/10353969/how-can-i-select-from-list-of-values-in-oracle. I particularly like Justin Cave's approach (which reminds me of a Tom Kyte approach). — Patrick Bacon, Aug 20 '14 at 17:28

AndreDuarte · Answer 1 · 2014-08-20T15:12:56.110

0

If you are using Oracle, you can use IN:

select from item i where i.id in (12,1005,2)

edited Aug 20 '14 at 15:12

answered Aug 20 '14 at 15:04

AndreDuarte

784
1
5
21

How would I use the string though? If I have a `string a = "12,1005,2"` how would i use that in an sql query? – Kevin Schildhorn Aug 20 '14 at 15:40
An unelegant approach (from a performance perspective) could be '%,'||to_char(i.id)||',%' like ','||'12,1005,2'||',' – Patrick Bacon Aug 20 '14 at 17:06
I don't know whats the problem you're trying to solve, but i think it's better to treat the string in you application. Can you use Execute Immediate? You can concat the sql -> "select from item i where i.id in (" with your string, then use execute immediate to query. – AndreDuarte Aug 20 '14 at 17:33

score 0 · Answer 2 · answered Aug 20 '14 at 16:45

0

is your parameter is always consist of 3 numbers or it may be different in runtime?? if it's fixed you can use substr and instr to cut

answered Aug 20 '14 at 16:45

feno noga

5
2

I believe it can be different in runtime. – Kevin Schildhorn Aug 20 '14 at 17:06

score 0 · Answer 3 · edited May 23 '17 at 12:21

I would consider an approach documented by Justin Cave with the question, How can I select from list of values in Oracle. Tom Kyte documents a similar approach (without regexpressions) on his Ask Tom site.

Since you are in an Oracle Apps environment using Oracle Reports, I just provide an example using the items table:

with x as
  ( select '12, 1005, 2' str from dual
  )
select *
from inv.mtl_system_items_b item
where item.organization_id =
  &org_id
and item.inventory_item_id in
  (select to_number(regexp_substr(str,'[^,]+',1,level)) element
  from x
    connect by level <= length(regexp_replace(str,'[^,]+')) + 1
  )

Procedurally in Oracle reports, you could create a function as a program unit that performs this parsing of this comma separated value parameter using similar logic (again, see Justin Cave's parse list function (answer associated with the question I refer to above).

SQL How to only show fields from a list of numbers

3 Answers3