I have a working recursive query. I want to add another column, but it gives me an error:
Here is the working one:
*****************************
WITH RECURSIVE x(jina, namba, nasaba_1) AS (
SELECT (((majina2.jina::text || ' '::text) || majina2.baba::text) || ' '::text) || majina2.babu::text AS jina,
majina2.namba,
majina2.nasaba_1,
majina2.kuzaliwa,
majina2.anza_mchango,
majina2.mwisho_mchango
FROM majina2
WHERE majina2.nasaba_1 = 0
UNION ALL
SELECT ((((((x_1.jina || ' '::text) || ' - '::text) || e.jina::text) || ' '::text) || e.baba::text) || ' '::text) || e.babu::text,
e.namba,
e.nasaba_1,
e.kuzaliwa,
e.anza_mchango,
e.mwisho_mchango
FROM majina2 e,
x x_1
WHERE e.nasaba_1 = x_1.namba
)
SELECT jina,
namba,
nasaba_1,
(length(jina) - length(replace(jina, '-'::text, ''::text))) / length('-'::text) AS depth,
kuzaliwa,
anza_mchango,
mwisho_mchango
FROM x
ORDER BY jina;
************************
But when I add these lines:
(((majina2.jina::text || ' '::text) || majina2.baba::text) || ' '::text) || majina2.babu::text AS jina_2,
the upper part when executed separately its works:
When adding this in the UNION part: (((e.jina::text || ' '::text) || e.baba::text) || ' '::text) || e.babu::text AS jina_2,
I get the following error:
ERROR: operator does not exist: integer = text
LINE 21: WHERE e.nasaba_1 = x_1.namba
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
Any suggestion of where I am doing it wrong?
Thanks in advance
Ibrahim Shaame
