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?
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%'
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
where af_status = 'test'