issue joining on Temp Table

Question

issue joining on Temp Table

jeffshelix

SSCrazy

Points: 2257
More actions
January 3, 2018 at 8:43 am

#327024

crashes at
/* Joining on Prod_Class */ section.
Issue has something to do with joining on a created temp table.
thoughts?
SELECT * INTO #TEMP1 From
(Select
L.PART_NUM
,Like_PD_ID
,PD_ID
from
ADHOC.ATS_ESH H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.TRANS_SK
Inner Join [TSC].[EMS_PDLINK] P
on P.USER_VARCHAR_4 = L.Part_Num
WHERE
H.Importer = 'Ford'
and H.Entry_Summary_Date > '12/1/2017'
AND L.HTS_Num NOT LIKE N'98%'
AND L.Part_Num NOT LIKE N'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Like 'Z'
) as Service
SELECT DISTINCT
H.CUSTOMS_ENTRY_NUM as [Entry Num]
,L.Entry_Line_ID as Line
,H.[Broker_Invoice]
,H.Entry_Type
,L.PART_NUM AS [ESL Part]
,L.HTS_NUM AS [ESL HTS]
,CAST(H.ENTRY_DATE AS DATE) AS [Entry_Date]
,C.[CLASS_VALUE_1] as [P/C_HTS]
,C.[CLASSIFIED_DATE] as [P/C Class Date]
,P.Status as [Work Queue Status]
--,T.TSI_PROD_ID as [TA Prod ID]
--,T.HTS_NUM as [TA HTS]
--,ISNULL(T.UPDATE_DATE,T.INSERT_DATE) as [TA Update]
,Z.[HTS_NUMBER] as [Zats HTS]
,Z.[INSERT_DATE] as [Zats Date]
--,z.composite_part as [Zats Part]
--,B.[CLASS_VALUE_1] as [Hist HTS]
--,B.[Classified_Date] as [Hist Date]
----,B.[PROD_ID] as Hist_Prod_ID
--,CASE
-- WHEN Substring(L.Part_Num,4,1) = 'Z'
-- THEN 'Service'
-- END as Service
,A.PD_id as [PDLink Serv Part]
,A.[LIKE_PD_ID] as [PDLink Eng Part]
,[IRQ].[TCM_INRQ].[INFO_REQUEST_ID]
,[IRQ].[TCM_INRQ].ENTRY_NUM as [IRQ Entry]
,[IRQ].[TCM_INRQ_DTL].[REQUEST_TYPE]
,replace(replace([Request_NOTE], char(10), ''), char(13), '') as Request_Note
,replace(replace([Response_NOTE], char(10), ''), char(13), '') as Response_Note
,replace(replace([INTERNAL_NOTE], char(10), ''), char(13), '') as Internal_Note
,CASE
When A.[LIKE_PD_ID] is not Null
THEN 'Possible PDLINK Issue'
WHEN C.[CLASS_VALUE_1] IS NULL
THEN 'Part Not in Classifier'
WHEN Z.[HTS_NUMBER] IS NULL
THEN 'Part Not on Broker Feed'
WHEN Z.[HTS_NUMBER] <> C.[CLASS_VALUE_1]
THEN 'Prod Class not same as Broker Feed'
  END as Comment
,CASE
WHEN L.HTS_NUM = Z.[HTS_NUMBER]
THEN 'Broker Used Broker Feed'
   END as Comment_2
