9 Replies Latest reply: Dec 11, 2012 7:08 AM by BluShadow RSS

    need help with query

    946054
      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
          Handle: piyush9010
          Status Level: Newbie
          Registered: Jun 27, 2012
          Total Posts: 19
          Total Questions: 7 (6 unresolved)
          • 2. Re: need help with query
            Manik
            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
              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
                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
                  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
                    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
                      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
                        Thanks
                        • 9. Re: need help with query
                          BluShadow
                          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