SQL Error

Transact-SQL
1

Below SQL query results in error 'Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric'

SELECT ,ROW_NUMBER() OVER (PARTITION BY VendorID, InvNumber order by Ordinal)16384 SEQNUMBR
FROM (
SELECT
1 Ordinal,
StoreID,
VendorID,
TransType,
InvNumber,
(case
when AcctNumber = 1 then concat(Storeid,'-','4050','-','00')
when AcctNumber = 4 then concat(Storeid,'-','4050','-','00')
when AcctNumber = 5 then concat(Storeid,'-','4051','-','00')
when AcctNumber = 6 then concat(Storeid,'-','4052','-','00')
when AcctNumber = 7 then concat(Storeid,'-','4053','-','00')
when AcctNumber = 8 then concat(Storeid,'-','4054','-','00')
when AcctNumber = 9 then concat(Storeid,'-','4055','-','00')
when AcctNumber = 10 then concat(Storeid,'-','4056','-','00')
when AcctNumber = 11 then concat(Storeid,'-','4057','-','00')
when AcctNumber = 12 then concat(Storeid,'-','4058','-','00')
when AcctNumber = 13 then concat(Storeid,'-','4059','-','00')
when AcctNumber = 14 then concat(Storeid,'-','4070','-','00')
when AcctNumber = 18 then concat(Storeid,'-','5070','-','00')
when AcctNumber = 23 then concat(Storeid,'-','5120','-','00')
when AcctNumber = 25 then concat(Storeid,'-','5137','-','00')
when AcctNumber = 26 then concat(Storeid,'-','5138','-','00')
when AcctNumber = 28 then concat(Storeid,'-','5140','-','00')
when AcctNumber = 31 then concat(Storeid,'-','5254','-','00')
when AcctNumber = 32 then concat(Storeid,'-','5256','-','00')
when AcctNumber = 33 then concat(Storeid,'-','5258','-','00')
when AcctNumber = 34 then concat(Storeid,'-','5260','-','00')
when AcctNumber = 35 then concat(Storeid,'-','5262','-','00')
when AcctNumber = 36 then concat(Storeid,'-','5264','-','00')
when AcctNumber = 37 then concat(Storeid,'-','5268','-','00')
when AcctNumber = 69 then concat(Storeid,'-','5136','-','00')
else ' '
end) AcctNumber,
InvoiceDate,
Case
when TransType = 'E Invoice' then DistAmount
when TransType = 'E Credit Memo' then DistAmount-1
else '' end PurchAmt,
Case
when TransType = 'E Invoice' then DistAmount
when TransType = 'E Credit Memo' then 0
else '' end DebitAmount,
Case
when TransType = 'E Invoice' then 0
when TransType = 'E Credit Memo' then DistAmount-1
else '' end CreditAmount,
6 DISTTYPE,
0 DocAmount
FROM APDataLoad
Union
SELECT
2 Ordinal,
Storeid,
APDataLoad.VendorID,
TransType,
InvNumber,
'2000-2010-00' AcctNumber,
InvoiceDate,
0 PurchAmt,
Case
when TransType = 'E Invoice' then 0
when TransType = 'E Credit Memo' then ROUND(SUM(DistAmount), 2)-1
else ''
end DebitAmount,
Case
when TransType = 'E Invoice' then ROUND(SUM(DistAmount),2)
when TransType = 'E Credit Memo' then 0
else ''
end CreditAmount,
2 DISTTYPE,
case
when TransType = 'E Invoice' then ROUND(SUM(DistAmount),2)
when TransType = 'E Credit Memo' then ROUND(SUM(DistAmount),2)-1
else ''
end DocAmount
FROM APDataLoad
Group by StoreID, VendorID, TransType, InvNumber, InvoiceDate
)A
order by StoreID, VendorID, InvNumber, InvoiceDate, Ordinal

I've tried changing the datatypes of table, using cast function and anything else I could come up with but error continues. Any help appreciated!

2

The datatype of table is as follows:
StoreID, Invnumber, VendorID & AcctNumber are all varchar

3

This is my best guess, since I don't have sample data that actually caused the problem.

You can't use an empty string, '', in a decimal value, viz:
select 1.23 union select ''

Try NULL instead, for example:
...
Case
when TransType = 'E Invoice' then 0
when TransType = 'E Credit Memo' then ROUND(SUM(DistAmount), 2)-1
else NULL /* instead of '' as in original code /
end DebitAmount,
Case
when TransType = 'E Invoice' then ROUND(SUM(DistAmount),2)
when TransType = 'E Credit Memo' then 0
else NULL / instead of '' as in original code */
end CreditAmount,
...

And so on, wherever '' and decimal values are mixed in one column. You can '' in an int column, but it will be shown as 0, not blank.

4

You were exactly right! Thank you so much, I wish that I had sent the question earlier!