Skip to Content
0
Jan 20, 2021 at 05:13 AM

SQL Server Error Converting data type nvchar to numeric Query error

40 Views

Hi Experts

I am trying to write a query which works, until I try to create a concatenated column merging 3 columns with a dash as a separator

I think it's something to do with the Debit column which has a thousand separator

What do I add to the query to fix it? Please help

Below is the query I have written so far

SELECT T0.[RefDate] as 'Date', 
T0.[TransId] as 'Trans. No', 
CASE WHEN T0.[TransType] = - 1 THEN 'System Generated' WHEN T0.[TransType] = - 2 THEN 'Open Balance' WHEN T0.[TransType] = - 3 THEN 'Year End Closing' WHEN T0.[TransType] = 13 THEN 'IN' WHEN T0.[TransType] = 14 THEN 'AR Cred Memo' WHEN T0.[TransType] = 15 THEN 'DN' WHEN T0.[TransType] = 16 THEN 'AR Goods Ret' WHEN T0.[TransType] = 17 THEN 'AR Sales Order' WHEN T0.[TransType] = 18 THEN 'PU' WHEN T0.[TransType] = 19 THEN 'AP Cred Memo' WHEN T0.[TransType] = 20 THEN 'Goods Receipt PO' WHEN T0.[TransType] = 21 THEN 'AP Goods Ret' WHEN T0.[TransType] = 22 THEN 'AP Purch Ord' WHEN T0.[TransType] = 23 THEN 'AR Sales Quote' WHEN T0.[TransType] = 24 THEN 'RC' WHEN T0.[TransType] = 25 THEN 'Deposit' WHEN T0.[TransType] = 30 THEN 'JE' WHEN T0.[TransType] = 46 THEN 'PS' WHEN T0.[TransType] = 56 THEN 'Chk for Payment' WHEN T0.[TransType] = 58 THEN 'Inv Bal Adj' WHEN T0.[TransType] = 59 THEN 'SI' WHEN T0.[TransType] = 60 THEN 'SO' WHEN T0.[TransType] = 67 THEN 'IM' WHEN T0.[TransType] = 68 THEN 'Work Instruct' WHEN T0.[TransType] = 69 THEN 'Landed Costs' WHEN T0.[TransType]
= 76 THEN 'Posted Deposit' WHEN T0.[TransType] = 132 THEN 'Correct Invoice' WHEN T0.[TransType] = 162 THEN 'Inv Reval' WHEN T0.[TransType] = 163 THEN 'AP Corr Inv' WHEN T0.[TransType] = 164 THEN 'AP Corr Inv Rev' WHEN T0.[TransType] = 165 THEN 'AR Corr Inv' WHEN T0.[TransType] = 166 THEN 'AR Corr Inv Rev' WHEN T0.[TransType] = 182 THEN 'BOE Deposit' WHEN T0.[TransType] = 202 THEN 'Prod Ord' WHEN T0.[TransType] = 203 THEN 'AR Down Pay' WHEN T0.[TransType] = 204 THEN 'AP Down Pay' WHEN T0.[TransType] = 321 THEN 'Manual Recon' WHEN T0.[TransType] = 310000001 THEN 'Open Balance' ELSE 'RESEARCH' END AS 'Origin', 
T0.[BaseRef] as 'Origin No', 
T0.[LineMemo] as 'Details', 
T0.[Debit] as 'Debit', 
T0.[Credit] as 'Credit', 
T1.[Segment_0] + '-' + T1.[Segment_1] + '-' + T1.[Segment_2] AS 'GL Account', 
T0.[Account] as 'AcctCode',
T0.[BaseRef] + '-' + T0.[Account] + '-' + T0.[Debit] as 'OriginAcctDebitMerge' 
FROM JDT1 T0  INNER JOIN OACT T1 ON T0.[Account] = T1.[AcctCode] WHERE T0.[RefDate] >= '01.01.19' GROUP BY T0.[RefDate], T0.[TransId], T0.[TransType], T0.[BaseRef], T0.[LineMemo], T0.[Debit], T0.[Credit], T1.[Segment_0], T1.[Segment_1], T1.[Segment_2], T1.[FormatCode], T1.[AcctName], T0.[Account]

Regards

Rahul