GPO (Grupo de Profissionais Oracle)

25 de julho de 2023 às 7:56 pm #168114

José Laurindo Chiappa

Moderador

Oi : novamente, nós só vamos poder Cravar algo quando tivermos os INSERTs para podermos ter uma massa de dados E tivermos, certinho e exato, o TEXTO DO SQL em questão, mas COM CERTEZA NÂO FAZ SENTIDO isso que vc diz que “o NVL não entende que o LEAD retorna DATE” (ou NULL, que ele PODE retornar também)….
Vamos a um exemplo : primeiro, entenda que a função analítica LEAD traz o PRÓXIMO valor (o valor da linha de baixo) de uma coluna (ou de uma expressão envolvendo colunas), DENTRO DE UM AGRUPAMENTO INDICADO PELO OVER (PARTITION …. , E o default é buscar uma linha só abaixo E quando a linha corrente é a última linha do grupo (e assim portanto NÂO EXISTE PRÒXIMA linha, aí ele traz null…
Suponha que eu tenho estes dados aqui :

SCOTT@xepdb1::CONTAINER=XEPDB1> select deptno, empno, ename, hiredate, sal from emp order by 1,2;

DEPTNO      EMPNO ENAME      HIREDATE                   SAL

    10       7782 CLARK      09/06/1981 00:00:00       2450
    10       7839 KING       17/11/1981 00:00:00       5000
    10       7934 MILLER     23/01/1982 00:00:00       1300
    20       7369 SMITH      17/12/1980 00:00:00        800
    20       7566 JONES      02/04/1981 00:00:00       2975
    20       7788 SCOTT                                3000
    20       7876 ADAMS      23/05/1987 00:00:00       1100
    20       7902 FORD       03/12/1981 00:00:00       3000
    30       7499 ALLEN      20/02/1981 00:00:00       1600
    30       7521 WARD       22/02/1981 00:00:00       1250
    30       7654 MARTIN     28/09/1981 00:00:00       1250
    30       7698 BLAKE      01/05/1981 00:00:00       2850
    30       7844 TURNER     08/09/1981 00:00:00       1500
    30       7900 JAMES      03/12/1981 00:00:00        950
    30       8000 CHIAPPA                             12000
    30       8001 VITOR                                6000

16 linhas selecionadas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

=> E suponha que eu quero via LEAD buscar a linha de baixa de uma ordenação de HIREDATE (data de contratação) nessa tabela de demonstração chamada EMP, eu poderia escrever :

SCOTT@xepdb1::CONTAINER=XEPDB1> select deptno, empno, ename, hiredate, lead(hiredate) over (partition by deptno order by deptno, empno) as data_seguinte_no_depto from emp;

DEPTNO      EMPNO ENAME      HIREDATE            DATA_SEGUINTE_NO_DE

    10       7782 CLARK      09/06/1981 00:00:00 17/11/1981 00:00:00
    10       7839 KING       17/11/1981 00:00:00 23/01/1982 00:00:00
    10       7934 MILLER     23/01/1982 00:00:00
    20       7369 SMITH      17/12/1980 00:00:00 02/04/1981 00:00:00
    20       7566 JONES      02/04/1981 00:00:00
    20       7788 SCOTT                          23/05/1987 00:00:00
    20       7876 ADAMS      23/05/1987 00:00:00 03/12/1981 00:00:00
    20       7902 FORD       03/12/1981 00:00:00
    30       7499 ALLEN      20/02/1981 00:00:00 22/02/1981 00:00:00
    30       7521 WARD       22/02/1981 00:00:00 28/09/1981 00:00:00
    30       7654 MARTIN     28/09/1981 00:00:00 01/05/1981 00:00:00
    30       7698 BLAKE      01/05/1981 00:00:00 08/09/1981 00:00:00
    30       7844 TURNER     08/09/1981 00:00:00 03/12/1981 00:00:00
    30       7900 JAMES      03/12/1981 00:00:00
    30       8000 CHIAPPA
    30       8001 VITOR

16 linhas selecionadas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==|> ok, no exemplo de cima dá pra ver bem as duas situações onde o LEAD manda um null, que são : quando a linha é a última do grupo E quando o valor já é null mesmo : porque a LEAD retornou NULL para o empregado 7934 chamado MILLER? PORQUE no PARTITION eu mandei agrupar por DEPTNO , e o MILLER é o ÙLTIMO EMPREGADO DO DEPTO 10, então NÃO TEM PRÓXIMO valor para depto 10…. E no caso do empregado 7788, o SCOTT, PERCEBA na query anterior que ele JÁ TEM MESMO null como o valor de HIREDATE, então a linha de cima dele (que é a linha do empregado JONES) retornou esse NULL que já existia mesmo…
Muito bem, quero que em caso de NULL seja por que motivo for, a coluna tenha o NULL transformado em TRUNC da data de hoje, olhaí :

SCOTT@xepdb1::CONTAINER=XEPDB1> select deptno, empno, ename, hiredate,
NVL(lead(hiredate) over (partition by deptno order by deptno, empno), trunc(sysdate)) as data_seguinte_no_depto from emp;

DEPTNO      EMPNO ENAME      HIREDATE            DATA_SEGUINTE_NO_DE

    10       7782 CLARK      09/06/1981 00:00:00 17/11/1981 00:00:00
    10       7839 KING       17/11/1981 00:00:00 23/01/1982 00:00:00
    10       7934 MILLER     23/01/1982 00:00:00 25/07/2023 00:00:00
    20       7369 SMITH      17/12/1980 00:00:00 02/04/1981 00:00:00
    20       7566 JONES      02/04/1981 00:00:00 25/07/2023 00:00:00
    20       7788 SCOTT                          23/05/1987 00:00:00
    20       7876 ADAMS      23/05/1987 00:00:00 03/12/1981 00:00:00
    20       7902 FORD       03/12/1981 00:00:00 25/07/2023 00:00:00
    30       7499 ALLEN      20/02/1981 00:00:00 22/02/1981 00:00:00
    30       7521 WARD       22/02/1981 00:00:00 28/09/1981 00:00:00
    30       7654 MARTIN     28/09/1981 00:00:00 01/05/1981 00:00:00
    30       7698 BLAKE      01/05/1981 00:00:00 08/09/1981 00:00:00
    30       7844 TURNER     08/09/1981 00:00:00 03/12/1981 00:00:00
    30       7900 JAMES      03/12/1981 00:00:00 25/07/2023 00:00:00
    30       8000 CHIAPPA                        25/07/2023 00:00:00
    30       8001 VITOR                          25/07/2023 00:00:00

16 linhas selecionadas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==> prontinho, todas as linhas onde o próximo valor capturado pelo LEAD eram NULL (ie, MILLER, JONES, FORD, JAMES, CHIAPPA, VITOR), o nvl transformou na DATA DE HOJE, truncada…
Aí vem o ponto matador : para PROVAR que realmente TANTO o LEAD só mandou DATE ou NULL, E QUE quando o LEAD mandou NULL o NVL ** transformou SIM ** em DATE (NÃO FAZ SENTIDO isso que vc supos, de NVL ou LEAD transformar o datatype para NUMBER!!), vou criar uma tabela AONDE a coluna transformada pelo NVL vai ser inserida num datatype DATE) :

SCOTT@xepdb1::CONTAINER=XEPDB1> create table TESTE_INS (deptno number(2), empno number(4), ename VARCHAR2(10), hiredate date, data_seguinte_no_depto date);
Tabela criada.

SCOTT@xepdb1::CONTAINER=XEPDB1> INSERT INTO TESTE_INS (select deptno, empno, ename, hiredate, NVL(lead(hiredate) over (partition by deptno order by deptno, empno), trunc(sysdate)) as data_seguinte_no_depto from emp);

16 linhas criadas.

SCOTT@xepdb1::CONTAINER=XEPDB1> select * from TESTE_INS;

DEPTNO      EMPNO ENAME      HIREDATE            DATA_SEGUINTE_NO_DE

    10       7782 CLARK      09/06/1981 00:00:00 17/11/1981 00:00:00
    10       7839 KING       17/11/1981 00:00:00 23/01/1982 00:00:00
    10       7934 MILLER     23/01/1982 00:00:00 25/07/2023 00:00:00
    20       7369 SMITH      17/12/1980 00:00:00 02/04/1981 00:00:00
    20       7566 JONES      02/04/1981 00:00:00 25/07/2023 00:00:00
    20       7788 SCOTT                          23/05/1987 00:00:00
    20       7876 ADAMS      23/05/1987 00:00:00 03/12/1981 00:00:00
    20       7902 FORD       03/12/1981 00:00:00 25/07/2023 00:00:00
    30       7499 ALLEN      20/02/1981 00:00:00 22/02/1981 00:00:00
    30       7521 WARD       22/02/1981 00:00:00 28/09/1981 00:00:00
    30       7654 MARTIN     28/09/1981 00:00:00 01/05/1981 00:00:00
    30       7698 BLAKE      01/05/1981 00:00:00 08/09/1981 00:00:00
    30       7844 TURNER     08/09/1981 00:00:00 03/12/1981 00:00:00
    30       7900 JAMES      03/12/1981 00:00:00 25/07/2023 00:00:00
    30       8000 CHIAPPA                        25/07/2023 00:00:00
    30       8001 VITOR                          25/07/2023 00:00:00

16 linhas selecionadas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

taí, c.q.d., como queríamos demonstrar…. quando vc nos der uma massa de testes faremos um teste similar a este….