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!

*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.

Jobs

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!

Resources

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