This discussion is archived
8 Replies Latest reply: Dec 4, 2012 9:38 AM by berx 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 have many records like this.
  • 1. Re: need help with query
    Maqsood Hussain Journeyer
    Currently Being Moderated
    Your question is not clear.

    What do you mean by I have many records like this. ?
  • 2. Re: need help with query
    946054 Newbie
    Currently Being Moderated
    Like i have 500-600 records
  • 3. Re: need help with query
    Maqsood Hussain Journeyer
    Currently Being Moderated
    Please explain in detail what you want to select from Emp table. Which employees data you want to see ?.

    Do you want to select only these 3 employees ('2345', '1234', '5678') ?. If yes then your query is correct . How does it matter how many records you have ?.
  • 4. Re: need help with query
    946054 Newbie
    Currently Being Moderated
    Actually i want to retrive data in the same sequence as the values are passed in IN Clause.

    normally when we query the result is not shown according to the sequence of the values passed in IN clause.
  • 5. Re: need help with query
    MLBrown Journeyer
    Currently Being Moderated
    You could use CASE or DECODE in your order by statement, but I'm not sure how dynamic you would be able to make it.
    SELECT *
      FROM emp
     WHERE empno IN ('2345', '1234', '5678')
     order by 
      case empno
       when 2345 then 1
       when 1234 then 2
       when 5678 then 3
      end;
    or
    SELECT *
      FROM emp
     WHERE empno IN ('2345', '1234', '5678')
     order by decode(empno, 2345, 1,
                            1234, 2,
                            5678, 3)
    If this doesn't help then I would suggest closing this post and asking the question in the SQL Forum: {forum:id=75}
  • 6. Re: need help with query
    946054 Newbie
    Currently Being Moderated
    Thanks MLbrown,

    But in the following example there are only 3 values so we can use decode or case.

    But my actual query contains more than 3 value. So in that case what can be done.
  • 7. Re: need help with query
    MLBrown Journeyer
    Currently Being Moderated
    How will you be using this query? Is this in a procedure, a report, etc... How is the SQL created? What determines the values for the where cluase?

    If this is going to be a stored procedure and the values are passed in as a parameter, then you could logically build the order by clause.
  • 8. Re: need help with query
    berx Explorer
    Currently Being Moderated
    sorry, even oracle calls it an in_list, it should be called +in_set+.
    There is no given order - even when you find any way to get it under special circumstances, don't accept it as a solution.

    The only way to get anything ordered in SQL is an explicit ORDER BY.

    I'd suggest to create a global temporary table, (order [number], empno [varchar] ) - so you can join it (as replacement of IN) and order by the order-number.

    btw,
    please use a proper SQL - Forum, this one is for SQLDeveloper.
    I'd suggest [url https://forums.oracle.com/forums/forum.jspa?forumID=75]SQL and PL/SQL.

    Martin

Legend

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