• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

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

0
ShamD
Asked:
ShamD
  • 2
  • 2
1 Solution
 
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
 
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:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now