I have a table-valued function where there are multiple tables being declared and finally joined together to create the final table with data from each table. I had a problem with duplicates so I created a new table @ORDERHEADER so select the distinct values and then insert it into the the table and eventually the final table. When I run the select distinct by itself, it populates correctly but when I run it with the insert nothing populates.
Below is the code I am currently working with (I kept the commented out lines for you to see what I have tried — above the first “–LEFT OUTER JOIN” is my working code).
DECLARE @ORDERHEADER TABLE( [DIV_CD] [varchar](8) NULL, [SALES_ORD_NR] [varchar](8) NOT NULL, [CUST_EDP_ID] [decimal](28,0) NOT NULL, [OFFER_CD] [varchar] (8) NULL) INSERT INTO @ORDERHEADER SELECT DISTINCT(SV_OR_1000_ORDER_HEADER.SALES_ORD_NR), SV_OR_1000_ORDER_HEADER.DIV_CD, SV_OR_1000_ORDER_HEADER.CUST_EDP_ID, SV_OR_1000_ORDER_HEADER.OFFER_CD from SV_OR_1000_ORDER_HEADER --join @RETURNS r on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR = r.SALES_ORD_NR --LEFT OUTER JOIN OPENQUERY(CH1KNOWAPP,'SELECT DISTINCT SALES_ORD_NR FROM EmailVPN..EB_RETURN_CONFIRMATION_NEW WITH(NOLOCK) WHERE ORD_ITM_STA_LGCY_CD IN (''R'',''E'',''4'')') as RET_EMAILS on RET_EMAILS.SALES_ORD_NR COLLATE SQL_Latin1_General_CP1_CI_AS = SV_OR_1000_ORDER_HEADER.SALES_ORD_NR --join @RETURNS r on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR = r.SALES_ORD_NR --join SV_MACORD_RETURNS on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR = SV_MACORD_RETURNS.SALES_ORD_NR -- where exists (SELECT DISTINCT * from SV_OR_1000_ORDER_HEADER)-- exists (select * from SV_OR_1000_ORDER_HEADER join SV_MACORD_RETURNS on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR = SV_MACORD_RETURNS.SALES_ORD_NR) --where SV_OR_1000_ORDER_HEADER.SALES_ORD_NR is not null --where SV_OR_1000_ORDER_HEADER.OFFER_CD not in ('36') where SV_OR_1000_ORDER_HEADER.SALES_ORD_NR = 'W3151536' --group by SV_MACORD_RETURNS.SALES_ORD_NR, SV_MACORD_RETURNS.DIVISION, SV_OR_1000_ORDER_HEADER.CUST_EDP_ID, SV_MACORD_RETURNS.OFFER_CD --having COUNT(SV_OR_1000_ORDER_HEADER.SRC_SYS_TRANS_DT) < 2