connect by level does not adjust with the change of the field in each row

user_1205746 0
Viewed 62

Let me start my question by setting up my scenario.

I have a test2 table which contains only 2 fields: productid and productlife, I would like to explicitly list all the years along with the products

![enter image description here

for example,

With product A, I would like to list 

Y15|Y16|Y17|Y18|Y19, A

and for product B, using the same rule, I should get

Y18|Y19, B

My sql does not produce the result I am looking for:

SELECT
  (
  SELECT listagg("Year",'|') within GROUP (
  ORDER BY "Year") "Prefix"
  FROM
    (
    SELECT 'Y'
      ||(TO_CHAR(SYSDATE,'yy')-LEVEL) "Year"
    FROM dual
      CONNECT BY level<=r.productlife
    )
  ) "Prefix", productid
FROM TEST2 r

How should it be corrected? I would think the field productlife in each record will control the level in the statement but it does not seem to do so..

Please advise.

Below is the script to create my example for your convenience.

CREATE TABLE "TEST2" 
(   productid VARCHAR2(20 BYTE), 
    productlife NUMBER
   )  ;
Insert into TEST2 (productid,productlife) values ('A',5);
Insert into TEST2 (productid,productlife) values ('B',2);

Thanks!

oracle sql
2 Answers

This query will do it, but I feel like there must be a better way:

SELECT t.productid, s.yr
FROM test2 t
INNER JOIN (SELECT ROWNUM RN, TO_CHAR(SYSDATE, 'YY')-ROWNUM+1 YR
            FROM dual d
            CONNECT BY level <= (SELECT max(productlife)
                                 FROM test2)) s ON t.productlife >= s.rn
ORDER BY t.productid, s.yr;

Here is a SQLFiddle for you: SQLFiddle

user_22997600

If it doesn't absolutely have to use connect by, another approach might be to write it explicitly as a function:

with
    function list_years(n number) return varchar2 as
        end_year   date := trunc(sysdate,'YYYY');
        start_year date := add_months(end_year, (n*-12));
        y date;
        years_list varchar(200);
    begin
        for i in reverse 1..n loop
            y := add_months(sysdate, -12 * i);
            years_list := years_list || to_char(y,'"Y"YY"|"');
        end loop;

        return rtrim(years_list,'|');
    end list_years;
select productid
     , productlife
     , list_years(productlife) as prefix
from   test2
/

DBFiddle

user_2304710
Related