question

Bone12-2270 avatar image
0 Votes"
Bone12-2270 asked TomCooper-6989 answered

Union Error Message

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'

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @Bone12-2270,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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 …’.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bone12-2270 avatar image
0 Votes"
Bone12-2270 answered

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


135842-image.png



image.png (12.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.