Issue with converting Varchar to Numeric so I can add them

Question

Post reply

Issue with converting Varchar to Numeric so I can add them

jeffshelix

SSCrazy

Points: 2257

November 4, 2022 at 2:09 pm

#4108721

I have this so far . . .

SELECT *
INTO #tempValue
FROM
(
SELECT
t.HTS_98_Value,
t.HTS_99_Value,
t.HTS_B_Value,
t.HTS_A_Value,
t.HTS_C_Value,
t.Customs_Entry_Number, 
SUM(convert(numeric,replace(isnull(hts_a_value, '0'), '','0')) + convert(numeric,replace(isnull(hts_b_value, '0'), '','0'))) AS New
--HTS_A_Value + HTS_B_Value) AS new
--+ HTS_B_Value + HTS_A_Value + HTS_99_Value) AS Header_Val
FROM #temp1 t
GROUP BY t.HTS_98_Value, t.HTS_99_Value, t.HTS_B_Value, t.HTS_A_Value,
t.HTS_C_Value, t.Customs_Entry_Number
) V

I get an error converting varchar. I think the issue might be that since is raw data, there might be a mixture of nulls, possible bad characters, etc. I think i should convert/cast/replace etc, at the column level, but nothing I have tried works. Thoughts? thanks. Due to business reasons, I can't get IT to change the field types in the table.

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

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

Phil Parkin SSC Guru Points: 246974 More actions · Answer 1

If a column contains a non-numeric character, do you want to treat it is zero? If not, what?

Have you investigated TRY_CAST()?

Remember that the numeric datatype has both a size and precision. It is good practice to specify these.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jeffshelix SSCrazy Points: 2257 More actions · Answer 2

thanks.

Any non numeric value can be treated as a 0.

I did try try_cast . . (with just using two values in the SUM)

SELECT
TRY_CAST(t.HTS_98_Value AS NUMERIC) AS 'HTS_98_value',
try_Cast (t.HTS_99_Value AS NUMERIC) AS HTS_99_Value,
try_cast (t.HTS_B_Value AS NUMERIC) AS HTS_B_Value,
try_cast (t.HTS_A_Value AS NUMERIC) AS HTS_A_Value,
try_cast (t.HTS_C_Value AS NUMERIC) AS HTS_C_Value,
SUM(hts_a_value + hts_b_value) AS New,
t.Customs_Entry_Number 
FROM #temp1 t
GROUP BYTRY_CAST(t.HTS_98_Value AS NUMERIC),
            TRY_CAST(t.HTS_99_Value AS NUMERIC),
            TRY_CAST(t.HTS_B_Value AS NUMERIC),
            TRY_CAST(t.HTS_A_Value AS NUMERIC),
            TRY_CAST(t.HTS_C_Value AS NUMERIC),
            t.Customs_Entry_Number
) V

I get this error:

Msg 8117, Level 16, State 1, Line 80

Operand data type varchar is invalid for sum operator.

Ed B Hall of Fame Points: 3669 More actions · Answer 3

You need to TRY_CAST the columns before you add/sum them.

SUM(TRY_CAST(hts_a_value AS NUMERIC) + TRY_CAST(hts_b_value AS NUMERIC)) AS New,

What is the source data like? If the numbers are integers I would use INT, not NUMERIC.

If they are decimals then casting to numeric without precision and scale will round the numbers.

DECLARE @this NUMERIC = 1.4999999,
        @that NUMERIC = 1.5
SELECT @this, @that

jeffshelix SSCrazy Points: 2257 More actions · Answer 4

SELECT *
INTO #tempValue
FROM
(
SELECT
--TRY_CAST(t.HTS_98_Value AS NUMERIC (10)),
--try_Cast (t.HTS_99_Value AS NUMERIC (10)),
--try_cast (t.HTS_B_Value AS NUMERIC (10))
--try_cast (t.HTS_A_Value AS NUMERIC (10))
--try_cast (t.HTS_C_Value AS NUMERIC (10))
SUM(TRY_CAST(hts_a_value AS NUMERIC) + TRY_CAST(hts_b_value AS NUMERIC)+TRY_CAST(hts_c_value AS NUMERIC)
+TRY_CAST(hts_98_value AS NUMERIC)+TRY_CAST(hts_99_value AS NUMERIC)) AS New,
t.Customs_Entry_Number 
FROM #temp1 t
GROUP BY TRY_CAST(t.HTS_98_Value AS NUMERIC(10)),
         TRY_CAST(t.HTS_99_Value AS NUMERIC(10)),
         TRY_CAST(t.HTS_B_Value AS NUMERIC(10)),
         TRY_CAST(t.HTS_A_Value AS NUMERIC(10)),
         TRY_CAST(t.HTS_C_Value AS NUMERIC(10)),
         t.Customs_Entry_Number
) V

All cells are empty in column "new" . . . thoughts. Many thanks

ratbak Ten Centuries Points: 1380 More actions · Answer 5

Then none of those columns in any of the rows evaluate to valid NUMERIC(10) values. e.g., they might have spaces, punctuation, or alpha characters... or actually be null.

Do a select w/o the SUM & GROUP BY to confirm for yourself -- you should see nothing but nulls.

Then just do a select on the raw data w/o the TRY_CASTs to see what the raw data looks like.

If you have any rows where all the columns are null, or any columns for which all rows are null, you probably need to wrap the TRY_CASTs in ISNULL or COALESCE -- e.g., :

If you have any rows where all the columns are null, or any columns for which all rows are null, you probably instead need:
SUM(ISNULL(TRY_CAST(hts_a_value AS NUMERIC),0) + ISNULL(TRY_CAST(hts_b_value AS NUMERIC),0)+ISNULL(TRY_CAST(hts_c_value AS NUMERIC),0)
+ISNULL(TRY_CAST(hts_98_value AS NUMERIC),0)+ISNULL(TRY_CAST(hts_99_value AS NUMERIC),0)) AS New,

ratbak Ten Centuries Points: 1380 More actions · Answer 6

Note that query would still return a row for every different combination of the columns. If all you're trying to get is the sum, you need to change the group by to

GROUP BY 
    TRY_CAST(hts_a_value AS NUMERIC) + TRY_CAST(hts_b_value AS NUMERIC)+TRY_CAST(hts_c_value AS NUMERIC),
    t.Customs_Entry_Number

jeffshelix SSCrazy Points: 2257 More actions · Answer 7

I want the sum of each of those column per record.

If each has 10.oo, I want 50.00. but all columns arent all populated.

thanks

jeffshelix SSCrazy Points: 2257 More actions · Answer 8

jeffshelix

SSCrazy

Points: 2257

November 7, 2022 at 9:30 pm

#4109814

Solved, thanks 10 Centuries!!!!!!!!!