Leogal
asked on
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:
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
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Shaun gave me invaluable help that is most appreciated.
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.
ASKER
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!
Thank so much for your kind and very helpful assistance!
ASKER
here is the query
Open in new window
here is the errorOpen in new window
I am feeling very inept. I would truly appreciate any help on the concat & substring issues.