Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Newbie: Simple select with 'IN ALL'

578885Aug 30 2007 — edited Sep 22 2007

Hi..
I have a table PROJECT with data as

SQL> select person, id_state from project order by  1, 2;

PERSON       ID_STATE
---------- ----------
Carl                3
Carl                4
Joe                 3
Mark                2
Mark                3
Mark                4
Paul                1
Paul                2
Sam                 2
Sam                 3

10 rows selected.

I want to select persons from this table having all the id states (2, 3 and 4)

In this case the result would be Mark.

The following quiery works

SQL> select person from project t3,
  2  (
  3  select person p2, id_state from project t2,
  4  (
  5  select person p1 from PROJECT where ID_STATE = 2
  6  ) t1
  7  where t1. p1 = t2.person and t2.id_state = 3
  8  ) t4
  9  where t3.person = t4.p2 and t3.id_state = 4
 10  /

PERSON
----------
Mark

But I think this is too large a quiery for such a simple select. Can any one post a more refined and compact quiery for this simple select.

Thanks all in advance..

Comments

JPreda

I have the same problem.

It's happend to me when passing more than 1 parameter to the target page and then you press the back button of the browser.

Any suggestions?

Hilary Farrell-Oracle

Hi,

I believe you are hitting bug 25838279, which has been fixed in 5.1.2.  A 5.1.1 Patch Set Exception (PSE) is also available for download from My Oracle Support - on the 'Patches & Updates' tab, search for the bug number 25838279.  Please note that you will need to upgrade your instance to APEX 5.1.1 before attempting to apply the PSE to your instance.

Regards,

Hilary

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 1 2007
Added on Aug 30 2007
31 comments
19,845 views