none
Error converting varchar to numeric RRS feed

  • Question

  • For the Full CTN I'm doing a cast and it's not working. Should I also do it in the subquery?

    Select 
    fcomponent as [PartNo],
     fitem as [Class],
    --Remainder Exist
    [Remainder Exist] = 0,
    -- Original Qty
    fqty as [Original Qty],
    --Qty   
    Case when fitem = 'MSHELF'
                Then [Full Cartons]
                Else fqty
                End as [fqty],
     -- fnusrqty1
    fnusrqty1,
    
    --Full Ctn
    Case when Remainder = '0'
    Then cast(O.[In Pieces] AS numeric)  Else '' end as [Full CTN],
    --Remainder Qty
    
    		case when cast([Full Cartons] as numeric(15,5)) >= 0.0000
    		Then 0
    		Else  '' end as [Remainder Qty]
    	
    From
    (
    Select 
     
    fcomponent, fitem, fqty, fnusrqty1, 
    fqty/fnusrqty1 as [# of Cartons],
    FLOOR(fqty/fnusrqty1) as [Full Cartons],
    (CAST(fqty AS FLOAT)/CAST(fnusrqty1 AS FLOAT)) - FLOOR((CAST(fqty AS FLOAT)/CAST(fnusrqty1 AS FLOAT))) as Remainder,
    ((fqty/fnusrqty1) - FLOOR(fqty/fnusrqty1)) * (fnusrqty1) as [In Pieces] 
    From inboms 
    inner join inmastx on inboms.fcomponent=inmastx.fpartno 
    where fparent='IWD-SS-IMPACT-TEST-LP'
     
    ) O
     
    UNION ALL
    Select 
    fcomponent as [PartNo],
     fitem as [Class], 
    
    --Remainder Exist
    Remainder = '1',
    -- Original Qty
    fqty as [Original Qty],
    --Qty
    round(Remainder * fnusrqty1,0) as Quantity,
    -- fnusrqty1
    fnusrqty1,
    
    --Full Ctn
    cast([In Pieces] as numeric)  as [Full CTN],
    --Remainder Qty
    cast([Full Cartons] as numeric(15,5)) as [Remainder Qty]
    
    From 
    (
    Select
    fcomponent, fitem, fqty, fnusrqty1,  fqty/fnusrqty1 as [# of Cartons],
    FLOOR(fqty/fnusrqty1) as [Full Cartons],
    (CAST(fqty AS FLOAT)/CAST(fnusrqty1 AS FLOAT)) - FLOOR((CAST(fqty AS FLOAT)/CAST(fnusrqty1 AS FLOAT))) as Remainder,
    ((fqty/fnusrqty1) - FLOOR(fqty/fnusrqty1)) * (fnusrqty1) as [In Pieces] 
    From inboms 
    inner join inmastx on inboms.fcomponent=inmastx.fpartno 
    where fparent='IWD-SS-IMPACT-TEST-LP'
    ) Q
     
    where fitem='MSHELF'
    order by fitem desc 

    Wednesday, November 14, 2018 2:44 PM

Answers

  • '' is not a valid numeric value

    CASE WHEN have to return same data type from all branches

    so if you want value to be numeric else has to return a default value  like 0,-1 etc (or may be NULL if nullable field)

    It cant be '' which is a string value


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by David9501 Wednesday, November 14, 2018 3:37 PM
    Wednesday, November 14, 2018 2:55 PM