Solved

TSQL Column Qualifier or table undefined error

Posted on 2014-10-01
6
171 Views
Last Modified: 2014-10-02
When attempting to run a TSQL script using linked servers I am getting an error where a column or a table is not being recognized by the query.  

I suspect that it has something to do with the last join in the SQL script where I am joining to a second linked server and one of its tables because when I take that join and it where statement I do not receive any errors. If I remove the where statement I still return the error when trying to run the script.  I can only resolve the error by removing the VKITMSTR join altogether and that is not good as I need to join to that file and perform the where statement.


Here is the error I am receiving:
OLE DB provider "DB2OLEDB" for linked server "KBM400MFG" returned message "Column qualifier or table *N undefined. SQLSTATE: 42703, SQLCODE: -5001".
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query " SELECT    
				 b.PELINE 
	            ,b.PENO	            
				,b.PECSDT
				,c.POCATG
				,b.PEPN
				,c.POCO
				,d.POCOM1
				,c.POCOST
				,c.POQTY
				,c.POVEND
				
				



	 FROM  [KBM400MFG].[FLPODET] B  
	      LEFT OUTER JOIN [KBM400MFG].[FLPOSUM] c ON  B.[PECO] = C.[POCO] AND B.[PENO] = C.[PONO] 
	      LEFT OUTER JOIN [KBM400MFG].[FLSTCMST] D ON  B.[PECO] = D.[POCO] AND B.[PENO] = D.[PONO] 
		  inner join [VD_GPL].[VKITMSTR]  E on  E.[IMPN] = A.[PEPN] and E.[IMCO] = A.[PECO]
 
 
  " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG". 

Open in new window


Here is the SQL script:
USE DmStagingVD

