Hello. One update. I have casted the ID and PAR_ID columns to varchar(5).
FlameRobin tells me myRecursive_VW has the following columns: - ID varchar(5) - PAR_ID varchar(5) - NAME varchar(255) However when I execute the recursive query below I get the following error. Not sure what else to check now. I am open for suggestions. --------------------------- An IBPP error occurred. --------------------------- *** IBPP::SQLException *** Context: Statement::Fetch Message: isc_dsql_fetch failed. SQL Message : -802 Arithmetic overflow or division by zero has occurred. Engine Code : 335544321 Engine Message : arithmetic exception, numeric overflow, or string truncation string right truncation --------------------------- OK --------------------------- Best regards, ________________________________ From: Ronan van Riet <[email protected]> To: "[email protected]" <[email protected]> Sent: Friday, June 21, 2013 3:47 PM Subject: [firebird-support] Conversion Error in Recursive Query Hello. I could use some help with understanding why my query returns a partial record set and a conversion error from string "102". SQL Message: -413 Engine code: 335544334 FlameRobin tells me myRecursive_VW has the following columns: - ID Bigint - PAR_ID Bigint - NAME varchar(255) I am using the following test to confirm the ID and PAR_ID are indeed integers. SELECT a.ID+2, a.PAR_ID+2, a.NAME FROM MYRECURSIVE_VW a This test passed. I also created a procedure and have the code run against that instead of my view. Same result. I do not see where the conversion is happening. Any pointers? WITH RECURSIVE myCTEName (id,level,top_key,par_id,name) as ( select id , 0 as level , id as top_key , null as immediate_parent_key, name from myRecursive_VW where par_id is null UNION ALL select chd.id,par.level+1,par.top_key,chd.par_id, name FROM myCTEName par JOIN myRecursive_VW chd ON chd.par_id = par.id ) select * from myCTEName Best regards. [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
