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.

Request for help

647960Jul 2 2008 — edited Jul 2 2008
Dear All

I Have The Following Tables


Table A As Val As Column Contains - 1,2,3,5,6
Table B As Val As Column Contains- 2,3,4

When I Am Using Query

Using Intersect


Select Val From A Where Val In (1,2,3) - Q1
Intersect
Select Val From B Where Val In (2,3) -Q2

Now I Get Result As 2,3 Only When Two Q1,Q2 Are Executed.

Then I Am Using

Select Val From A Where Val In (5,6) - Q1
Intersect
Select Val From B Where Val In (2,3) -Q2

Now I Got No Rows Return

But I Want The Query Q2 Result Separatly When Q1 Is Also Not Fired. If Q1,Q2 Is Fires Common Values
Only To Be Return....


If Any Idea Bout Query ??????????


Thanks

K.Prabakaran

Comments

BluShadow
Sorry, but your issue isn't clear.

The output you are getting is what I would expect from those queries. What are you wanting exactly? Perhaps show us by giving an example of the data if you have trouble explaining it in words.
damorgan
First run your intersect without a WHERE clause.
Examine the returned rows.

Anything you put in the WHERE clause filters (limits) the rows that will be returned by that portion of the query BEFORE the intersection takes place.
Sven W.
there is also a MINUS operator. Maybe you like that one more then INTERSECT? But since you didn't tell us your business case, we can only guess.
Aketi Jyuuzou

Maybe below.

select aVal
from (select a.Val as aVal,b.Val as bVal,
      max(b.Val) over() as hasNull
        from A Left join B
          on a.Val = b.Val)
 where hasNull is null
    or bVal is not null
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 30 2008
Added on Jul 2 2008
4 comments
1,143 views