October 3, 2012 at 12:54 pm
Hey everyone --
I'm having some trouble figuring out what I'm doing wrong in my query, and I'm hoping to get some help.
I'm trying to create a view that will use a UNION to allow me to put headers on top of columns. I'm going to export a CSV file
with BCP and I've found this way lets me include headers in a CSV file easily.
But I keep getting an error that says "SQLState=37000, NativeError=8114 -- Error converting data type varchar to numeric... Unable to resolve column level collations'
The query that throws this error is:
SELECT 'Code' AS Code, 'Description' AS Description, 'Total Inventory' AS [Total Inventory], 'Active Amount' AS [Active Amount], 'Inactive Amount' AS [Inactive Amount],
'Soft Allocated' AS [Soft Allocated], 'Soft Incoming' AS [Soft Incoming], 'Available Amount' AS [Available Amount], 'UOM' AS UOM
UNION ALL
SELECT TOP 100 PERCENT inventory.globalMaterialName AS Code, mats.description AS [Description], inventory.totalAmount AS [Total Inventory],
inventory.activeAmount AS [Active Amount], inventory.inactiveAmount AS [Inactive Amount], inventory.softAllocatedAmount AS [Soft Allocated],
inventory.softIncomingAmount AS [Soft Incoming], inventory.availableAmount AS [Available Amount], inventory.materialPackagingWeightUOMShortName AS UOM
FROM datex_footprint.InventoryDetailedViewByMaterial inventory INNER JOIN
datex_footprint.Materials mats ON mats.id = inventory.materialId
WHERE inventory.ownerName = 'ariix'
ORDER BY Code
Any help would be appreciated!
October 3, 2012 at 1:01 pm
You have to remember that your "column headings" aren't really headings, they're data values in columns in the unioned result. Columns can only have one data type, so you are trying to put varchar data (the heading) into a column that might be numeric in the select (like an amount) from the other query in the union. If you cast everything as varchar, that should get you by.
October 3, 2012 at 1:06 pm
Ahh! Right.
Thank you very much.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply