asked on

How to locate Syntax error for SQLstate 42000 type error

I have a stored procedure using 5 tables and experiencing a SQLSTATE =42000 incorrect syntax found near the Keyword "ON" . The error message points to a statement near the bottom of the procedure, no where near the first or last ON statement of which there are 10 of them.

FROM
 
 
    	dbo.apply_partyto
  LEFT OUTER JOIN
	   dbo.address
  INNER JOIN 	dbo.company_address
ON	
	dbo.address.addr_id = dbo.apply_partyto.addr_id
 
  INNER JOIN
ON
	dbo.company_address.addr_id = dbo.address.addr_id
  INNER JOIN	
ON
	dbo.company.company_id = dbo.company_address.company_id
 INNER JOIN
ON
	dbo.app_particip_cd.particip_cd = dbo.apply_partyto.particip_cd
AND
 
	dbo.apply_partyto.class = @as_app_class
And
	dbo.apply_partyto.nbr = @ad_app_number
And
	dbo.apply_partyto.sufix = @as_app_suffix

Open in new window

Chris Mangus

You don't have an ON clause for the LEFT OUTER JOIN between dbo.apply_partyto and dbo.address.

Daniel Reynolds

after a first look, it looks like you are missing the on statement for dbo.address
you have from dbo.apply_partyto
left outer join dbo.address
<missing on statement>
Inner join ...blah

DNRITSTAFF

ASKER

so far the two solutions yield the same results in the same syntax error on the " ON" statement

Chris Mangus

You're also missing the joining table name in 3 of the INNER JOINs you posted. If you post the entire query we'd probably stand a better chance of being able to help.

DNRITSTAFF

ASKER

I have attached the complete code snippet for review


CREATE PROCEDURE dbo.app_partyto_listing  
   @as_app_class varchar(15),
   @ad_app_number decimal(10, 1),
   @as_app_suffix varchar(15)
AS 
   
   /* NEW SYNTAX  Inner- Outer Joins 3.19.2008 M3H
   *   Generated by SQL Server Migration Assistant for Sybase.
   *   Contact syb2sql@microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
   */
   CREATE TABLE #customer_list
   (
      class char(4)  NOT NULL, 
      nbr decimal(10, 1)  NOT NULL, 
      sufix char(6)  NOT NULL, 
      comp_or_cont char(1)  NOT NULL, 
      particip_descr varchar(80)  NOT NULL, 
      addr_id int  NOT NULL, 
      customer_id int  NOT NULL, 
      phone_nbr char(14)  NULL, 
      street_1 varchar(50)  NULL, 
      street_2 varchar(50)  NULL, 
      street_3 varchar(50)  NULL, 
      city varchar(25)  NULL, 
      state varchar(3)  NULL, 
      zip char(9)  NULL, 
      country varchar(25)  NULL, 
      customer_name varchar(50)  NOT NULL, 
      percent_interest decimal(18, 12)  NULL
   )
   SELECT 
      dbo.apply_partyto.class, 
      dbo.apply_partyto.nbr, 
      dbo.apply_partyto.sufix, 
      dbo.apply_partyto.comp_or_cont, 
      dbo.app_particip_cd.particip_descr, 
      dbo.apply_partyto.addr_id, 
      dbo.company.company_id AS customer_id, 
      dbo.company.phone_nbr AS phone_nbr, 
      dbo.address.street_1, 
      dbo.address.street_2, 
      dbo.address.street_3, 
      dbo.address.city, 
      dbo.address.state, 
      dbo.address.zip, 
      dbo.address.country, 
      dbo.company.company_name AS customer_name, 
      dbo.apply_partyto.percent_interest AS percent_interest
      INTO #company_list
   FROM
     	dbo.apply_partyto
ON
  LEFT OUTER JOIN
	((((   dbo.address
ON
  INNER JOIN 	dbo.company_address
ON	
	dbo.address.addr_id = dbo.apply_partyto.addr_id)
 
  INNER JOIN
ON
	dbo.company_address.addr_id = dbo.address.addr_id)
  INNER JOIN	
ON
	dbo.company.company_id = dbo.company_address.company_id)
 INNER JOIN
ON
	dbo.app_particip_cd.particip_cd = dbo.apply_partyto.particip_cd)
WHERE 
(
 	dbo.apply_partyto.class = @as_app_class
And
	dbo.apply_partyto.nbr = @ad_app_number
And
  	dbo.apply_partyto.sufix = @as_app_suffix
)
   
   SELECT 
      dbo.apply_partyto.class, 
      dbo.apply_partyto.nbr, 
      dbo.apply_partyto.sufix, 
      dbo.apply_partyto.comp_or_cont, 
      dbo.app_particip_cd.particip_descr, 
      dbo.apply_partyto.addr_id, 
      dbo.contact.contact_id AS customer_id, 
      dbo.contact.phone_nbr AS phone_nbr, 
      dbo.address.street_1, 
      dbo.address.street_2, 
      dbo.address.street_3, 
      dbo.address.city, 
      dbo.address.state, 
      dbo.address.zip, 
      dbo.address.country, 
      
         rtrim(dbo.contact.last_name)
          +
          
         ', '
          +
          
         rtrim(dbo.contact.first_name)
          +
          
         ' '
          +
          
         rtrim(dbo.contact.mid_init)
          +
          
         ' '
          +
          
         rtrim(dbo.contact.jr_sr) AS customer_name, 
      dbo.apply_partyto.percent_interest AS percent_interest
      INTO #contact_list
 
   FROM
   	 	dbo.apply_partyto
ON
 
  LEFT OUTER JOIN
 (((  dbo.address
 
  INNER JOIN 	dbo.contact_address
ON
	dbo.address.addr_id = dbo.apply_partyto.addr_id)
 
  INNER JOIN
ON
	dbo.contact.contact_id = dbo.contact_address.contact_id)
 
 INNER JOIN
ON
	  dbo.app_particip_cd.particip_cd = dbo.apply_partyto.particip_cd)
    
 
Where 
(
      dbo.apply_partyto.class = @as_app_class
And
	  dbo.apply_partyto.nbr = @ad_app_number
And
	  dbo.apply_partyto.sufix = @as_app_suffix
)
 
 
   INSERT #customer_list
      SELECT *
      FROM #company_list
      
   INSERT #customer_list
      SELECT *
      FROM #contact_list
      
   SELECT *
   FROM #customer_list
   
   DROP TABLE #company_list
   DROP TABLE #contact_list
   DROP TABLE #customer_list;

Open in new window

ASKER CERTIFIED SOLUTION

Chris Mangus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DNRITSTAFF

ASKER

Based on this solution, I found an error on my SQL script

There was one modification to the second set of joins where dbo.contact was not bound , once this was corrected the last resolution worked.
Thanks
Great job guys... I really appreciate your help and expertise.

DNRITSTAFF

ASKER

THANK YOU VERY MUCH