I wrote a query yesterday and it ran fine. Today i inserted a new record and it has broken my query.
Can anyone help me figure out why?
Query:
WITH temp_orgChart (KitID, PartID, Quantity, iteration) AS
(
SELECT KitID, PartID, Quantity, 0
FROM ItemParts WHERE KitID = 200185
UNION ALL (SELECT b.KitID, b.PartID, b.Quantity, a.iteration + 1
FROM temp_orgChart AS a, ItemParts AS b
WHERE a.PartID = b.KitID)
)
SELECT SUM(Quantity)
FROM temp_Orgchart
WHERE PartID NOT IN (SELECT KitID FROM temp_orgChart)
I have a table of stock items, its primary key is an nvarchar(50) called StockNo.
The ItemParts table is as follows:
KitID nvarchar(50) composite_key f_key-StockNo
PartID nvarchar(50) composite _key f_key_StockNo
Quantity numeric(4,0) NOT NULL
Basically, a stock item can be a kit made up of otehr stock items and/or a part in a kit.
The query is a recursive search to find all the composite parts. It returns a temporary table holding all the levels of kit to part then a select statement extracts the bottom level.
Everything went fine when i only had numeric StockNo's. They were stored as nvarchar but contained only digits. As soon as i added an entry with a stock no of **CustomKit#03" i got
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '**CustomKit#03' to data type int.
What i cant figure out is where the conversion is being attempted. There aren't any int data types in the query.
Please, someone help me, i thought i finally had this working and now its crashed down around my ears : /