CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
user6935204
user6935204

Reputation:

Types don't match between the anchor and the recursive part

I am executing the following query : 

WITH rg(id, childid, NAME) 
     AS (SELECT id, 
                0 ChildID, 
                NAME 
         FROM   kuaf 
         WHERE  id = 1000 
         UNION ALL 
         SELECT c.id, 
                c.childid, 
                k.NAME 
         FROM   kuafchildren c 
                JOIN rg p 
                  ON ( c.childid = p.id ) 
                JOIN kuaf k 
                  ON ( c.id = k.id )) 
SELECT DISTINCT ka.id 
FROM   rg, 
       lm_approvals app 
       JOIN kuaf ka 
         ON ( ka.id = app.useridentifier ) 
       JOIN dtree dt 
         ON ( dt.dataid = app.dataid ) 
WHERE  app.dataid = 25773 
       AND app.isapproved = 0 
       AND Replace(ka.NAME, ka.type + '_', dt.ownerid + '_') = rg.NAME

But I am ending with the following error:

Types don't match between the anchor and the recursive part in column "ChildID" of recursive query "rg".

Upvotes: 1

Views: 1367

Answers (3)

Aditya
Aditya

Reputation: 2301

Try this ...

;WITH CTE RG AS (
SELECT   Id,
         0 AS 'ChildId',
         Name
FROM     Kuaf
WHERE    id = 1000

UNION ALL

SELECT   C.Id,
         C.ChildId,
         C.Name
FROM     KuafChildren C
         INNER JOIN RG ON (C.ChildId = RG.ChildId)
)
SELECT ...

I assume ChildId column datatype is int

Upvotes: 0

Eralper
Eralper

Reputation: 6612

If you change the first SELECT or the anchor part of the resursive CTE as follows, it will work

SELECT id, 
                CAST(0 as int) ChildID, 
                NAME 
         FROM   kuaf 
         WHERE  id = 1000

This is the case we experience generally with empty string or NULL is selected at this step. So explicitely defining the type at anchor SELECT is required for CTEs

Upvotes: 3

Андрей Коршунов
Андрей Коршунов

Reputation: 36

I myself have not tried this Judging by the documentation it is necessary to explicitly convert 0 type like kuafchildren.childid through CAST ( expression AS data_type [ ( length ) ] )
or CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
expression - 0 data_type [ ( length ) ] ) same as kuafchildren.childid

You can try to write instead 0 ChildID kuaf.id*0 ChildID This might work if kuaf.id type the same as the kuafchildren.childid

Upvotes: 0

Related Questions