×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

WHERE IN CLAUSE WITH same literal

WHERE IN CLAUSE WITH same literal

WHERE IN CLAUSE WITH same literal

(OP)
Hi, I'm working on a application program where I need to use where in function to include my literals which are received during run time. Assume below is my table.
Table name : Master
SEQ_NO NAME
1 XXXX
2 YYYY
1 ZZZZ

My queries are..
The literals will change based on the need.

select * from master where seq_no in(1,2)
This query is returning just 3 rows which seems to be OK and I don't have questions on that.
select * from master where seq_no in(1,1)
This query is returning just 2 rows. It makes sense because only 2 rows are available. I'm using DB2 and the question is how DB2 decides it's the same row and eliminates the second literal and not returning 4 same rows ? if the literals are same, it just ignores the 2nd one ?
select * from master where seq_no in(2,2)
This query is returning just 1 row. It makes sense because only 1 row is available. I'm using DB2 and the question is.. how DB2 decides it's the same row and eliminates the second literal and not returning 2 same rows ? if the literals are same, it just ignores the 2nd one ?


Thanks for your help in Advance!
CVR





RE: WHERE IN CLAUSE WITH same literal

HI,

(1,1) returns 1 row despite there are 2 rows in your example.

(2,2) returns 2 rows despite there is 1 row in your example.

Did I get this correct?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: WHERE IN CLAUSE WITH same literal

(OP)
thanks for the response skipvought!

Sorry - The question I placed was exchanged between for some reason. I didn't mean that way. I corrected my most.

(1,1) returns 2 rows. -- > how DB2 decides it's the same row and eliminates the second literal and not returning 4 same rows ? if the literals are same, it just ignores the 2nd one ?

(2,2) returns 1 rows. --> how DB2 decides it's the same row and eliminates the second literal and not returning 2 same rows ? if the literals are same, it just ignores the 2nd one ?

RE: WHERE IN CLAUSE WITH same literal

Ahhhh!

I believe that I see what your are assuming. You're saying that you expect ...

IN (1) to return
SEQ_NO NAME
1      XXXX
1      ZZZZ
 

IN (1,1) to return
SEQ_NO NAME
1      XXXX
1      ZZZZ
1      XXXX
1      ZZZZ
 

IN (1,1,1) to return
SEQ_NO NAME
1      XXXX
1      ZZZZ
1      XXXX
1      ZZZZ
1      XXXX
1      ZZZZ
 

...etc.

Does not work that way. The IN() statement is like a list of ORs. All it's requiring is to return the rows that meet the criteria: and there are only TWO rows that meet that criteria.

If you want what you expect, then use UNIONs
select * from master where seq_no in(1,1)
UNION
select * from master where seq_no in(1,1)
 


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close