This discussion is archived
9 Replies Latest reply: Dec 11, 2012 5:08 AM by BluShadow RSS

need help with query

946054 Newbie
Currently Being Moderated
Hi,

I have a requirment to show the values according to the values available in IN clause of the query.

For example:

SELECT *
FROM emp
WHERE empno IN ('2345', '1234', '5678')

Required ouput:

2345
1234
5678

I want a create a dynamic query which will display result in order the values are passed in IN clause
  • 1. Re: need help with query
    Ora Pro
    Currently Being Moderated
    Handle: piyush9010
    Status Level: Newbie
    Registered: Jun 27, 2012
    Total Posts: 19
    Total Questions: 7 (6 unresolved)
  • 2. Re: need help with query
    Manik Expert
    Currently Being Moderated
    May be:

    ;) Simple if you have column empno in your table emp..
    select empno from emp where empno in ('2345', '1234', '5678');
    Cheers,
    Manik.
  • 3. Re: need help with query
    946054 Newbie
    Currently Being Moderated
    HI Manik,

    If you fire the query which i have provided will not give result as required.
    and in the query i have given only 3 values but in realy prodcution situation i will be having 1000 records to be selected.
  • 4. Re: need help with query
    Purvesh K Guru
    Currently Being Moderated
    One way of achieving it
     SELECT *
     FROM emp
     WHERE empno IN ('2345', '1234', '5678')
    order by case when empno = '2345' then 1
                       when empno = '1234' then 2
                       when empno = '5678' then 3
                       else 4
                end;
    Certainly not a best way of achieving but the requirement isn't the best too...

    One another way of achieving, maybe rather expensive way {message:id=10581744}.

    Edited by: Purvesh K on Dec 5, 2012 12:55 PM
    --Added Link to Stew's post.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 5. Re: need help with query
    Ashu_Neo Pro
    Currently Being Moderated
    Manik wrote:
    May be:
    
     Simple if you have column empno in your table emp..
    
    select empno from emp where empno in ('2345', '1234', '5678');
    I am not sure, the OP is asking for this!
    Piyush,
    it seems, you have a dynamic comma separate string of empnos and these may or may not have entries in emp table( assuming ). the query can be like this:-
    WITH t(str) AS
    (
    Select '2345,1234,5678' from dual /* dynamic string as of OP's posting */
    ),
    tt AS
    (
    Select regexp_substr(t.str,'[^,]+',1,level) eid FROM t
    CONNECT BY level-1 <= regexp_count(t.str, ',')  /* 11g */
    )
    Select eid FROM tt, emp where tt.eid = emp.empno(+)
    /
    Op:-
    EID
    --------------
    2345
    1234
    5678
    I hope, I got your query!

    Thanks!

    Edited by: Ashu_Neo on Dec 5, 2012 1:02 PM
  • 6. Re: need help with query
    jeneesh Guru
    Currently Being Moderated
    Ashu_Neo wrote:
    it seems, you have a dynamic comma separate string of empnos and these may or may not have entries in emp table( assuming ).
    Or simply:
    WITH emp as
    (
      select 1234 empno from dual union all
      select 2345 from dual union all
      select 5678 from dual union all
      select 234  from dual union all
      select 123  from dual
    )
    Select *
    FROM emp 
    where ','||'2345,1234,5678,999'||',' like '%,'||empno||',%'
    order by instr(','||'2345,1234,5678,999'||',',
                    ','||empno||',');
    
    EMPNO
    -----
     2345 
     1234 
     5678 
  • 7. Re: need help with query
    Ashu_Neo Pro
    Currently Being Moderated
    Or simply:
    WITH emp as
    (
      select 1234 empno from dual union all
      select 2345 from dual union all
      select 5678 from dual union all
      select 234  from dual union all
      select 123  from dual
    )
    Select *
    FROM emp 
    where ','||'2345,1234,5678,999'||',' like '%,'||empno||',%'
    order by instr(','||'2345,1234,5678,999'||',',
                    ','||empno||',');
     
    EMPNO
    -----
     2345 
     1234 
     5678
    It's not simple, is it! rather I say in a different way tried and that comes in a simple way through a different expert mind.. ! .. ;)
  • 8. Re: need help with query
    946054 Newbie
    Currently Being Moderated
    Thanks
  • 9. Re: need help with query
    BluShadow Guru Moderator
    Currently Being Moderated
    piyush9010 wrote:
    WHERE empno IN ('2345', '1234', '5678')

    I want a create a dynamic query which will display result in order the values are passed in IN clause
    Bear in mind that the values passed into the IN clause are a 'set' of values, not an ordered list. So, by default, Oracle doesn't care what order the values are in and there is not considered to be any order to them that you can use to determine the order of your results. It would be the same as asking for data to be returned in the same order it was inserted into a database table. The table is also a set of data and doesn't know, remember, or care what order the data was inserted.

    If you want an order to your data, then you need to provide that information.

    Also, if there could be many values, then you would be better to store those in a table (or global temporary table) and join on that, rather than using an IN clause...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 1 as ord, 7839 as empno from dual union all
      2             select 2, 7788 from dual union all
      3             select 3, 7934 from dual)
      4  select e.empno, e.ename
      5  from emp e join t on (t.empno = e.empno)
      6* order by t.ord
    SQL> /
    
         EMPNO ENAME
    ---------- ----------
          7839 KING
          7788 SCOTT
          7934 MILLER

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points