OPENQUERY not recognizing valid table names

I truly need help with this production query issue. I have a Openquery that was working until I had to add a concatenated field.   I am stumped just what I am doing wrong.  The incoming data is  a DB2 table (FLVENDM) via a linked server (KBM400MFG).

Here is the query:

use DmStagingVD

SELECT 
     'Delivery' 
     ,A.VMCTRY
	 ,'Delivery' 
	 ,A.VMNO
	 ,CONCAT(A.VMADR1, A.VMADR2)

 FROM OPENQUERY 
 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  ,''CONCAT(B.VMADR1,B.VMADR2)''
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
  '
   ) AS A

Open in new window


Here is the error messages I get when trying to run the query:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'VMADR1'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'VMADR2'.


I have stumbled into yet another problem. I can comment out the troublesome concat and move on but then I hit a new issue with SUBTRING , see the modified query & error below:

here is the query
 FROM OPENQUERY 
 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  --,''CONCAT(B.VMADR1,B.VMADR2)''
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,ISNULL(SUBSTRING([VMADR3],1, (CHARINDEX(' ',REVERSE([VMADR3]),+14 ) )),' ')  AS [DeliveryCity]
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
  '
   ) AS A

Open in new window

this is the error:
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near ',REVERSE([VMADR3]),+14 ) )),'.


I have attached the DB2  file layout in the form of a simple query to this question. I am really wanting to use OPENQUERY as otherwise the query runs forever and a day.

Would someone be able to help me with this problem? It would be most appreciated, Thank you!
sql-select-vender-address-master.rtf
LeogalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LeogalAuthor Commented:
In moving forward I find this error when attempting a substring in openquery....

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]

 FROM OPENQUERY 
 (
 KBM400MFG, 
  '
  SELECT 
       ''Delivery''
	  ,B.VMCTRY 
	  , ''Delivery'' 
	  ,B.VMNO
	  --,''CONCAT(B.VMADR1,B.VMADR2)''
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,''URL''
	  ,ISNULL(SUBSTRING(B.[VMADR3],1, (CHARINDEX('' '',REVERSE(B.[VMADR3]),+14 ) )),'' '')  AS [DeliveryCity]
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
  '
   ) AS A

Open in new window

here is the error

OLE DB provider "DB2OLEDB" for linked server "KBM400MFG" returned message "*N in *N type *N not found. SQLSTATE: 42704, SQLCODE: -204".
Msg 7321, Level 16, State 2, Line 3
An error occurred while preparing the query "
  SELECT 
       'Delivery'
	  ,B.VMCTRY 
	  , 'Delivery' 
	  ,B.VMNO
	  --,'CONCAT(B.VMADR1,B.VMADR2)'
	  ,B.VMPFXN
	  ,B.VMPPHN
	  ,'URL'
	  ,ISNULL(SUBSTRING(B.[VMADR3],1, (CHARINDEX(' ',REVERSE(B.[VMADR3]),+14 ) )),' ')  AS [DeliveryCity]
   FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
  " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG". 

Open in new window

I am feeling very inept. I would truly appreciate any help on the concat & substring issues.
0
Shaun KlineLead Software EngineerCommented:
I would suggest not performing any string manipulation in your OPENQUERY. Bring back all of the columns needed, and then perform the manipulation on the SQL Server side.

In your first query, it would look like this:
SELECT 
      'Delivery' 
      ,A.VMCTRY
        ,'Delivery' 
        ,A.VMNO
        ,CONCAT(A.VMADR1, A.VMADR2)

  FROM OPENQUERY 
  (
  KBM400MFG, 
   '
   SELECT 
        ''Delivery''
         ,B.VMCTRY 
         , ''Delivery'' 
         ,B.VMNO
         ,B.VMADR1
         ,B.VMADR2
    FROM [S1022466].[KBM400MFG].[FLVENDM] AS B
   '
    ) AS A

Open in new window

The same would apply to your second query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeogalAuthor Commented:
Shaun gave me invaluable help that is most appreciated.
0
Shaun KlineLead Software EngineerCommented:
The errors you are receiving are most likely caused by DB2 not understanding the SQL Server functions you are attempting to use in the OPENQUERY statement. The OPENQUERY SQL should follow the source (in your case, DB2) "rules" for SQL.
0
LeogalAuthor Commented:
Shaun, DB2 seems to be a bit obstinate when writing an OPENQUERY.  I am having yet another issue and opened a new question.   I am truly a novice at OPENQUERY but I want to learn more about it as it is very fast and very powerful.
Thank so much for your kind and very helpful assistance!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.

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.