SQL Server Fix invalid datatype error, nvarchar is invalid [duplicate]

cqoc49vn  于 2023-05-05  发布在  其他
关注(0)|答案(1)|浏览(215)

This question already has an answer here:

Can I use "/" operator on nvarchar column type in SQL Server Management Studio? [closed] (1 answer)
Closed 6 days ago.

I have the query:

Select 
    Location, date, total_cases, total_deaths,        
    (total_deaths / total_cases) * 100 as DeathPercentage
From 
    PortfolioProject.dbo.CovidDeaths$
Order By 
    1, 2

But when I run it I get this error:

Msg 8117, Level 16, State 1, Line 24
Operand data type nvarchar is invalid for divide operator.

How do I rewrite the query so this error can be fixed?

I used

SELECT 
    CAST(total_deaths AS Integer) 
FROM 
    PortfolioProject.dbo.CovidDeaths$

SELECT 
    CAST(total_cases AS Integer) 
FROM 
    PortfolioProject.dbo.CovidDeaths$

and it returned the integers, but when I rerun the query with the division I get the same error.

qfe3c7zg

qfe3c7zg1#

As the error message informs us that you are attempting arithmetic on nvarchar columns, you need to cast (or convert) the data into something that you can perform arithmetic with. As you are using SQL Server I recommend you use TRY_CAST() which is fault tolerant in that if a value cannot be converted it returns NULL instead of halting the query. So, you could do this:

SELECT
      Location
    , [date]
    , total_cases
    , total_deaths
    , (TRY_CAST(total_deaths AS INT) / TRY_CAST(total_cases AS INT)) * 100.0 AS DeathPercentage
FROM PortfolioProject.dbo.CovidDeaths$
ORDER BY
      Location
    , [date]

Going a step further you may need to avoid a divide by zero error, and you may also need more precise figures than integers will provide, so perhaps:

SELECT
      Location
    , [date]
    , total_cases
    , total_deaths
    , (TRY_CAST(total_deaths AS NUMERIC(10, 2)) / NULLIF(TRY_CAST(total_cases AS NUMERIC(10, 2)), 0)) * 100.0 AS DeathPercentage
FROM PortfolioProject.dbo.CovidDeaths
ORDER BY
      Location
    , [date]

ps: "date" isn't a wise choice of column name.

In the comments you ask for help on how to "fix" the nvarchar columns. This involves several steps and you need to make sure you have a backup in case it belly flops at any point.

-- Step 1: Create a backup copy of the table
SELECT * INTO PortfolioProject.dbo.CovidDeaths_backup
FROM PortfolioProject.dbo.CovidDeaths;

-- Step 2: Add new numeric columns to the table
ALTER TABLE PortfolioProject.dbo.CovidDeaths
ADD total_cases_numeric NUMERIC(10,2),
    total_deaths_numeric NUMERIC(10,2);

-- Step 3: Set the values of the new columns using TRY_CAST
UPDATE PortfolioProject.dbo.CovidDeaths
SET total_cases_numeric = TRY_CAST(total_cases AS NUMERIC(10,2)),
    total_deaths_numeric = TRY_CAST(total_deaths AS NUMERIC(10,2));

-- WARNING you may want to stop at this point! Inspect the new columns before proceeding

-- Step 4: OPTIONAL! Drop the original nvarchar columns
ALTER TABLE PortfolioProject.dbo.CovidDeaths
DROP COLUMN total_cases,
            total_deaths;

-- Step 5: OPTIONAL! Rename the new numeric columns to the original names
EXEC sp_rename 'PortfolioProject.dbo.CovidDeaths.total_cases_numeric', 'total_cases', 'COLUMN';
EXEC sp_rename 'PortfolioProject.dbo.CovidDeaths.total_deaths_numeric', 'total_deaths', 'COLUMN';

Don't do any of this column changeover if you are not comfortable with it.

nb: throughout I have used numeric(10,2) that is a guess, you can adjust this to suit, or just use integers if that's what you need.

相关问题