Error converting data type nvarchar to numeric

Question

HI Team
I have an view where it would be pulling multiple columns from few tables and union into the different set of queries ,
here we are passing Nulls as column names for the couple of column in the table, and trying to update the data view , it is getting failed

select
Null as name
Null as id
Null as batch
from table
union all
select
Null as name
Null as id
Null as batch
from table

like this the query is made,i have getting the fllow error,while i execute the view,but the view is updating fine,while retriving the data from the view we were getting this error

i was really tried to get soluction out of this,can some one please help me on this topic.

"Error converting data type nvarchar to numeric."

I have made check the data types and meta data,all looks identical, i obeseve one thing here, i pass some value like '0' its is getting executed,but i supposed to insert null values into this fields.
please provide some insist on this

Answer 1

You cannot posted sample data
create TABLE #orders

(

QtyOrdered VARCHAR(50)

)

INSERT #orders
SELECT '19'
UNION ALL SELECT 'Why?'
UNION ALL SELECT '1.84736378483933'
UNION ALL SELECT '1e3'
UNION ALL SELECT '$1.40'

SELECT CASE WHEN ISNUMERIC(QtyOrdered)=1 AND QtyOrdered NOT LIKE '%[^0-9.]%'

THEN CAST(QtyOrdered AS DECIMAL(19,6))
ELSE 0.0
END AS Qty
FROM #orders

Answer 2

Since you did not cast your NULL to a data type, SQL Server guesses based on the first values it finds.

You need to use CAST to get the correct data types for the NULL.

select
CAST(Null AS VARCHAR(100)) as name,
CAST(Null AS INT) as id,
CAST(Null AS VARCHAR(100)) as batch,
from table
union all
select
Null as name,
Null as id,
Null as batch,
from table

Answer 3

Hi @Naresh y ，

As Tom said,you need to convert the data type of null to the data type of the corresponding column.
I did a test and found that it can be executed successfully after changing the data type of null, please refer to:

    create TABLE #orders  
    (QtyOrdered VARCHAR(50))  
    INSERT #orders  
    SELECT '19'  
    UNION ALL SELECT 'Why?'  
    UNION ALL SELECT '1.84736378483933'  
    UNION ALL SELECT '1e3'  
    UNION ALL SELECT '$1.40'  

select NULL as name,  
Null as id,  
Null as batch into test from #orders  

update test   
set name='Amy'   

DROP TABLE test   
select CAST(NULL AS VARCHAR(15)) as name,  
Null as id,  
Null as batch into test from #orders  

update test   
set name='Amy'    

select * from test   

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Naresh y ，