SELECT    TOP  150
			 ISNULL([PELINE],' ')  as [Line Number]
			,ISNULL([PENO],' ')  as [Purchase Order] 
			,ISNULL([PECSDT],'01-01-1900 00:00:00') as [Delivery Date] 
			,ISNULL([POCATG], ' ')as [External] 
			,ISNULL([PEPN],' ') as [Item Number] 
			,ISNULL([POCOM1],' ') AS NAME 
			,ISNULL([POCOST],00.00)  as [Unit Price] 
			,ISNULL([POQTY],0)   as [Quantity]  
			,ISNULL([POVEND],99999999)  as [Vendor Account] 
			,1  as [RecId]
			,' ' as [ACCOUNTINGDISTRIBUTIONTEMPLATE]
			,' ' as [ActivityNumber]
			,' ' as [ASSETBOOKID]
			,' ' as [ASSETGROUP]
			,' ' as [ASSETID]
			,' ' as [BARCODE]	
			,' ' as [BARCODETYPE]
			,' ' as [BLOCKED]
			,' ' as [CASETAGGING]
			,' ' as [CFOPTABLE_BR_CFOPID]
			,' ' as [COMPLETE]
			,' ' as [CONFIGID]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [CONFIRMEDDLV]
			,0.00 as [CONFIRMEDTAXAMOUNT]
			,' ' as [CONFIRMEDTAXWRITECODE]
			,' ' as [COUNTYORIGDEST]
			,' ' as [COVREF]
			,' ' as [CREATEFIXEDASSET]
			,' ' as [CURRENCYCODE]
			,' ' as [CUSTOMERREF]
			,' ' as [CUSTPURCHASEORDERFORMNUM]
			,' ' as [DEFAULTDIMENSION]
			,' ' as [DEFINITIONGROUP]
			,' ' as [DELIVERYADDRESS]
			,' ' as [DELIVERYCITY]
			,' ' as [DELIVERYCOUNTRYREGIONID]
			,' ' as [DELIVERYCOUNTY]
			,' ' as [DELIVERYLOCATIONROLE]
			,' ' as [DELIVERYNAME]
			,' ' as [DELIVERYSTATE]
			,' ' as [DELIVERTYSTREET]
			,' ' as [DELIVERYTYPE]
			,' ' as [DELIVERYZIPCODE]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [DEPRECIATIONSTARTDATE]
			,0.00 as [DISCAMOUNT]
			,0.00 as [DISCPERCENT]
			,' '  as [EDITABLEINWORKFLOW]
			,' ' as [EXECUTIONID]
			,' ' as [GSTHSTTAXTYPE_CA]
			,' ' as [INTERCOMPANYINVENTTRANSID]
			,' ' as [INTERCOMPANYORIGIN]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [INTRASTATFULFILLMENTDATE_HU]
			,' ' as [INVENTBATCHID]
			,' ' as [INVENTCOLORID]
			,0.00 as [INVENTINVOICENOW]
			,' ' as [INVENTLOCATIONID]
			,0.00 as [INVENTRECEIVEDNOW]
			,' ' as [INVENTREFID]
			,' ' as [INVENTREFTRANSID]
			,' ' as [INVENTSERIALID]
			,' ' as [INVENTSITEID]
			,' ' as [INVENTSIZEID]
			,' ' as [INVENTSTYLEID]
			,' ' as [ISDELETED]
			,' ' as [ISFINALIZED]
			,' ' as [ISINVOICEMATCHED]
			,' ' as [ISMODIFIED]
			,' ' as [ISPWP]
			,' ' as [ISSELECTED]
			,' ' as [ITEMBOMID]
			,' ' as [ITEMPBAID]
			,' ' as [ITEMROUTEID]
			,' ' as [ITEMTAGGING]
			,' ' as [LEDGERDIMENSION]
			,0.00 as [LINEAMOUNT]
			,' ' as [LINEDELIVERYTYPE]
			,0.00 as [LINEDISC]
			,' ' as [LINEHEADER]
			,0.00 as [LINEPERCENT]
			,0.00 as [MARKUPTRANS_CALCULATEDAMOUNT]
			,' ' as [MARKUPTRANS_CURRENCYCODE]
			,' ' as [MARKUPTRANS_DOCUMENTSTATUS]
			,' ' as [MARKUPTRANS_INTERCOMPANYCOMPANYID]
			,' ' as [MARKUPTRANS_INTERCOMPANYINVOICEID]
			,' ' as [MARKUPTRANS_INTERCOMPANYMARKUPUSEVALUE]
			,0.00 as [MARKUPTRANS_INTERCOMPANYMARKUPVALUE]
			,' ' as [MARKUPTRANS_ISDELETED]
			,' ' as [MARKUPTRANS_ISMODIFIED]
			,' ' as [MARKUPTRANS_KEEP]
			,0 as [MARKUPTRANS_LINENUM]
			,' ' as [MARKUPTRANS_MARKUPCATEGORY]
			,' ' as [MARKUPTRANS_MARKUPCODE]
			,' ' as [MARKUPTRANS_MODULECATEGORY]
			,' ' as [MARKUPTRANS_MODULETYPE]
			,' ' as [MARKUPTRANS_ORIGTABLEID]
			,0.00 as [MARKUPTRANS_POSTED]
			,0.00 as [MARKUPTRANS_TAXAMOUNT]
			,' '  as [MARKUPTRANS_TAXAUTOGENERATED]
			,' ' as [MARKUPTRANS_TAXGROUP]
			,' ' as [MARKUPTRANS_TAXITEMGROUP]
			,' ' as [MARKUPTRANS_TAXWRITECODE]
			,convert(dateTime,'1901-01-01 00:00:00.000')as [MARKUPTRANS_TRANSDATE]
			,' ' as [MARKUPTRANS_TRANSTABLEID]
			,' ' as [MARKUPTRANS_TXT]
			,0.00 as [MARKUPTRANS_VALUE]
			,' ' as [MARKUPTRANS_VOUCHER]
			,' ' as [MATCHINGPOLICY]
			,0.00 as [MULTILNDISC]
			,0.00 as [MULTILNPERCENT]
			,' ' as [OPERATIONTYPE_MX]
			,0.00 as [OVERDELIVERYPCT]
			,' ' as [PALLETTAGGING]
			,' ' as [PDSCALCULATIONID]
			,0.00 as [PDSCWINVENTRECEIVEDNOW]
			,0 as [PDSCWQTY]
			,0.00 as [PDSCWREMAININVENTFINANCIAL]
			,0  as [PDSCWREMAININVENTPHYSICAL]
			,' ' as [PORT]
			,0.00 as [PRICEUNIT]
			,' ' as [PROJCATEGORYID]
			,' ' as [PROJID]
			,' ' as [PROJLINEPROPERTYID]
			,' ' as [PROJSALESCURRENCYID]
			,0.00 as [PROJSALESPRICE]
			,' ' as [PROJSALESUNITID]
			,' ' as [PROJTAXGROUPID]
			,' ' as [PROJTAXITEMGROUPID]
			,' ' as [PPROJTRANSID]
			,' ' as [PSARETAINSCHEDULEID]
			,0.00 as [PSATOTALRETAINAMOUNT]
			,' ' as [PURCHASETYPE]
			,0.00 as [PURCHMARKUP]
			,0 as [PURCHRECEIVEDNOW]
			,' ' as [PURCHREQID]
			,' ' as [PURCHSTATUS]
			,' ' as [PURCHUNIT]
			,0 as [RBOPACKAGELINENUM]
			,0 as [REMAINDER]
			,' ' as [REQATTENTION]
			,' ' as [REQPLANIDSCHED]
			,' ' as [REQPOID]
			,0 as [RETAILLINENUMEX1]
			,' ' as [RETAILPACKAGEID]
			,0.00 as [RETAILTEMPVALUEEX2]
			,' ' as [RETURNACTIONID]
			,' ' as [RETURNDISPOSITIONCODEID]
			,' ' as [RETURNSTATUS]
			,' ' as [ROWID]
			,' ' as [SCRAP]
			,' ' as [SEARCHKEY]
			,' ' as [SERVICEADDRESS]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [SERVICEDATE]
			,convert(dateTime,'1901-01-01 00:00:00.000')as [SHIPPINGDATECONFIRMED]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [SHIPPINGDATEREQUESTED]
			,' ' as [SKIPUPDATE]
			,0.00 as [STATISTICVALUE_LT]
			,' ' as [STATPROCID]
			,' ' as [STATTRIANGULARDEAL]
			,' ' as [STOCKEDPRODUCT]
			,' ' as [SYSTEMENTRYSOURCE]
			,0.00 as [TAX1099AMOUNT]
			,' '  as [TAX1099BOX]
			,' ' as [TAX1099STATE]
			,' ' as [TAXGROUP]
			,0.00 as [TAX1099STATEAMOUNT]
			,' ' as [TAX1099TYPE]
			,' ' as [TAXAUTOGENERATED]
			,' ' as [TAXITEMGROUP]
			, ' ' as [TAXSERVICECODE_BR]
			,0.00 as [TAXWITHHOLDBASECUR_TH]
			,0.00 as [TAXWITHHOLDGROUP_TH]
			,0.00 as [TAXWITHHOLDITEMGROUP_TH]
			,' ' as [TRANSACTIONCODE]
			,' ' as [TRANSFERSTATUS]
			,' ' as [TRANSPORT]
			,0.00 as [UNDERDELIVERYPCT]
			,' ' as [VARIANTID]
			,' ' as [VENDGROUP]
			,' ' as [WMSLOCATIONID]
			,' ' as [WMSPALLETID]
			,' ' as [WORKFLOWSTATE]
			,' ' as [RECVERSION]
			,0   as [PARTITION]



