Union Error Message

Question

Hi,

I am trying to UNION 2 tables below to create the one table but when running it, I get the following error message:

Msg 8114, Level 16, State 5, Line 159
Error converting data type nvarchar to numeric.

I can only assume this has something to do with the SubStrings but can't work out how to resolve, any ideas please? Also, where would I place the INTO within this query please?

select

into [mort_db].[dbo].[final_mort]

distinct a.invoice_number,
substring(a.rep_co,5,6) as job,
c.starting_date as start_date,
substring(a.cust_code,3,7) as cust_id,
b.fullname as userid,
sum(c.mort_bal) over (partition by a.invoice_number , c.starting_date ) as mo_value,
'typ' as system

from [mort_db].[dbo].[cust_db] as a
left join [mort_db].[dbo].[mort_users] as b
on a.[userid] = b.[userid]
left join [mort_db].[dbo].[jp_fin] as c
on a.invoice_number = c.invoice_number

where a.draft = '0' and a.rep_co like '%98%'

union

select
distinct finalinv as invoice_number,
jobnumber as job,
invoicedate as start_date,
cust_no as cust_id,
createby as userid,
total_bal as mo_value,
'ssy' as system

from [fa_db].[dbo].[inv]

where af_status = 'test'

Answer 1

Hi @Bone_12 ,

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

I can only assume this has something to do with the SubStrings but can't work out how to resolve

You could have a try with below replacement.

Replace below:

substring(a.rep_co,5,6) as job  

With below:

CASE WHEN Isnumeric(substring(a.rep_co,5,6)) = 1 THEN substring(a.rep_co,5,6) ELSE null END  as job  

where would I place the INTO within this query please?

If the [mort_db].[dbo].[final_mort] table already exists, you could refer below as mentioned by other expert:

Insert into [mort_db].[dbo].[final_mort]  
select * from ...  
union  
select * from ...  

Otherwise refer below:

select *   
 into [mort_db].[dbo].[final_mort]  
from   
 (select * from ...  
  union  
  select * from ...) a  

Best regards,
Melissa

---

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 2

Error converting data type nvarchar to numeric.

Without knowing table design/column definition we even can't guess it.
You have to lookup every column defintion to see which is nvarchar/numeric for each column and compare it for every column ordinate.

Answer 3

To clarify the cause, comment the ‘into’ line and find the line that it reported by the error message.

The error can be also caused by incomparable values that are used in WHERE or ON. If you believe that the issue is caused by the returned columns, then you can partially comment some columns until you find the problematic ones. Sometimes you can solve it by casting. For example if jobnumber is a number, but substring(a.rep_co,5,6) returns some text, and this is a valid situation, you can write something like ‘SELECT … cast(jobnumber as varchar(max)) as job …’. I.e. cast the numbers to text.

To insert into an existing table, consider ‘INSERT [mort_db].[dbo].[final_mort] SELECT … UNION …’.

Answer 4

I have taken a look at each variable and these are the format types. Any tips on how to make this union work would be greatly appreciated

Answer 5

Because Invoice_Number and Substring(rep_co, 5, 6) are both nvarchar and the matching columns finalinv and jobnumver are decimal(18,0), SQL will attempt to convert Invoice_Number and Substring(rep_co, 5, 6) to decimal(18,0). One or both of these conversions is failing for at least one row.

To find those rows, try running

Select *
from [mort_db].[dbo].[cust_db]
where  (Try_Cast(Invoice_Number As decimal(18,0)) Is Null And Invoice_Number Is Not Null) Or 
  (Try_Cast(Substring(rep_co, 5, 6) As decimal(18,0)) Is Null And rep_co Is Not Null)

That will show the rows which cannot be converted. Then you can fix those rows. Alternatively, you can make the query run without error by converting the finalinv and jobnumber columns into nvarchar. That would make the output of your query your query nvarchar for those columns. That would look like

select

into [mort_db].[dbo].[final_mort]

distinct a.invoice_number,
substring(a.rep_co,5,6) as job,
c.starting_date as start_date,
substring(a.cust_code,3,7) as cust_id,
b.fullname as userid,
sum(c.mort_bal) over (partition by a.invoice_number , c.starting_date ) as mo_value,
'typ' as system

from [mort_db].[dbo].[cust_db] as a
left join [mort_db].[dbo].[mort_users] as b
on a.[userid] = b.[userid]
left join [mort_db].[dbo].[jp_fin] as c
on a.invoice_number = c.invoice_number

where a.draft = '0' and a.rep_co like '%98%'

union

select
distinct Cast(finalinv as nvarchar(20)) as invoice_number,
Cast(jobnumber as nvarchar(28)) as job,
invoicedate as start_date,
cust_no as cust_id,
createby as userid,
total_bal as mo_value,
'ssy' as system

from [fa_db].[dbo].[inv]

where af_status = 'test'

Tom