Recursive CTE and Type Mismatch

[MajP]

As mentioned here

Recommendation for calling Share Procedures with Parameter

I am converting my first DB to SQL Server and teaching myself to use SQL as a backend. To teach myself as much SQL Server I am doing everything on the server side even though the database is relatively small. It is however pretty complex code since it is a self refencing hierarchical database...

www.access-programmers.co.uk

I am in the process of converting a complex Access database over to SQL Server in order to teach myself. As mentioned this is a self referencing hierarchical database dealing with a population of whales. I am doing my first Recursive CTE and works well. I can return the descendants for a given mother whale.

WITH cte_Ancestors AS (
    SELECT     
      1 as Level,
      CASENAME,
      MOT,
      caseName as TOP_MOT
      --,CaseName as Lineage
    FROM     
        vw_Population
    WHERE casename = 'aly'
    UNION ALL
    SELECT
      Level + 1,
      A.CaseName,
      A.MOT,
      TOP_MOT
      --,Lineage + a.CASENAME
    FROM
        vw_Population as A
        inner join cte_ancestors as C
        on
        c.casename = a.mot
)

1 ALY NULL ALY
2 AME ALY ALY
2 250 ALY ALY
2 AY9 ALY ALY
3 AME04 AME ALY
3 AME08 AME ALY
3 AME12 AME ALY
3 AME16 AME ALY
3 ANH AME ALY
3 405 AME ALY
3 ANN AME ALY
3 579 AME ALY
3 AVA AME ALY
4 AVA13 AVA ALY
4 AVA19 AVA ALY
4 ALF ANN ALY
4 ANN08 ANN ALY
4 ANN12 ANN ALY
4 ANN17 ANN ALY
4 ANH02 ANH ALY
4 ANH07 ANH ALY
4 AKI ANH ALY
4 ANH16 ANH ALY
4 ANH96 ANH ALY
4 ANH99 ANH ALY
5 ALF16 ALF ALY
5 ALF20 ALF ALY


So level 2 is a child of Aly, 3 is a grandchild of Aly, etc. The second column is the Mother of the given Case (whale)

Along with the level I would like to add the complete Lineage. The lineage for AME04 would be (AME04 - AME - ALY). When I try this I get a Type incompatibility.

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

Which I assume means that

CaseName as Lineage in the base will not UNION with "Lineage + a.CASENAME" in the recursive part

In the second part I even tried just a very simple concatenation of
Lineage + ' literal '
and get the same error.

I do not think this question is specific to the recursive CTE, but a general union question. Do I have to cast a concatenated string in this case? I tried to cast the string, but could not get the syntax correct.

 

[Minty]

There is an example of what you need here https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings
along with an excellent explanation!

 

[MajP]

@Minty,
Thanks, that nailed it.

WITH cte_Ancestors AS (
    SELECT       
      1 as Level,
      CASENAME,
      MOT,
      caseName as TOP_MOT,
      cast(CaseName AS nvarchar) as Lineage
    FROM       
        vw_Population
    WHERE casename = 'aly'
    UNION ALL
    SELECT
      Level + 1,
      A.CaseName,
      A.MOT,
      TOP_MOT,
      cast(Lineage + ' - ' + a.CASENAME AS nvarchar)
    FROM
        vw_Population as A
        inner join cte_ancestors as C
        on
        c.casename = a.mot
)
SELECT * FROM cte_ancestors order by 1;

                        Lineage
1    ALY    NULL    ALY      ALY
2    AME    ALY    ALY              ALY - AME
2    250    ALY    ALY            ALY - 250
2    AY9    ALY    ALY            ALY - AY9
3    AME04    AME    ALY        ALY - AME - AME04
3    AME08    AME    ALY        ALY - AME - AME08
3    AME12    AME    ALY        ALY - AME - AME12
3    AME16    AME    ALY        ALY - AME - AME16
3    ANH    AME    ALY            ALY - AME - ANH
3    405    AME    ALY            ALY - AME - 405

So for whale 405 the lineage is from Aly to AME to 405

Is this a more general issue that concatenated strings must get cast in order to union them?

 

[Minty]

Yes - any data that isn't a string must be converted/cast to a string data type for you to be able to concatenate it.
It's a bit of a bind (excuse the pun) but SQL is much more data type aware.

 

[Isaac]

Random additional thought.. and this is something I often forget and keep forgetting no matter how many times it gets me, you always have to make sure you don't have any nulls in your concatenation or the whole thing will always end up null... Quite the opposite of VBA where we sometimes concatenate things for the explicit purpose of forcing an implicitly created zero length string.