I need help with an SSMS SQL QEURY

Question

I have the following query and I am getting the following error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'M7' to data type int.

I had tried both with the casting and without the casting here is how it looks with the casting.

WITH LLD AS
(
    SELECT
        LTRIM(RTRIM(WTDOCO)) AS WO,
        MAX(USADAT) AS LABORREPORTDT
    FROM
        DWH.dbo.F31122
        INNER JOIN JDE.IQDATE.IQDATE
        ON WTPBDT = JULIAN
    WHERE
        USADAT <= GETDATE()
    GROUP BY
        WTDOCO
)

SELECT
    STRTDT.USADAT AS RTR_START_DATE,
    DUEDT.USADAT AS RTR_RQST_DATE,
    RQST.USADAT AS WO_RQST_DATE,
    ORDDT.USADAT AS WO_ORDER_DATE,
   
    TRGT.TDDL011 AS TARGETCD,
    RUNOUTDT AS RUNOUTDT,
    WOH.WAPRTS AS PRIORITY,
    WADCTO AS WOType,
    WOH.WASRST AS WORK_STATUS,
    CASE
        WHEN (WOH.WASRST) < 30 THEN 'Unreleasable'
        ELSE 'Releasable'
    END as STS_TYPE,
    WOD.WLDOCO AS WORK_ORDER,
    LTRIM(RTRIM(WOD.WLKITL)) AS ITEM_NUMBER,
    LTRIM(RTRIM(WOH.WAITM)) AS SHORT_ITEM_NUMBER,
    WOD.WLOPSQ/100 AS OPERATION_SEQUENCE,
    LTRIM(RTRIM(WOD.WLMCU)) AS WORKCENTER,
    UPPER(LTRIM(RTRIM(MC.MCDL01))) AS WCDESC,
    COALESCE(WCD.Type, 'Other') as WC_TYPE,
    LTRIM(RTRIM(WOD.WLDSC1)) AS ROUTERDESC,
    WOD.WLRUNL/100 AS STD_LAB_HRS,
    WOD.WLRUNM/100 AS STD_AUTO_HRS,
    (WOD.WLRUNL/100 + WOD.WLRUNM/100) AS TOTAL_STANDARD_HOURS,
    WOD.WLUORG/100000 AS ORIGINAL_ORDER_QUANTITY,
    WOD.WLSOQS/100000 AS REPORTED_PARTS_COMPLETE,
    WOH.WASOQS/100000 AS PARTS_RECEIVED,
    CASE
        WHEN (WOD.WLUORG > WOD.WLSOQS) THEN (WOD.WLUORG - WOD.WLSOQS)/100000
        ELSE 0
    END AS REM_PCS,
    CASE
        WHEN (WLUORG > WLSOQS) THEN (WLUORG - WLSOQS) * POWER(10, -2) * (WLRUNL/WLUORG)
        ELSE 0
    END AS LABOR_HRS_REM,
    CASE
        WHEN (WLUORG > WLSOQS) THEN (WLUORG - WLSOQS) * POWER(10, -2) * (WLRUNM/WLUORG)
        ELSE 0
    END AS AUTO_HRS_REM,
    LTRIM(RTRIM(WOD.WLAPID)) AS TOOL_NUMBER,
    COALESCE(LTRIM(RTRIM(TS.TOOL2)), WLAPID) AS TOOL_GROUP,
    DENSE_RANK() OVER(PARTITION BY WOD.WLDOCO ORDER BY WLOPSQ ASC) as RANK,
    ROW_NUMBER() OVER(ORDER BY WOD.WLDOCO ASC , WLOPSQ ASC) as [INDEX],
    CASE
        WHEN WOD.WLUORG <= WOD.WLSOQS THEN 'Y'
        Else 'N'
    End as COMPLETED,
    CASE
        WHEN WOD.WLSOQS > 0 THEN 'Yes'
        WHEN WOD.WLSOQS < 0 THEN 'No'
        Else 'No'
    End as Started,
    CST.TDDL011 AS CUSTOMER,
    MFGNT.TDDL011 AS MFGNOTE,
    CSNT.TDDL011 AS CSNOTE,
    MFGSQN.TDDL011 AS MFGSQN,
    CASE
        WHEN MFGSQN.TDDL011 <> '' THEN 'SEQ'
        ELSE 'UNSEQ'
    END as SEQUENCED_YN,
    CASE
        WHEN ((WOH.WASRST ) < 30) OR ((WOH.WASRST) >= 45) THEN 0
        ELSE 1
    END as Flg_St3044,
    CASE
        WHEN DUEDT.USADAT < GETDATE() THEN 'Past Due'
        WHEN (DUEDT.USADAT < DATEADD(dd, -(DATEPART(dw, CONVERT(DATE, GETDATE())) - 1), DATEADD(dd, 7, CONVERT(DATE, GETDATE())))) THEN 'This Week'
        WHEN (DUEDT.USADAT >= (DATEADD(dd, -(DATEPART(dw, CONVERT(DATE, GETDATE())) - 1), DATEADD(dd, 7, CONVERT(DATE, GETDATE())))) AND DUEDT.USADAT < DATEADD(dd, -(DATEPART(dw, CONVERT(DATE, GETDATE())) - 1), DATEADD(dd, 14, CONVERT(DATE, GETDATE())))) THEN 'Next Week'
        WHEN (DUEDT.USADAT >= DATEADD(dd, -(DATEPART(dw, CONVERT(DATE, GETDATE())) - 1), DATEADD(dd, 14, CONVERT(DATE, GETDATE())))) THEN 'Future'
    END AS DtRangeFlag,
    CASE
        WHEN WLRUNM > 0 THEN 'Machine'
        ELSE 'Run'
    END AS MRFlag
