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 <ronanvanriet@...>
To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
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]