Re: Need help with one query--ASAP

On Nov 28, 10:42 pm, Sandy80 <svarshneym..._at_gmail.com> wrote:
> Hi,
>
> I have a query in which I have a value that is repeated a number of
> times in the where clause. If I use the 'in' clause it returns only 1
> value no matte how many no. of times the value is repeated. For eg.
>
> select legacy_emp_no from Table 1
> where
> emp_no in
> (1234,
> 1234,
> 1234,
> 2345,
> 2345)
>
> Now what this query returns is one row per emp_no i.e.
> 456
> 789
>
> What I want the query to return is:
> 456
> 456
> 456
> 789
> 789
>
> I mean I want the return value to be repeated the same no. of times it
> is repeated in the 'in' clause.
> Please help ASAP.
>
> Regards,
> Sandy

The above will not work, but there may be a work-around. An example: The set up:
CREATE TABLE T1 (LEGACY_EMP_NO NUMBER(10));

INSERT INTO T1 VALUES (1234);
INSERT INTO T1 VALUES (2345);
INSERT INTO T1 VALUES (1111);

Create a quick counter, that will permit us to perform a limited Cartesian join:
SELECT
  ROWNUM COUNTER
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

   COUNTER

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

Placing the counter into an inline view, and then using DECODE to specify the maximum value for the counter based on the value of the LEGACY_EMP_NO column:
SELECT
  LEGACY_EMP_NO
FROM
  T1,
  (SELECT
    ROWNUM COUNTER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10) C
WHERE
  LEGACY_EMP_NO IN (1234,2345)
  AND C.COUNTER<=DECODE(LEGACY_EMP_NO,1234,3,2345,2,0);

LEGACY_EMP_NO

         1234
         2345
         1234
         2345
         1234

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Nov 29 2007 - 05:52:30 CST