/* Pulls Entry Data */
FROM
ADHOC.ATS_ESH H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.TRANS_SK
/* Joining on Prod_Class */
left JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON #TEMP1.PD_ID = C.[PROD_ID] AND C.[CTRY_CODE] = 'US' AND C.CLASS_TYPE = 'HS' and C.BUS_UNIT = 'FORD' AND C.[CLASS_DETAIL_LEVEL] = 'Import_Export'
--/* Joining on Prod_Class History */
--LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS_HIST] B
--ON L.[PART_NUM] = Replace(B.[PROD_ID],'-','') and B.[CTRY_CODE] = 'US' and B.CLASS_TYPE = 'HS' and B.[BUS_UNIT] = 'FORD' AND B.[CLASS_DETAIL_LEVEL] = 'Import_Export'
--and B.CLASSIFIED_DATE > Getdate() - 365
/* Joining on Work Queue */
LEFT JOIN [TSC].[EMS_TVC_WRK_QUEUE] P
ON L.[PART_NUM] = Replace(P.[PROD_ID],'-','') and P.CLASS_TYPE = 'HS' and P.[BUS_UNIT] = 'FORD'
--/* Joining on TSI (TA)*/
--LEFT JOIN
--TSI.TSI_PD_HS T
--ON L.[PART_NUM] = Replace(T.[TSI_PROD_ID],'-','')
--and T.INSERT_DATE > Getdate() -365
/* Joining on PD Link to pull Engineering Part */
LEFT JOIN [TSC].[EMS_PDLINK] A
ON L.[PART_NUM] = Replace(A.Like_PD_ID,'-','') and A.[SUB_ORG] = 'FORD'
/* Joining on ZATS Broker Feed */
LEFT JOIN
[TSI].[ZATS_BROKER_FEED] Z
on L.Part_Num = Z.Composite_part and Z.[SUB_ORG] = 'FORD' and Z.Insert_date > Getdate() - 365
/* Joining on IRQ */
Left Join [IRQ].[TCM_INRQ]
on H.[Compressed Entry_Num] = [IRQ].[TCM_INRQ].Entry_Num
Inner Join [IRQ].[TCM_INRQ_DTL]
ON [IRQ].[TCM_INRQ].[INRQ_SK] = [IRQ].[TCM_INRQ_DTL].[INRQ_SK]
Where
H.Importer = N'FORD'
AND H.Entry_Summary_Date >= '11/1/2017'
AND H.Entry_Summary_Date < '12/1/2017'
AND L.HTS_Num NOT LIKE N'98%'
AND L.Part_Num NOT LIKE N'NPN'
AND L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '')
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Like 'Z'
ORDER BY
L.PART_NUM,
CAST(H.ENTRY_DATE AS DATE),
C.[CLASSIFIED_DATE],
Z.[INSERT_DATE]
February 17, 2018 at 4:10 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

Well it doesn't look like you actually joined the temp table into the query anywhere, you're just using it in the join criteria.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Well, you're referencing the temp table without joining to it first.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jeffshelix SSCrazy Points: 2257 More actions · Answer 3

Thanks
starting with...
/* Joining on Prod_Class */
left JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON #TEMP1.PD_ID = C.[PROD_ID]

I need the value (Class_value_1) from Prod_Class where #TEMP1.PD_ID = C.[PROD_ID]

I appreciate the reply.

jeffshelix SSCrazy Points: 2257 More actions · Answer 4

"Well, you're referencing the temp table without joining to it first. "

I do not understand what this means or how to fix it . . . .thanks

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 5

jeffshelix - Wednesday, January 3, 2018 9:03 AM
"Well, you're referencing the temp table without joining to it first. "
I do not understand what this means or how to fix it . . . .thanks

It means you have to treat the temp table the same way as any other table in the query, you can't just include it in the join criteria without actually well joining to the table first in some way.

jeffshelix SSCrazy Points: 2257 More actions · Answer 6

ISnt this code joining the tables?

left JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON #TEMP1.PD_ID = C.[PROD_ID]

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 7

jeffshelix - Wednesday, January 3, 2018 9:10 AM
ISnt this code joining the tables?
left JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON #TEMP1.PD_ID = C.[PROD_ID]

Nope, that is joining to [TSC].[EMS_TVC_PROD_CLASS] and referencing #TEMP1 in the join criteria but you haven't actually joined to #TEMP1 which is why it's complaining.

jeffshelix SSCrazy Points: 2257 More actions · Answer 8

Ah, the light is flickering . . . thanks, i will try that . . . .

Thanks for your patience and quick reply!!!!!!!!!!!!!!!!

Jeff

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

JOIN #Temp ON ...
Then you can reference it further down. Same as any other table.

You can't say
FROM Customers inner join OrderDetails on Customers.ID = Orders.CustomerID, same with temp tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jeffshelix SSCrazy Points: 2257 More actions · Answer 10

Still stuck here . . .

I’ve simplified my issue down to this:
With or without using a temp table...............

Table A
            Service_Part
Table B
            Service_Part
            Production_Part
Table C
            Cost of Production Part

I am stuck on how to Join(s) Table A to Table C to get the cost of the Service Part in Table A from Table C.

Thanks

GaryV Hall of Fame Points: 3307 More actions · Answer 11

FROM TableA as a JOIN TableB as b ON a.Service_Part = b.Service_Part JOIN TableC as c ON b.Production_Part = c.Production_Part

I sure hope TableC has a Production_Part column. Or has something else that can be used to link the records correctly.