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

On Nov 28, 10:42 pm, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:
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.

On Nov 28, 10:42 pm, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:
While Charles gave you a workaround, the question must be asked: WHY?

Why do you expect 5 return rows?
SQL implements SET algebra. (well relational algebra but that's
getting really picky)
So why would you expect to get 5 rows in your result set for that
query? The IN operator says basically:
return this row if the emp_no value matches any value in the list that
follows.
Once SQL finds a match it returns that ONE row. So you obviously have
only one row with each of those employee numbers.

Why would you want to return multiple rows?
let's assume for a brief moment there were two rows in Table 1 with
emp_no=789, would you want the result to include 2 rows, 3 rows, or 4
rows?

You may have a valid business reason for wanting the results
described. If you do, it would be interesting and helpful for you to
describe those needs in your post.

HTH,
Ed

Hallo,

Sandy80 schrieb:
try

CREATE TYPE number_list as table of number;
SELECT *
FROM TABLE (number_list (7369, 7369, 7499)) a
, scott.emp b
WHERE a.COLUMN_VALUE = b.empno;

Viktor Wiens