FROM OPENQUERY  
      (KBM400MFG,

	 ' SELECT    
				 b.PELINE 
	            ,b.PENO	            
				,b.PECSDT
				,c.POCATG
				,b.PEPN
				,c.POCO
				,d.POCOM1
				,c.POCOST
				,c.POQTY
				,c.POVEND
				
				



	 FROM  [KBM400MFG].[FLPODET] B  
	      LEFT OUTER JOIN [KBM400MFG].[FLPOSUM] c ON  B.[PECO] = C.[POCO] AND B.[PENO] = C.[PONO] 
	      LEFT OUTER JOIN [KBM400MFG].[FLSTCMST] D ON  B.[PECO] = D.[POCO] AND B.[PENO] = D.[PONO] 
		  inner join [VD_GPL].[VKITMSTR]  E on  E.[IMPN] = A.[PEPN] and E.[IMCO] = A.[PECO]
             where  [IMCO] in(01,03,05,07)
 
  '
   ) AS A 

Open in new window

0
Comment
Question by:Leogal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40354623
you have to move that join to the outside:
USE DmStagingVD

SELECT    TOP  150
			 ISNULL([PELINE],' ')  as [Line Number]
			,ISNULL([PENO],' ')  as [Purchase Order] 
			,ISNULL([PECSDT],'01-01-1900 00:00:00') as [Delivery Date] 
			,ISNULL([POCATG], ' ')as [External] 
			,ISNULL([PEPN],' ') as [Item Number] 
			,ISNULL([POCOM1],' ') AS NAME 
			,ISNULL([POCOST],00.00)  as [Unit Price] 
			,ISNULL([POQTY],0)   as [Quantity]  
			,ISNULL([POVEND],99999999)  as [Vendor Account] 
			,1  as [RecId]
			,' ' as [ACCOUNTINGDISTRIBUTIONTEMPLATE]
			,' ' as [ActivityNumber]
			,' ' as [ASSETBOOKID]
			,' ' as [ASSETGROUP]
			,' ' as [ASSETID]
			,' ' as [BARCODE]	
			,' ' as [BARCODETYPE]
			,' ' as [BLOCKED]
			,' ' as [CASETAGGING]
			,' ' as [CFOPTABLE_BR_CFOPID]
			,' ' as [COMPLETE]
			,' ' as [CONFIGID]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [CONFIRMEDDLV]
			,0.00 as [CONFIRMEDTAXAMOUNT]
			,' ' as [CONFIRMEDTAXWRITECODE]
			,' ' as [COUNTYORIGDEST]
			,' ' as [COVREF]
			,' ' as [CREATEFIXEDASSET]
			,' ' as [CURRENCYCODE]
			,' ' as [CUSTOMERREF]
			,' ' as [CUSTPURCHASEORDERFORMNUM]
			,' ' as [DEFAULTDIMENSION]
			,' ' as [DEFINITIONGROUP]
			,' ' as [DELIVERYADDRESS]
			,' ' as [DELIVERYCITY]
			,' ' as [DELIVERYCOUNTRYREGIONID]
			,' ' as [DELIVERYCOUNTY]
			,' ' as [DELIVERYLOCATIONROLE]
			,' ' as [DELIVERYNAME]
			,' ' as [DELIVERYSTATE]
			,' ' as [DELIVERTYSTREET]
			,' ' as [DELIVERYTYPE]
			,' ' as [DELIVERYZIPCODE]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [DEPRECIATIONSTARTDATE]
			,0.00 as [DISCAMOUNT]
			,0.00 as [DISCPERCENT]
			,' '  as [EDITABLEINWORKFLOW]
			,' ' as [EXECUTIONID]
			,' ' as [GSTHSTTAXTYPE_CA]
			,' ' as [INTERCOMPANYINVENTTRANSID]
			,' ' as [INTERCOMPANYORIGIN]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [INTRASTATFULFILLMENTDATE_HU]
			,' ' as [INVENTBATCHID]
			,' ' as [INVENTCOLORID]
			,0.00 as [INVENTINVOICENOW]
			,' ' as [INVENTLOCATIONID]
			,0.00 as [INVENTRECEIVEDNOW]
			,' ' as [INVENTREFID]
			,' ' as [INVENTREFTRANSID]
			,' ' as [INVENTSERIALID]
			,' ' as [INVENTSITEID]
			,' ' as [INVENTSIZEID]
			,' ' as [INVENTSTYLEID]
			,' ' as [ISDELETED]
			,' ' as [ISFINALIZED]
			,' ' as [ISINVOICEMATCHED]
			,' ' as [ISMODIFIED]
			,' ' as [ISPWP]
			,' ' as [ISSELECTED]
			,' ' as [ITEMBOMID]
			,' ' as [ITEMPBAID]
			,' ' as [ITEMROUTEID]
			,' ' as [ITEMTAGGING]
			,' ' as [LEDGERDIMENSION]
			,0.00 as [LINEAMOUNT]
			,' ' as [LINEDELIVERYTYPE]
			,0.00 as [LINEDISC]
			,' ' as [LINEHEADER]
			,0.00 as [LINEPERCENT]
			,0.00 as [MARKUPTRANS_CALCULATEDAMOUNT]
			,' ' as [MARKUPTRANS_CURRENCYCODE]
			,' ' as [MARKUPTRANS_DOCUMENTSTATUS]
			,' ' as [MARKUPTRANS_INTERCOMPANYCOMPANYID]
			,' ' as [MARKUPTRANS_INTERCOMPANYINVOICEID]
			,' ' as [MARKUPTRANS_INTERCOMPANYMARKUPUSEVALUE]
			,0.00 as [MARKUPTRANS_INTERCOMPANYMARKUPVALUE]
			,' ' as [MARKUPTRANS_ISDELETED]
			,' ' as [MARKUPTRANS_ISMODIFIED]
			,' ' as [MARKUPTRANS_KEEP]
			,0 as [MARKUPTRANS_LINENUM]
			,' ' as [MARKUPTRANS_MARKUPCATEGORY]
			,' ' as [MARKUPTRANS_MARKUPCODE]
			,' ' as [MARKUPTRANS_MODULECATEGORY]
			,' ' as [MARKUPTRANS_MODULETYPE]
			,' ' as [MARKUPTRANS_ORIGTABLEID]
			,0.00 as [MARKUPTRANS_POSTED]
			,0.00 as [MARKUPTRANS_TAXAMOUNT]
			,' '  as [MARKUPTRANS_TAXAUTOGENERATED]
			,' ' as [MARKUPTRANS_TAXGROUP]
			,' ' as [MARKUPTRANS_TAXITEMGROUP]
			,' ' as [MARKUPTRANS_TAXWRITECODE]
			,convert(dateTime,'1901-01-01 00:00:00.000')as [MARKUPTRANS_TRANSDATE]
			,' ' as [MARKUPTRANS_TRANSTABLEID]
			,' ' as [MARKUPTRANS_TXT]
			,0.00 as [MARKUPTRANS_VALUE]
			,' ' as [MARKUPTRANS_VOUCHER]
			,' ' as [MATCHINGPOLICY]
			,0.00 as [MULTILNDISC]
			,0.00 as [MULTILNPERCENT]
			,' ' as [OPERATIONTYPE_MX]
			,0.00 as [OVERDELIVERYPCT]
			,' ' as [PALLETTAGGING]
			,' ' as [PDSCALCULATIONID]
			,0.00 as [PDSCWINVENTRECEIVEDNOW]
			,0 as [PDSCWQTY]
			,0.00 as [PDSCWREMAININVENTFINANCIAL]
			,0  as [PDSCWREMAININVENTPHYSICAL]
			,' ' as [PORT]
			,0.00 as [PRICEUNIT]
			,' ' as [PROJCATEGORYID]
			,' ' as [PROJID]
			,' ' as [PROJLINEPROPERTYID]
			,' ' as [PROJSALESCURRENCYID]
			,0.00 as [PROJSALESPRICE]
			,' ' as [PROJSALESUNITID]
			,' ' as [PROJTAXGROUPID]
			,' ' as [PROJTAXITEMGROUPID]
			,' ' as [PPROJTRANSID]
			,' ' as [PSARETAINSCHEDULEID]
			,0.00 as [PSATOTALRETAINAMOUNT]
			,' ' as [PURCHASETYPE]
			,0.00 as [PURCHMARKUP]
			,0 as [PURCHRECEIVEDNOW]
			,' ' as [PURCHREQID]
			,' ' as [PURCHSTATUS]
			,' ' as [PURCHUNIT]
			,0 as [RBOPACKAGELINENUM]
			,0 as [REMAINDER]
			,' ' as [REQATTENTION]
			,' ' as [REQPLANIDSCHED]
			,' ' as [REQPOID]
			,0 as [RETAILLINENUMEX1]
			,' ' as [RETAILPACKAGEID]
			,0.00 as [RETAILTEMPVALUEEX2]
			,' ' as [RETURNACTIONID]
			,' ' as [RETURNDISPOSITIONCODEID]
			,' ' as [RETURNSTATUS]
			,' ' as [ROWID]
			,' ' as [SCRAP]
			,' ' as [SEARCHKEY]
			,' ' as [SERVICEADDRESS]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [SERVICEDATE]
			,convert(dateTime,'1901-01-01 00:00:00.000')as [SHIPPINGDATECONFIRMED]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [SHIPPINGDATEREQUESTED]
			,' ' as [SKIPUPDATE]
			,0.00 as [STATISTICVALUE_LT]
			,' ' as [STATPROCID]
			,' ' as [STATTRIANGULARDEAL]
			,' ' as [STOCKEDPRODUCT]
			,' ' as [SYSTEMENTRYSOURCE]
			,0.00 as [TAX1099AMOUNT]
			,' '  as [TAX1099BOX]
			,' ' as [TAX1099STATE]
			,' ' as [TAXGROUP]
			,0.00 as [TAX1099STATEAMOUNT]
			,' ' as [TAX1099TYPE]
			,' ' as [TAXAUTOGENERATED]
			,' ' as [TAXITEMGROUP]
			, ' ' as [TAXSERVICECODE_BR]
			,0.00 as [TAXWITHHOLDBASECUR_TH]
			,0.00 as [TAXWITHHOLDGROUP_TH]
			,0.00 as [TAXWITHHOLDITEMGROUP_TH]
			,' ' as [TRANSACTIONCODE]
			,' ' as [TRANSFERSTATUS]
			,' ' as [TRANSPORT]
			,0.00 as [UNDERDELIVERYPCT]
			,' ' as [VARIANTID]
			,' ' as [VENDGROUP]
			,' ' as [WMSLOCATIONID]
			,' ' as [WMSPALLETID]
			,' ' as [WORKFLOWSTATE]
			,' ' as [RECVERSION]
			,0   as [PARTITION]



