CREATE OR ALTER VIEW VWGRADE(
    ID,
    DESCRICAO,
    TAMANHO)
AS
select
    ID,
    DESCRICAO,
    list(TAMANHO,'|') as TAMANHO
from(
    select
        COD_GRADE as ID,
        DESCRICAO as DESCRICAO,
        TAMANHO,
        row_number() over(order by cod_grade, ordem) as LINHA
    from(
        select
            g.cod_grade,
            g.descricao,
            t.tamanho,
            fn_lpad(cast('0'||FN_REMOVER_LETRA(coalesce(o.id, t.tamanho)) as integer),2,'0') ordem
        from grades g, tamanhos t
        left join TBGRADE_TEMP o on o.desc = t.tamanho
        where g.grade = t.grade
        order by g.cod_grade, t.ctamanho
    ) order by cod_grade, ordem

) group by 1,2
;



CREATE OR ALTER VIEW TBGRADE(
    DESCRICAO,
    CODIGO,
    TOTAL_TAMANHOS,
    STATUS,
    T01,
    T02,
    T03,
    T04,
    T05,
    T06,
    T07,
    T08,
    T09,
    T10,
    T11,
    T12,
    T13,
    T14,
    T15,
    T16,
    T17,
    T18,
    T19,
    T20)
AS
select
        descricao,
        codigo,
        coalesce((select count(osplit) from split(GRADE,'|')),0) total_tamanhos,
        1 status,
        fn_array(GRADE, 1,'|') as t01,
        fn_array(GRADE, 2,'|') as t02,
        fn_array(GRADE, 3,'|') as t03,
        fn_array(GRADE, 4,'|') as t04,
        fn_array(GRADE, 5,'|') as t05,
        fn_array(GRADE, 6,'|') as t06,
        fn_array(GRADE, 7,'|') as t07,
        fn_array(GRADE, 8,'|') as t08,
        fn_array(GRADE, 9,'|') as t09,
        fn_array(GRADE,10,'|') as t10,
        fn_array(GRADE,11,'|') as t11,
        fn_array(GRADE,12,'|') as t12,
        fn_array(GRADE,13,'|') as t13,
        fn_array(GRADE,14,'|') as t14,
        fn_array(GRADE,15,'|') as t15,
        fn_array(GRADE,16,'|') as t16,
        fn_array(GRADE,17,'|') as t17,
        fn_array(GRADE,18,'|') as t18,
        fn_array(GRADE,19,'|') as t19,
        fn_array(GRADE,20,'|') as t20

from(
    select
        s.id as codigo,
        s.descricao,
        s.tamanho as GRADE
    from vwgrade s
)
;