INTO DBO.MoldingDispatch --SELECT TOP(10)*

FROM
    JDE.PRODDTA.F3112 WOD
    LEFT OUTER JOIN JDE.IQDATE.IQDATE DUEDT
    ON WOD.WLDRQJ = DUEDT.JULIAN
    LEFT OUTER JOIN JDE.proddta.F4801 WOH
    ON WOD.WLDOCO = WOH.WADOCO
    LEFT OUTER JOIN JDE.IQDATE.IQDATE RQST
    ON WOH.WADRQJ = RQST.JULIAN
    LEFT OUTER JOIN JDE.IQDATE.IQDATE STRTDT
    ON WOD.WLSTRT = STRTDT.JULIAN
    LEFT OUTER JOIN PY900DTA.F0006 MC
    ON WOD.WLMCU = MC.MCMCU
    LEFT OUTER JOIN PY900DTA.F55TXT02 CST
    ON CAST(LTRIM(RTRIM(WOD.WLDOCO)) AS NVARCHAR) = CAST(LTRIM(RTRIM(CST.TDDOCO)) AS NVARCHAR)
    AND CST.TDLDLN/1000 = '1'
    LEFT OUTER JOIN PY900DTA.F55TXT02 MFGNT
    ON CAST(LTRIM(RTRIM(WOD.WLDOCO)) AS NVARCHAR) = CAST(LTRIM(RTRIM(MFGNT.TDDOCO)) AS NVARCHAR)
    AND MFGNT.TDLDLN/1000 = '2'
    LEFT OUTER JOIN PY900DTA.F55TXT02 CSNT
    ON CAST(LTRIM(RTRIM(WOD.WLDOCO)) AS NVARCHAR) = CAST(LTRIM(RTRIM(CSNT.TDDOCO)) AS NVARCHAR)
    AND CSNT.TDLDLN/1000 = '3'
    LEFT OUTER JOIN PY900DTA.F55TXT02 MFGSQN
    ON CAST(LTRIM(RTRIM(WOD.WLDOCO)) AS NVARCHAR) = CAST(LTRIM(RTRIM(MFGSQN.TDDOCO)) AS NVARCHAR)
    AND MFGSQN.TDLDLN/1000 = '4'
    LEFT OUTER JOIN PY900DTA.F55TXT02 TRGT
    ON CAST(LTRIM(RTRIM(WOD.WLDOCO)) AS NVARCHAR) = CAST(LTRIM(RTRIM(TRGT.TDDOCO)) AS NVARCHAR)
    AND TRGT.TDLDLN/1000 = '5'
    LEFT OUTER JOIN JDE.IQDATE.IQDATE ORDDT
    ON WOD.WLTRDJ = ORDDT.JULIAN
    LEFT OUTER JOIN dbo.QRYSOUMITR WCD
    ON LTRIM(RTRIM(WCD.WC)) = LTRIM(RTRIM(WOD.WLMCU))
    LEFT OUTER JOIN QRYSOUMITRTOOLSPLIT3 TS
    ON LTRIM(RTRIM(WOD.WLAPID)) = LTRIM(RTRIM(TS.TOOL))
    
	LEFT OUTER JOIN LLD
	ON LLD.WO = LTRIM(RTRIM(WLDOCO))
    
	
	LEFT OUTER JOIN PY900DTA.F55INVRUNO RN
    ON RN.WO = WLDOCO
    AND RN.WOTYPE = 'WO'
    AND RN.PARTNO = WOH.WALITM

ORDER BY
    WOD.WLDOCO ASC,
    WOD.WLOPSQ ASC

Answer 1

You need to know in which column the value M7 can be hiding.

Generally, when you mix data types in an expression, SQL Server applies type precedence, so a type with lower precedence will be converted to the type with higher precedence. nvarchar has lower precedence than int, and conversion from string to number often leads to conversion error.

You first need to identify where in the query these types meet. Then you can resolve in one of two ways:

• Convert the int column to nvarcar(10)
• Convert the string column to int, using the function try_convert. With try_convert the conversion yields NULL and not an error when the string value is not convertible.

Answer 2

What is TDLDLN column's type in each of the table?