FROM OPENQUERY  
      (KBM400MFG,

	 ' SELECT    
				 b.PELINE 
	            ,b.PENO	            
				,b.PECSDT
				,c.POCATG
				,b.PEPN
				,c.POCO
				,d.POCOM1
				,c.POCOST
				,c.POQTY
				,c.POVEND
				
				



	 FROM  [KBM400MFG].[FLPODET] B  
	      LEFT OUTER JOIN [KBM400MFG].[FLPOSUM] c ON  B.[PECO] = C.[POCO] AND B.[PENO] = C.[PONO] 
	      LEFT OUTER JOIN [KBM400MFG].[FLSTCMST] D ON  B.[PECO] = D.[POCO] AND B.[PENO] = D.[PONO] 
             where  [IMCO] in(01,03,05,07)
 
  '
   ) AS A 
		  inner join [VD_GPL].[VKITMSTR]  E on  E.[IMPN] = A.[PEPN] and E.[IMCO] = A.[PECO]
                                  

Open in new window

0
 

Author Comment

by:Leogal
ID: 40354654
@Guy Hengel, now I am receiving this error:

Msg 208, Level 16, State 1, Line 4
Invalid object name 'VD_GPL.VKITMSTR'.
0
 

Author Comment

by:Leogal
ID: 40354670
I modified the code to a fully qualified file for the outside join and now I am receiving this error:

