DB2 With in clause, cache variable

Question

According to the example of Generate aggregated Json data from Json string in java, I want to cache the result length in a variable within a function

DECLARE result CLOB;
DECLARE leng INT;

    WITH BASE AS ( 
    select id, item, 
      JSON_OBJECT('item' value item, 
                   'itemScore' value itemScore,
                   'stage' value stage,
                   'reco' VALUE JSON_OBJECT('product' value product,
                                            'url' value url ,
                                            'score' value score 
                                            FORMAT JSON ) 
                    FORMAT JSON ABSENT ON NULL  
                   RETURNING VARCHAR(200) FORMAT JSON) ITEM_JSON  
    FROM PROD_T  ),  
    SIZE AS (SELECT COUNT(*) AS SIZ INTO leng FROM BASE),--not working
     PROD_OBJS AS ( 
         SELECT  JSON_OBJECT ( KEY 'id' VALUE ID , 
                KEY 'itens' VALUE 
                       JSON_ARRAY ( LISTAGG( ITEM_JSON , ', ') WITHIN GROUP (ORDER BY ITEM) FORMAT JSON ) 
                FORMAT JSON ) json_objects 
         FROM BASE GROUP BY ID ) 
    SELECT JSON_ARRAY (select json_objects FROM PROD_OBJS format json) INTO result FROM SYSIBM.SYSDUMMY1;

I tried this one, see the line above, but it is not working

SIZE AS (SELECT COUNT(*) AS SIZ INTO leng FROM BASE)

Answer 1

A single INTO is allowed in a SELECT statement only.
Run it as is to check and uncomment the commented out block in your function (removing the VALUES block obviously to work with your real PROD_T table).

    WITH BASE AS ( 
    select id, item, 
      JSON_OBJECT('item' value item, 
                   'itemScore' value itemScore,
                   'stage' value stage,
                   'reco' VALUE JSON_OBJECT('product' value product,
                                            'url' value url ,
                                            'score' value score 
                                           ) 
                   ABSENT ON NULL  
                   RETURNING VARCHAR(200) FORMAT JSON) ITEM_JSON  
    FROM 
    (
      VALUES 
        ('id1', 'item1', 'itemScore1', 'stage1', 'product1', 'url1', 'score1')
      , ('id2', 'item2', 'itemScore2', 'stage2', 'product2', 'url2', 'score2')  
    )
    PROD_T  (id, item, itemScore, stage, product, url, score)
    ),  
     PROD_OBJS AS ( 
         SELECT  JSON_OBJECT ( KEY 'id' VALUE ID , 
                KEY 'itens' VALUE 
                       JSON_ARRAY ( LISTAGG( ITEM_JSON , ', ') WITHIN GROUP (ORDER BY ITEM) FORMAT JSON ) 
                FORMAT JSON ) json_objects 
         FROM BASE GROUP BY ID ) 
    SELECT 
      JSON_ARRAY (select json_objects FROM PROD_OBJS format json) 
    , (SELECT COUNT(*) FROM BASE)
    --INTO result, leng
    FROM SYSIBM.SYSDUMMY1