Convert Oracle SQL subquery to join

Hi,

I have an SQL subquery but I want to write it as a join. I know there is a limitation on the IN clause in PL/SQL whereby its Max Limit of Params is 1000. So I need a join statement instead of using the IN with a subquery.
I have spent hours trying to convert it but am Stumped.

Can anyone help please. See attached code.

There are 2 tables..passenger and segment. The segment has a dep_city field which I need to check...and retrieve data from depending on whats in it.
'id' is a common value between the tables.  

Thank you.
select distinct myseg.segment_id, myseg.segment_name
    from segment myseg where myseg.pkno in (
         select segment.pkno 
        
           from passenger, 
                   segment, 
        
           where passenger.date > to_date('16/02/2009', 'dd/MM/yyyy')  
                   and passenger.index= '1'       
                   and seg.dep_city in ('BOS', 'NYC', CHI', 'SFO')
                   and passenger.segment_id = segment.segment_id   
) ;

Open in new window

ShamDAsked:
Who is Participating?
 
FVERCommented:
The easyest way
select distinct myseg.segment_id, myseg.segment_name
    from segment myseg, (
         select segment.pkno 
           from passenger, 
                   segment, 
           where passenger.date > to_date('16/02/2009', 'dd/MM/yyyy')  
                   and passenger.index= '1'       
                   and seg.dep_city in ('BOS', 'NYC', CHI', 'SFO')
                   and passenger.segment_id = segment.segment_id   
) t2
where myseg.pkno = t2.pkno ;

Open in new window

0
 
ShamDAuthor Commented:
I realise there is a slight misnomer in the sql where I am missing a left opening comma before CHI
  and seg.dep_city in ('BOS', 'NYC', CHI', 'SFO')

Open in new window

0
 
reb73Commented:
But to replace the 'and seg.dep_city in ('BOS', 'NYC', 'CHI', 'SFO')' you need to capture this filter list in a table or something to do the join..
0
 
ShamDAuthor Commented:
FVER:

This looks good, I am going to do a buit of testing and will award points later.

Thanks very much.
0
 
reb73Commented:
Actually misread your code initially.. Try the following code with Exists - should normally outperform an inner join

select distinct myseg.segment_id, myseg.segment_name
from segment myseg where exists (
         select NULL
           from passenger, segment 
           where passenger.date > to_date('16/02/2009', 'dd/MM/yyyy')  
                   and passenger.index= '1'       
                   and seg.dep_city in ('BOS', 'NYC', 'CHI', 'SFO')
                   and passenger.segment_id = segment.segment_id
		   and segment.pkno = myseg.pkno
);

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.