OLE DB provider "DB2OLEDB" for linked server "KBM400MFG" returned message "Column *N not in table *N in *N. SQLSTATE: 42703, SQLCODE: -205".
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query " SELECT    
                         b.PELINE
                  ,b.PENO                 
                        ,b.PECSDT
                        ,c.POCATG
                        ,b.PEPN
                        ,c.POCO
                        ,d.POCOM1
                        ,c.POCOST
                        ,c.POQTY
                        ,c.POVEND
                        ,c.PECO
                        ,c.PSPMRN

       FROM  [KBM400MFG].[FLPODET] B  
            LEFT OUTER JOIN [KBM400MFG].[FLPOSUM] c ON  B.[PECO] = C.[POCO] AND B.[PENO] = C.[PONO]
            LEFT OUTER JOIN [KBM400MFG].[FLSTCMST] D ON  B.[PECO] = D.[POCO] AND B.[PENO] = D.[PONO]  
               
  " for execution against OLE DB provider "DB2OLEDB" for linked server "KBM400MFG".





Here is the code as it sits now:
-- DATABASE
USE DmStagingVD

SELECT    TOP  150
			 ISNULL([PELINE],' ')  as [Line Number]
			,ISNULL([PENO],' ')  as [Purchase Order] 
			,ISNULL([PECSDT],'01-01-1900 00:00:00') as [Delivery Date] 
			,ISNULL([POCATG], ' ')as [External] 
			,ISNULL([PEPN],' ') as [Item Number] 
			,ISNULL([POCOM1],' ') AS NAME 
			,ISNULL([POCOST],00.00)  as [Unit Price] 
			,ISNULL([POQTY],0)   as [Quantity]  
			,ISNULL([POVEND],99999999)  as [Vendor Account] 
			,1  as [RecId]
			,' ' as [ACCOUNTINGDISTRIBUTIONTEMPLATE]
			,' ' as [ActivityNumber]
			,' ' as [ASSETBOOKID]
			,' ' as [ASSETGROUP]
			,' ' as [ASSETID]
			,' ' as [BARCODE]	
			,' ' as [BARCODETYPE]
			,' ' as [BLOCKED]
			,' ' as [CASETAGGING]
			,' ' as [CFOPTABLE_BR_CFOPID]
			,' ' as [COMPLETE]
			,' ' as [CONFIGID]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [CONFIRMEDDLV]
			,0.00 as [CONFIRMEDTAXAMOUNT]
			,' ' as [CONFIRMEDTAXWRITECODE]
			,' ' as [COUNTYORIGDEST]
			,' ' as [COVREF]
			,' ' as [CREATEFIXEDASSET]
			,' ' as [CURRENCYCODE]
			,' ' as [CUSTOMERREF]
			,' ' as [CUSTPURCHASEORDERFORMNUM]
			,' ' as [DEFAULTDIMENSION]
			,' ' as [DEFINITIONGROUP]
			,' ' as [DELIVERYADDRESS]
			,' ' as [DELIVERYCITY]
			,' ' as [DELIVERYCOUNTRYREGIONID]
			,' ' as [DELIVERYCOUNTY]
			,' ' as [DELIVERYLOCATIONROLE]
			,' ' as [DELIVERYNAME]
			,' ' as [DELIVERYSTATE]
			,' ' as [DELIVERTYSTREET]
			,' ' as [DELIVERYTYPE]
			,' ' as [DELIVERYZIPCODE]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [DEPRECIATIONSTARTDATE]
			,0.00 as [DISCAMOUNT]
			,0.00 as [DISCPERCENT]
			,' '  as [EDITABLEINWORKFLOW]
			,' ' as [EXECUTIONID]
			,' ' as [GSTHSTTAXTYPE_CA]
			,' ' as [INTERCOMPANYINVENTTRANSID]
			,' ' as [INTERCOMPANYORIGIN]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [INTRASTATFULFILLMENTDATE_HU]
			,' ' as [INVENTBATCHID]
			,' ' as [INVENTCOLORID]
			,0.00 as [INVENTINVOICENOW]
			,' ' as [INVENTLOCATIONID]
			,0.00 as [INVENTRECEIVEDNOW]
			,' ' as [INVENTREFID]
			,' ' as [INVENTREFTRANSID]
			,' ' as [INVENTSERIALID]
			,' ' as [INVENTSITEID]
			,' ' as [INVENTSIZEID]
			,' ' as [INVENTSTYLEID]
			,' ' as [ISDELETED]
			,' ' as [ISFINALIZED]
			,' ' as [ISINVOICEMATCHED]
			,' ' as [ISMODIFIED]
			,' ' as [ISPWP]
			,' ' as [ISSELECTED]
			,' ' as [ITEMBOMID]
			,' ' as [ITEMPBAID]
			,' ' as [ITEMROUTEID]
			,' ' as [ITEMTAGGING]
			,' ' as [LEDGERDIMENSION]
			,0.00 as [LINEAMOUNT]
			,' ' as [LINEDELIVERYTYPE]
			,0.00 as [LINEDISC]
			,' ' as [LINEHEADER]
			,0.00 as [LINEPERCENT]
			,0.00 as [MARKUPTRANS_CALCULATEDAMOUNT]
			,' ' as [MARKUPTRANS_CURRENCYCODE]
			,' ' as [MARKUPTRANS_DOCUMENTSTATUS]
			,' ' as [MARKUPTRANS_INTERCOMPANYCOMPANYID]
			,' ' as [MARKUPTRANS_INTERCOMPANYINVOICEID]
			,' ' as [MARKUPTRANS_INTERCOMPANYMARKUPUSEVALUE]
			,0.00 as [MARKUPTRANS_INTERCOMPANYMARKUPVALUE]
			,' ' as [MARKUPTRANS_ISDELETED]
			,' ' as [MARKUPTRANS_ISMODIFIED]
			,' ' as [MARKUPTRANS_KEEP]
			,0 as [MARKUPTRANS_LINENUM]
			,' ' as [MARKUPTRANS_MARKUPCATEGORY]
			,' ' as [MARKUPTRANS_MARKUPCODE]
			,' ' as [MARKUPTRANS_MODULECATEGORY]
			,' ' as [MARKUPTRANS_MODULETYPE]
			,' ' as [MARKUPTRANS_ORIGTABLEID]
			,0.00 as [MARKUPTRANS_POSTED]
			,0.00 as [MARKUPTRANS_TAXAMOUNT]
			,' '  as [MARKUPTRANS_TAXAUTOGENERATED]
			,' ' as [MARKUPTRANS_TAXGROUP]
			,' ' as [MARKUPTRANS_TAXITEMGROUP]
			,' ' as [MARKUPTRANS_TAXWRITECODE]
			,convert(dateTime,'1901-01-01 00:00:00.000')as [MARKUPTRANS_TRANSDATE]
			,' ' as [MARKUPTRANS_TRANSTABLEID]
			,' ' as [MARKUPTRANS_TXT]
			,0.00 as [MARKUPTRANS_VALUE]
			,' ' as [MARKUPTRANS_VOUCHER]
			,' ' as [MATCHINGPOLICY]
			,0.00 as [MULTILNDISC]
			,0.00 as [MULTILNPERCENT]
			,' ' as [OPERATIONTYPE_MX]
			,0.00 as [OVERDELIVERYPCT]
			,' ' as [PALLETTAGGING]
			,' ' as [PDSCALCULATIONID]
			,0.00 as [PDSCWINVENTRECEIVEDNOW]
			,0 as [PDSCWQTY]
			,0.00 as [PDSCWREMAININVENTFINANCIAL]
			,0  as [PDSCWREMAININVENTPHYSICAL]
			,' ' as [PORT]
			,0.00 as [PRICEUNIT]
			,' ' as [PROJCATEGORYID]
			,' ' as [PROJID]
			,' ' as [PROJLINEPROPERTYID]
			,' ' as [PROJSALESCURRENCYID]
			,0.00 as [PROJSALESPRICE]
			,' ' as [PROJSALESUNITID]
			,' ' as [PROJTAXGROUPID]
			,' ' as [PROJTAXITEMGROUPID]
			,' ' as [PPROJTRANSID]
			,' ' as [PSARETAINSCHEDULEID]
			,0.00 as [PSATOTALRETAINAMOUNT]
			,' ' as [PURCHASETYPE]
			,0.00 as [PURCHMARKUP]
			,0 as [PURCHRECEIVEDNOW]
			,' ' as [PURCHREQID]
			,' ' as [PURCHSTATUS]
			,' ' as [PURCHUNIT]
			,0 as [RBOPACKAGELINENUM]
			,0 as [REMAINDER]
			,' ' as [REQATTENTION]
			,' ' as [REQPLANIDSCHED]
			,' ' as [REQPOID]
			,0 as [RETAILLINENUMEX1]
			,' ' as [RETAILPACKAGEID]
			,0.00 as [RETAILTEMPVALUEEX2]
			,' ' as [RETURNACTIONID]
			,' ' as [RETURNDISPOSITIONCODEID]
			,' ' as [RETURNSTATUS]
			,' ' as [ROWID]
			,' ' as [SCRAP]
			,' ' as [SEARCHKEY]
			,' ' as [SERVICEADDRESS]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [SERVICEDATE]
			,convert(dateTime,'1901-01-01 00:00:00.000')as [SHIPPINGDATECONFIRMED]
			,convert(dateTime,'1901-01-01 00:00:00.000') as [SHIPPINGDATEREQUESTED]
			,' ' as [SKIPUPDATE]
			,0.00 as [STATISTICVALUE_LT]
			,' ' as [STATPROCID]
			,' ' as [STATTRIANGULARDEAL]
			,' ' as [STOCKEDPRODUCT]
			,' ' as [SYSTEMENTRYSOURCE]
			,0.00 as [TAX1099AMOUNT]
			,' '  as [TAX1099BOX]
			,' ' as [TAX1099STATE]
			,' ' as [TAXGROUP]
			,0.00 as [TAX1099STATEAMOUNT]
			,' ' as [TAX1099TYPE]
			,' ' as [TAXAUTOGENERATED]
			,' ' as [TAXITEMGROUP]
			, ' ' as [TAXSERVICECODE_BR]
			,0.00 as [TAXWITHHOLDBASECUR_TH]
			,0.00 as [TAXWITHHOLDGROUP_TH]
			,0.00 as [TAXWITHHOLDITEMGROUP_TH]
			,' ' as [TRANSACTIONCODE]
			,' ' as [TRANSFERSTATUS]
			,' ' as [TRANSPORT]
			,0.00 as [UNDERDELIVERYPCT]
			,' ' as [VARIANTID]
			,' ' as [VENDGROUP]
			,' ' as [WMSLOCATIONID]
			,' ' as [WMSPALLETID]
			,' ' as [WORKFLOWSTATE]
			,' ' as [RECVERSION]
			,0   as [PARTITION]



