CODEWITHSUNDEEP
sql-servernumericvarchar
user3496218
user3496218

Reputation: 205

Error with Varchar to Numeric

For background: this works fine without the addition of the 'E' Table in production.

I am trying to execute the below query but am getting the error:

Msg 8114, Level 16, State 5, Line 10
Error converting data type varchar to numeric.

The odd thing is that it executes fine on our test DB - not seeing anything trying to convert to numeric so a bit baffled. Not really sure what would cause that difference as I assume data types are the same in the test DB and the production DB - any ideas?

Thanks. 

/* - adding po balance qty causes duplicates 
   - adding stock category causes duplicates
   - added PO comments using MAX function to return only first comment - gets rid of duplicates*/ 

/* Using NEW employee table as of 11/2 */
SELECT 
    S.team_member_name AS [ASSIGNED EMPLOYEE],  --Using new employee table - only on bus unit as of 11/2
    H.po_type AS [ PO TYPE],
    G.order_no AS [GPS PO #],
    P.po_number AS [SAP PO NUMBER],
    M.department AS [DEPARTMENT],
    G.order_qty AS [GPS QTY],
    SUM(P.po_ordered_quantity) AS [SAP QUANTITY],
    (SUM(P.po_ordered_quantity) - G.order_qty) AS [DIFFERENCE],
    G.last_conf_date_cst AS [LAST CONFIRMED DATE],
    K.business_unit_desc AS [BU],
    M.[description] AS [DESCRIPTION],
    P.material AS [MATERIAL],
    MAX(P.comment) AS [PO COMMENT],
    MIN(E.date) AS [FIRST SHOWN ON RPT]
FROM   
    (SELECT  
         order_no, order_qty, order_status,
         last_conf_date_cst
     FROM 
         asagdwpdx_prod.dbo.SimoxOrder1
     UNION ALL
     SELECT  
         order_no, order_qty, order_status,
         last_conf_date_cst
     FROM 
         asagdwpdx_prod.dbo.SimoxOrder2
     UNION ALL
     SELECT  
         order_no, order_qty, order_status,
         last_conf_date_cst
     FROM 
         asagdwpdx_prod.dbo.SimoxOrder3) G

JOIN
    pdx_sap_user.dbo.vw_po_header H ON G.order_no = h.ahag_number
JOIN 
    pdx_sap_user.dbo.vw_po_item P ON H.po_number = P.po_number
JOIN 
    pdx_sap_user.dbo.vw_mm_material M ON P.material = M.material
JOIN
    adi_user_maintained.dbo.SCM_PO_Employee_Name S ON P.po_number = S.po_number
JOIN
    pdx_sap_user.dbo.vw_kd_business_unit K ON M.business_unit_code = K.business_unit_code
JOIN
    adi_user_maintained.dbo.scm_po_error_tracking E ON E.po_number = P.po_number
WHERE     
    M.business_segment_code NOT IN ('421', '420', '422', '424') --exclude adi golf 
    AND E.report_source = 'gpsvssap_qty'
GROUP BY  
    G.order_no, -- GROUP BY needed on aggregate function in SELECT 
    G.order_qty,
    G.order_status,
    P.po_number,
    P.material,
    P.del_indicator,
    H.po_created_by,
    M.[description],
    M.department,
    S.team_member_name,
    K.business_unit_desc,
    G.last_conf_date_cst,
    H.po_type
HAVING 
    G.order_qty <> SUM(P.po_ordered_quantity)
    AND G.order_status NOT IN ('60', '90') -- excluding GPS cancelled (90) & shipped (60) - do we need to exclude other status'?
    AND P.del_indicator <> 'L'

Upvotes: 0

Views: 57

Answers (1)

Lolu Omosewo
Lolu Omosewo

Reputation: 263

You might want to look at 'MAX(P.comment)'. You cant find Max of string. Unless your comment is numeric

Upvotes: 2

Related Questions