query just runs; possible join issue?

  • This report used to run until i added the CTE to pull MAX Created date from the Broker Feed table.
    Now it just runs . . . Can anyone see an issue?  
    thanks 

    ;WITH CTE_1 AS
    (
    SELECT
    Z.[COMPOSITE_PART]
    ,Z.HTS_NUMBER
    ,MAX(Z.CREATED_DATE) AS MAX_DATE

    FROM [TSI].[ZATS_BROKER_FEED] Z
    INNER JOIN ADHOC.ATS_ESL L
    ON L.PART_NUM = Z.COMPOSITE_PART
        AND Z.CREATED_DATE <= L.ENTRY_DATE

    WHERE
    Z.SUB_ORG='FORD'
    GROUP BY
    Z.COMPOSITE_PART
    ,Z.HTS_NUMBER
    )

    SELECT    
    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(L.ENTRY_DATE AS DATE) AS [Entry_Date]
    ,C.[CLASS_VALUE_1] as [P/C_HTS]
    ,C.[CLASSIFIED_DATE] as [P/C Class Date]
    ,CTE_1.HTS_NUMBER
    ,CTE_1.MAX_DATE

    FROM
    ADHOC.ATS_ESH H
    INNER JOIN ADHOC.ATS_ESL L
    ON H.TRANS_SK = L.TRANS_SK

    /* Joining on ZATS */
    LEFT join CTE_1
    on L.Part_Num = CTE_1.COMPOSITE_PART

    /* Joining on Prod_Class */
    LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
    ON L.[PART_NUM] = Replace(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'

    WHERE (H.Importer = 'FORD'
    AND H.Entry_Summary_Date >= GetDate () - 10
    AND L.HTS_Num NOT LIKE '98%'
    AND L.Part_Num <> 'NPN'
    AND H.Com_Desc_Code = 'Parts'
    AND Substring(L.Part_Num,4,1) <> 'Z'
    AND L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], '')) --ENTRY <> PROD_CLASS

  • The problem is in the CTE, but i dont see it . . . 

    SELECT
    Z.[COMPOSITE_PART]
    ,Z.HTS_NUMBER
    ,MAX(Z.CREATED_DATE) AS MAX_DATE
    FROM [TSI].[ZATS_BROKER_FEED] Z
    INNER JOIN ADHOC.ATS_ESL L
    ON L.PART_NUM = Z.COMPOSITE_PART
        AND Z.CREATED_DATE <= L.ENTRY_DATE
    WHERE
    Z.SUB_ORG='FORD'
    GROUP BY
    Z.COMPOSITE_PART
    ,Z.HTS_NUMBER

  • The CTE runs on its own, and the rest of the SELECT runs its own, FAST.
    But when i JOIN them, it runs forever . . 

    I hope that helps

  • ok, maybe this is a "correlated subquery" issue???  i dont know

    thanks

  • jeffshelix - Saturday, March 3, 2018 9:37 PM

    ok, maybe this is a "correlated subquery" issue???  i dont know

    thanks

    Can you post the execution plan for the query please?
    😎

  • Eirikur Eiriksson - Sunday, March 4, 2018 12:31 AM

    jeffshelix - Saturday, March 3, 2018 9:37 PM

    ok, maybe this is a "correlated subquery" issue???  i dont know

    thanks

    Can you post the execution plan for the query please?
    😎

    Also, try this and post up whether or not it works, and the execution plan (as a .sqlplan attachment). Thanks.

    SELECT   
     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(L.ENTRY_DATE AS DATE) AS [Entry_Date]
     ,C.[CLASS_VALUE_1] as [P/C_HTS]
     ,C.[CLASSIFIED_DATE] as [P/C Class Date]
     ,CTE_1.HTS_NUMBER
     ,CTE_1.MAX_DATE

    FROM ADHOC.ATS_ESH H
    INNER JOIN ADHOC.ATS_ESL L
     ON  H.TRANS_SK =  L.TRANS_SK

    /* Joining on ZATS */
    --LEFT join CTE_1
    -- on L.Part_Num = CTE_1.COMPOSITE_PART

    OUTER APPLY (
     SELECT
      Z.HTS_NUMBER,
      MAX(Z.CREATED_DATE) AS MAX_DATE
     FROM [TSI].[ZATS_BROKER_FEED] Z
     WHERE Z.SUB_ORG = 'FORD'
      AND Z.COMPOSITE_PART = L.Part_Num
      AND Z.CREATED_DATE <= L.ENTRY_DATE
     GROUP BY Z.HTS_NUMBER
    ) CTE_1

    /* Joining on Prod_Class */
    LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
    ON  L.[PART_NUM] = Replace(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'

    WHERE (H.Importer = 'FORD'
    AND H.Entry_Summary_Date >= GetDate () - 10
    AND L.HTS_Num NOT LIKE '98%'
    AND L.Part_Num <> 'NPN'
    AND H.Com_Desc_Code = 'Parts'
    AND Substring(L.Part_Num,4,1) <> 'Z'
    AND L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], ''))  --ENTRY <> PROD_CLASS


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • thanks for looking at this.... the suggested query just ran also.  Attached is the execution plan for my query.

    thanks  . . .

  • jeffshelix - Sunday, March 4, 2018 7:59 AM

    thanks for looking at this.... the suggested query just ran also.  Attached is the execution plan for my query.

    thanks  . . .

    Looking at the execution plan, estimated row number output 34669.by the way, that's 53856200 x 29831100 or 1606589687820000 rows, add that producing that product 120 times, that is 192790762538400000 rows.
    I think you have a problem! This is by the way, more rows than the number of grains on sand in Sahara!
    😎

Viewing 8 posts - 1 through 7 (of 7 total)

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