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

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 Received on Thu Nov 29 2007 - 13:45:05 CST