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?


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

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 : /

GAH! How typical, as soon as i post it i fix it lol.
It was the WHERE Clause SELECT KitID, PartID, Quantity, 0 FROM ItemParts WHERE KitID = [B]200185[/B] I hadnt put in the enclosing apostrophes so it was trying to cast the nvarchar to an int to compare it with the int value 200185!
*palms face* WHERE KitID = '200185' got it going

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.