1

I am getting input into data variable as a string of numbers . but emp_id is integer datatype so , when i ran this query it throws error

**i am unable to work "with" or declaring variables as part of solution . there are similar questions which doesnt satisfy my question.

data is not from a table it is given by some program

so, please dont mark it duplicate** i want some kind of cast/ inner query such that outer query can succesfully run

data='1,2,3'

select *  from employees where emp_id in (data) 
select *  from employees where emp_id in ('1,2,3')

this throws error - invalid number
i think we should remove single quotes or cast into list of integers like select * from employees where emp_id in (1,2,3)

i think data has

data='1,2,3'

select *  from employees where emp_id in (data) 
select *  from employees where emp_id in ('1,2,3')

expected output is

i want some kind of cast/ inner query such that outer query can succesfully run

such as

select *  from employees where emp_id in (your query or cast or function)
manojreddy
  • 9
  • 3
  • @mto this is different from given duplicate questions. i have added details regarding this in the question again . i dont need with statement in the solution and i dont want to create functions of sql . **i want different solutions such as an inner query sort the problem such that outer query can run properly or using regex** – manojreddy Oct 16 '19 at 10:00
  • you can use this same trick i show here - it'll work with your in subquery https://www.thatjeffsmith.com/archive/2018/03/building-an-object-search-for-sqlcl/ – thatjeffsmith Oct 16 '19 at 12:26
  • but what i'm doing is the VERY EXACT thing in the first link/answer posted in the dupe info, you can use that https://stackoverflow.com/questions/27738359/select-from-table-with-varying-in-list-in-where-clause – thatjeffsmith Oct 16 '19 at 12:28
  • @thatjeffsmith thank you i got the idea at first i was like they are using " with " statement and such . but, the query in "with " statement as sub query for my query was the solution thanks – manojreddy Oct 16 '19 at 13:22
  • select * from employees where emp_id in (SELECT to_number(trim(regexp_substr('1,2,3', '[^,]+', 1, LEVEL))) ids 3 FROM temp 4 CONNECT BY instr('1,2,3', ',', 1, LEVEL - 1) > 0) – manojreddy Oct 16 '19 at 13:22
  • glad to hear you got it going - now give a nice upvote to one of those previous answers :) – thatjeffsmith Oct 16 '19 at 13:31
  • @thatjeffsmith can u look tnto this – manojreddy Oct 22 '19 at 06:15
  • how can we group 5 pm yesterday to 5 pm today records to todays date https://stackoverflow.com/q/58497919/12212199?sem=2 – manojreddy Oct 22 '19 at 06:15
  • you're lucky, Connor has already answered your question – thatjeffsmith Oct 22 '19 at 08:32

0 Answers0