FROM OPENQUERY  
      (KBM400MFG,

	 ' SELECT    
				 b.PELINE 
	            ,b.PENO	            
				,b.PECSDT
				,c.POCATG
				,b.PEPN
				,c.POCO
				,d.POCOM1
				,c.POCOST
				,c.POQTY
				,c.POVEND
				,c.PECO
				,c.PSPMRN

	 FROM  [KBM400MFG].[FLPODET] B  
	      LEFT OUTER JOIN [KBM400MFG].[FLPOSUM] c ON  B.[PECO] = C.[POCO] AND B.[PENO] = C.[PONO] 
	      LEFT OUTER JOIN [KBM400MFG].[FLSTCMST] D ON  B.[PECO] = D.[POCO] AND B.[PENO] = D.[PONO]  
		   
  '
   ) AS A 
         inner join [VD_GPL].[S1022466].[VD_GPL].[VKITMSTR]  E on  E.[IMPN] = A.[PEPN] and E.[IMCO] = A.[PECO]
           where  e.[IMCO] in(01,03,05,07)

	

Open in new window

0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:Leogal
ID: 40354927
i resolved the error now the program works just fine. Thanks!
0
 

Author Comment

by:Leogal
ID: 40357603
I've requested that this question be closed as follows:

Accepted answer: 0 points for Leogal's comment #a40354927

for the following reason:

I was able to figure out my coding issues and now am closing the ticket
0
 

Author Closing Comment

by:Leogal
ID: 40357604
I truly appreciate the expert help!
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
OfficeMate Freezes on login or does not load after login credentials are input.
In this Micro Tutorial viewers will learn how to use Windows Server Backup to create full image of their system. Tutorial shows how to install Windows Server Backup Feature on Windows 2012R2 and how to configure scheduled Bare Metal Recovery backup.…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question