SQL Help : Left join

Database

SQL Help : Left join

Hi

im writing a sql  query with Left join  :

sel

a.*

,b.ordr_date

from   tbl_cust_info  a

left join  tbl_ordr_date  b on  a.customer=b.customer 

and

( b.ordr_date between a.starting_date and a.ending_date)

it is showing me  no more spool space , can i mention the   BETWEEN condition in the Left join ?

if  i use the  'Where clause' also the query is not executing . suggest me

3 REPLIES

Re: SQL Help : Left join

ANY  suggestions about the same

Re: SQL Help : Left join

Queries aborting due to insufficient spool is because the quantity of data that is brought in spool for joining between the two tables is more than the allocated spool space for the user. Mostly this is because of improper primary index selection, because the rows needs to be on the same AMP to be joined together regardless of the type of join inner/outer.

BETWEEN clause is more resource intensive and normally ends up in full table scan.

You need to assign more spool space to the user.

Re: SQL Help : Left join

If you use AND operator in OUTER JOIN it will take more SPOOL, try WHERE clause but it will filter the result based on the inner table.