Problem with recursive SQL join in DB2
I am relatively new to writing SQL and cannot get the correct output from my recursive statement. The original table looks something like this:
SEQUENCE TEXT CATEGORY
1 This is apples
2 a complete apples
3 sentence. apples
The result I'm trying to get:
CATEGORY TEXT
apples This is a complete sentence.
However, I get the result:
CATEGORY TEXT
apples This is a complete
I cannot essentially concatenate more than two lines. I think it has something to do with the count I set in the recursive part of my WITH statement.
WITH rquery (category, sequence, sentence)
AS (SELECT base.category, base.sequence, base.text
FROM myTable base
WHERE sequence = 1
Union ALL
SELECT t1.category, t1.sequence, sentence || ' ' || t1.text
FROM rquery t0, myTable t1
WHERE t0.category = t1.category And t0.sequence + 1 = t1.sequence
)
SELECT * FROM rquery WHERE sequence in (SELECT MAX(sequence) FROM rquery);
If you remove the last WHERE clause, the results show what's going on, but I can't seem to find a solution to get it right. Here without the last WHERE clause:
CATEGORY SEQUENCE SENTENCE
apples 1 This is
apples 2 This is a complete
apples 3 This is a complete
source to share
Depending on how the TEXT is defined, the final value might be truncated, and then, depending on the client used to run the request, you never know.
Try to distinguish TEXT
to a longer one VARCHAR
, for example
WITH rquery (category, sequence, sentence)
AS (SELECT base.category, base.sequence, CAST( base.text AS VARCHAR(100))
FROM myTable base
...
The resulting datatype sentence
must be large enough to fit the largest possible concatenation of TEXT
s.
source to share