Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TSQL Column Qualifier or table undefined error

Posted on 2014-10-01
6
Medium Priority
?
178 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Google Certified Professional - Cloud Architect

This course (1 of 3) is designed to help students who are interested in Google Cloud Platform (GCP) to become familiar with the platform, navigate the console and learn its capabilities. It will also prepare students for the Google Cloud Architect certification exam.

Question has a verified solution.

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

What to do when Windows Update is not working correctly? What tools can I use to detect the cause of the malfunction problem? What does this numeric error code mean? These and other questions that you have been asking in the past are answered here (…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
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 …

705 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