asked on

OPENQUERY syntax errors (not recognizing CROSS APPLY)

I am definitely a novice with OPENQUERY. I was recently helped here no how to use concat & substring with OPENQUERY now I am stumped with how to use CROSS APPLY. This is a production issue so any assistance is gladly welcomed.

here is the query:

use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 --,CONCAT(A.VMADR1, A.VMADR2)
	 ,A.VMPFXN
	 ,A.VMPPHN
	 'URL'
	 ,ISNULL(SUBSTRING([VMADR3],1, (CHARINDEX(' ',REVERSE([VMADR3]),+14 ) )),' ')  AS [DeliveryCity]
	 ,A.VMCOUN
	  ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
					   ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
	 ,A.VMPZIP

 FROM OPENQUERY 

 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP

   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
   	 CROSS APPLY (
		SELECT PATINDEX('' ''%[ ][A-Z][A-Z] %'' '', C.VMADR3 + '' '') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1
  '
   ) AS A

Open in new window

here is the error I am receiving:

OLE DB provider "DB2OLEDB" for linked server "KBM400MFG" returned message "Token *N was not valid. Valid tokens: *N. SQLSTATE: 42601, SQLCODE: -104".
Msg 7321, Level 16, State 2, Line 3
An error occurred while preparing the query "
  SELECT 
       'Delivery'
	  ,B.VMCTRY 
	  , 'Delivery' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,'URL'
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP

   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
   	 CROSS APPLY (
		SELECT PATINDEX(' '%[ ][A-Z][A-Z] %' ', C.VMADR3 + ' ') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1
  " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG".

Open in new window

Leogal

ASKER

Following Shaun Kline's advice from a prior OPENQUERY issue I have modified my query as follows:
use DmStagingVD. Now I have syntax issue that I thought a second set of eyes may help me spot the syntax issue.

here is the query
use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 --,CONCAT(A.VMADR1, A.VMADR2)
	 ,A.VMPFXN
	 ,A.VMPPHN
	 'URL'
	 ,ISNULL(SUBSTRING([VMADR3],1, (CHARINDEX(' ',REVERSE([VMADR3]),+14 ) )),' ')  AS [DeliveryCity]
	 ,A.VMCOUN
	  ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
					   ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
	 ,A.VMPZIP

	FROM [S1022466].[KBM400MFG].[FLVENDM]
	 	 CROSS APPLY (
		SELECT PATINDEX('%[ ][A-Z][A-Z] %', 'VMADR3 + ' ') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1 


 FROM OPENQUERY 

 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP
	 
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B 
  '
   ) AS A

Open in new window

here is the error:

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1 


 FROM OPENQUERY 

 (
 KBM400MFG, 
  '.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near ''.
Msg 105, Level 15, State 1, Line 49
Unclosed quotation mark after the character string '
   ) AS A

Open in new window

Leogal

ASKER

oaky I whittled it down to this syntax error

use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 ,A.VMADR1 
	 ,A.VMADR2 
	 ,A.VMPFXN
	 ,A.VMPPHN
	 'URL'
	 ,ISNULL(SUBSTRING([VMADR3],1, (CHARINDEX(' ',REVERSE([VMADR3]),+14 ) )),' ')  AS [DeliveryCity]
	 ,A.VMCOUN
	  ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
					   ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS [STATE]
			    ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2)))  
	 ,A.VMPZIP

	FROM [S1022466].[KBM400MFG].[FLVENDM]
	 	 CROSS APPLY (
		SELECT PATINDEX('%[ ][A-Z][A-Z] %', VMADR3 + ' ') + 1 AS VMADR3_start_of_state
	) AS assign_alias_1  


 FROM OPENQUERY 

 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP
	 
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B 
  '
   ) AS A

Open in new window

Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'FROM'.

I am trying to figure out what I am missing.

Member_2_2484401

Do you mean "cross join" ?

SOLUTION

Shaun Kline

membership

This solution is only available to members.

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

Start Free Trial

Leogal

ASKER

I have modified my sql and resolved all issues in syntax except on last one:

the error is :
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'FROM'.

the query is:

use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 ,CONCAT(A.VMADR1,A.VMADR2)
	 ,A.VMPFXN
	 ,A.VMPPHN
	 ,'URL'
	 ,ISNULL(SUBSTRING( A.VMADR3 ,1, (CHARINDEX(' ',REVERSE(A.VMADR3 ),+14 ) )),' ')  AS [DeliveryCity]
	 ,A.VMCOUN
	 ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(A.VMADR2))) AS DeliveryAddress
						 ,ISNULL(SUBSTRING(A.VMADR3, 1, CHARINDEX(' ',REVERSE(A.VMADR3), 14)), ' ') AS DeliveryCity
						 ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(A.VMADR3, VMADR3_start_of_state, 2) END AS STATE 
					     ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(B.VMADR2))) AS ADDRESS
	 ,A.VMPZIP

	 FROM [S1022466].[KBM400MFG].[FLVENDM]  
	     CROSS APPLY  (
        SELECT PATINDEX('%[ ][A-Z][A-Z] %', B.[VMADR3] + ' ') + 1 AS VMADR3_start_of_state
 )AS assign_alias_1
 
 FROM OPENQUERY 

 ( 
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  ,''Delivery'' 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP
	 
   FROM [KBM400MFG].[FLVENDM] AS B 
                 

  '
   ) AS A

Open in new window

SOLUTION

Shaun Kline

membership

This solution is only available to members.

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

Start Free Trial

Leogal

ASKER

Scott I have made the change to the first from statement and removed the static fields from the inner query.
I am still getting an error, albeit small it is a roadblock.

The error is:
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'FROM'.

I know it is a syntax issue yet I cannot figure this out, please help.

 use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 ,CONCAT(A.VMADR1,A.VMADR2)
	 ,A.VMPFXN
	 ,A.VMPPHN
	 ,'URL'
	 ,ISNULL(SUBSTRING( A.VMADR3 ,1, (CHARINDEX(' ',REVERSE(A.VMADR3 ),+14 ) )),' ')  AS [DeliveryCity]
	 ,A.VMCOUN
	  ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(A.VMADR2))) AS DeliveryAddress
						 ,ISNULL(SUBSTRING(A.VMADR3, 1, CHARINDEX(' ',REVERSE(A.VMADR3), 14)), ' ') AS DeliveryCity
						 ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(A.VMADR3, VMADR3_start_of_state, 2) END AS STATE 
					     ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(B.VMADR2))) AS ADDRESS
	 ,A.VMPZIP

	  FROM A
	     CROSS APPLY  (
        SELECT PATINDEX('%[ ][A-Z][A-Z] %', B.[VMADR3] + ' ') + 1 AS VMADR3_start_of_state
 )AS assign_alias_1

 FROM OPENQUERY 

 ( 
 KBM400MFG, 
  '
  SELECT 
	  ,B.VMCTRY 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP
	 
   FROM [KBM400MFG].[FLVENDM] AS B 


  '
   ) AS A

Open in new window

SOLUTION

Shaun Kline

membership

This solution is only available to members.

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

Start Free Trial

Leogal

ASKER

here are the query & resulting error:

OLE DB provider "DB2OLEDB" for linked server "KBM400MFG" returned message "Token *N was not valid. Valid tokens: *N. SQLSTATE: 42601, SQLCODE: -104".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query " SELECT 
	  ,B.VMCTRY 
	  ,B.VMNO
	  ,B.VMADR1
	  ,B.VMADR2 
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,B.VMADR3
	  ,B.VMCOUN
	  ,B.VMPAD3
	  ,B.VMPZIP

	 
   FROM [KBM400MFG].[FLVENDM] AS B
  " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG".

Open in new window

Shaun Kline

That would indicate that the query is invalid on the DB2 server. My guess would be how the table is named. From my limited experience calling DB2 tables from SQL Server, you need a 3 part table name: database name/catalog, schema name and table name. This Microsoft page may help.

ASKER CERTIFIED SOLUTION

Shaun Kline

membership

This solution is only available to members.

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

Start Free Trial

Leogal

ASKER

When I take the alias off as shown below the query works?

SELECT *
FROM OPENQUERY 
 ( 
 KBM400MFG, 
  ' SELECT 
	   VMCTRY 
	  ,VMNO
	  ,VMADR1
	  ,VMADR2 
	  ,VMPFXN
	  ,VMPPHN
	  ,VMADR3
	  ,VMCOUN
	  ,VMPAD3
	  ,VMPZIP

	 
   FROM [KBM400MFG].[FLVENDM] AS B
  '
   ) AS A

Open in new window

SOLUTION

Shaun Kline

membership

This solution is only available to members.

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

Start Free Trial

Leogal

ASKER

I have been successful in getting the query to work !

Here is the query:

SELECT 
   VMCTRY 
	  ,VMNO
	  ,VMADR1
	  ,VMADR2 
	  ,VMPFXN
	  ,VMPPHN
	  ,VMCOUN
	  ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2))) AS DeliveryAddress
						 ,ISNULL(SUBSTRING(VMADR3, 1, CHARINDEX(' ',REVERSE(VMADR3), 14)), ' ') AS DeliveryCity
						 ,CASE WHEN VMADR3_start_of_state < 2 THEN '' 
                              ELSE SUBSTRING(VMADR3, VMADR3_start_of_state, 2) END AS STATE 
					     ,(RTRIM(VMADR1) +' '+ RTRIM(LTRIM(VMADR2))) AS ADDRESS

        ,VMPZIP

FROM OPENQUERY 
 ( 
 KBM400MFG, 
  ' SELECT 
	   VMCTRY 
	  ,VMNO
	  ,VMADR1
	  ,VMADR2 
	  ,VMPFXN
	  ,VMPPHN
	  ,VMADR3
	  ,VMCOUN
	  ,VMPAD3
	  ,VMPZIP

	 
   FROM [KBM400MFG].[FLVENDM] AS B
  '
   ) AS A
         CROSS APPLY (SELECT PATINDEX('%[ ][A-Z][A-Z] %', A.[VMADR3] + ' ') + 1 AS VMADR3_start_of_state) AS StateFinder

Open in new window

Shaun, thank you so very much for your assistance. I have learned new methods that are cleaner and have a bit better handle on OPENQUERY.

Shaun Kline

Glad to assist.

Leogal

ASKER

Shaun has once again been invaluable help in teaching me more about OPENQUERY and its syntax.