- Este tópico contém 22 respostas, 3 vozes e foi atualizado pela última vez 1 ano, 4 meses atrás por
ESSanches.
ESSanches.-
AutorPosts
-
21 de julho de 2023 às 10:47 am #167909
ESSanchesParticipanteBom dia pessoal.
Desenvolvi um select pra usar como fonte em uma instrução MERGE, segue o select:
SELECT A.CUS_FAM FAM, A.CUS_DTA DMO_INI, NVL(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM ORDER BY A.CUS_FAM, A.CUS_DTA) - 1,TO_DATE(SYSDATE,'DD/MM/YYYY')) DMO_FIN, A.CUS_VLT_RTO VLT_RTO, A.CUS_VLU_RTO VLU_RTO, A.CUS_VLT_RMO VLT_RMO, A.CUS_VLU_RMO VLU_RMO FROM SGI5_TAB_CUS_REP_RAT AO select roda muito bem dentro de um bloco, mas quando coloco ele dentro de um merge, segue o comando:
MERGE INTO SGI5_TAB_CUS_RET_RAT X
USING(
SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
NVL(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM
ORDER BY A.CUS_FAM,
A.CUS_DTA) – 1,TO_DATE(SYSDATE,’DD/MM/YYYY’)) DMO_FIN,
A.CUS_VLT_RTO VLT_RTO,
A.CUS_VLU_RTO VLU_RTO,
A.CUS_VLT_RMO VLT_RMO,
A.CUS_VLU_RMO VLU_RMO
FROM SGI5_TAB_CUS_REP_RAT A
) Y ON (X.CUS_FAM = Y.FAM AND
X.CUS_DMO BETWEEN Y.DMO_INI AND Y.DMO_FIN)
WHEN MATCHED THEN
UPDATE SET X.CUS_VLT_RTO = Y.VLT_RTO,
X.CUS_VLU_RTO = Y.VLU_RTO,
X.CUS_VLT_RMO = Y.VLT_RMO,
X.CUS_VLU_RMO = Y.VLU_RMO;ao tentar rodar, retorna o seguinte erro: ORA-00932: inconsistent datatypes expected NUMBER got DATE.
Consegui verificar que o erro ocorre na conversao da função NVL. Ao que parece, a função LEAD esta retornando um numero
e o NVL esta devolvendo uma data. Consegui resolver isso convertendo o retorno da função LEAD para string, tratando o nulo,
e reconvertendo para data depois (veja código abaixo).Funcionou, mas achei um gambiarra danada!! rs.
Alguma alma caridosa poderia me dar uma luz do que esta acontecendo?
Lembrando que o campo da tabela envolvida no LEAD é uma data. E lembrando tb que não é um erro recorrente, tenho outros codigos semelhantes mas
envolvendo outras tabela que não retornam erro. E lembrando tb que isso só ocorreu (até agora) com o LEAD é usando com um campo data.Usando Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0.
Muito obrigado.
MERGE INTO SGI5_TAB_CUS_RET_RAT X
USING(
SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
TO_DATE(NVL(TO_CHAR(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM
ORDER BY A.CUS_FAM,
A.CUS_DTA) – 1,’DD/MM/YYYY’),
TO_CHAR(TRUNC(SYSDATE),’DD/MM/YYYY’)),
‘DD/MM/YYYY’) DMO_FIN,
A.CUS_VLT_RTO VLT_RTO,
A.CUS_VLU_RTO VLU_RTO,
A.CUS_VLT_RMO VLT_RMO,
A.CUS_VLU_RMO VLU_RMO
FROM SGI5_TAB_CUS_REP_RAT A
) Y ON (X.CUS_FAM = Y.FAM AND
X.CUS_DMO BETWEEN Y.DMO_INI AND Y.DMO_FIN)
WHEN MATCHED THEN
UPDATE SET X.CUS_VLT_RTO = Y.VLT_RTO,
X.CUS_VLU_RTO = Y.VLU_RTO,
X.CUS_VLT_RMO = Y.VLT_RMO,
X.CUS_VLU_RMO = Y.VLU_RMO;23 de julho de 2023 às 2:04 pm #167960José Laurindo ChiappaModeradorTudo jóia : Numa primeira olhada, de cara SALTA AOS OLHOS esse trecho aqui :
… TO_DATE(SYSDATE,’…..
==>> ISSO É UMA BESTEIRA, é um BUG NO SEU CÓDIGO!!!! TO_DATE de um valor QUE JÁ É DATA é ILÓGICO, vc está FATALMENTE FORÇANDO o Oracle a fazer uma conversão IMPlÍCITA, pois a documentação oficial Oracle já MUITO CLARAMENTE diz em https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYSDATE.html#GUID-807F8FC5-D72D-4F4D-B66D-B0FE1A8FA7D2 que a função DATE JÁ RETORNA UM VALOR DO DATATYPE DATE, e https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_DATE.html#GUID-D226FA7C-F7AD-41A0-BB1D-BD8EF9440118 mostra que a função TO_DATE ** SÓ ACEITA DATEs ** , então para equalizar teu TO_DATE(SYSDATE o Oracle VAI ESTAR CONVERTENDO IMPLICITAMENTE essa data do SYSDATE para uma STRING a fim de fazer o TO_DATE funcionar, e conversão IMPLÍCITA é um inferno porque DEPENDE DOS SETTINGs da sessão…. SIMPLESMENTE EVITE ISSO….
E pelo que eu vejo, essa conversão implícita não parece ser a única, esse teu código inicial tá REPLETO de chamadas à TO_DATE e a TO_CHAR, acho que TEM MAIS PONTOS aonde vc PRECISA EVITAR CONVERSÃO IMPLÍCITA!!!!! E OUTRO PONTO, a diferença entre um SELECT que traz dados para um MERGE e um SELECT simples é que o primeiro VAI usar esses dados lidos para alimentar um INSERT e um UPDATE implícitos : pelo que eu vi (a não ser que haja trigger de DML OU valores default entrando em ação) esses valores que vc bem estranhamente pede conversão no SELECT não são usados no INSERT e no UPDATE do MERGE, mas TEM QUE CONFIRMAR se é isso mesmo : SE FOR O CASO, manda aqui o DESCRIBE e um CREATE TABLE junto com uns poucos INSERTs dessas tais tabelas SGI5_TAB_CUS_REP_RAT e SGI5_TAB_CUS_RET_RAT pra confirmar o DATATYPE dessa coluna CUS_DTA : a única maneira da função LEAD retornar número ao invés de DATA é se OU o datatype da coluna NÂO FOR DATE , OU (muito possível) se esse festival de conversão aí que vc usa tá gerando conversão implícita….
ok ?
Abraços,
Chiappa
24 de julho de 2023 às 8:44 am #168014ESSanchesParticipanteBom dia Chiappa, muito obrigado pela resposta! Vamos lá. Você tem toda razão, meu código esta bem confuso, realmente
não é necessário transformar implicitamente uma data em uma data!!! Como fiz no código abaixo:TO_DATE(NVL(TO_CHAR(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM ORDER BY A.CUS_FAM, A.CUS_DTA) - 1,'DD/MM/YYYY'), TO_CHAR(TRUNC(SYSDATE),'DD/MM/YYYY')), 'DD/MM/YYYY')O problema é que tentei colocar o código abaixo, esse sim sem conversões implícitas desnecessárias, segue o código abaixo:
NVL(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM ORDER BY A.CUS_FAM, A.CUS_DTA) - 1,TRUNC(SYSDATE))Mas é exatamente ai que esta o problemas, essa chamada a função LEAD, quando executada como um bloco, funciona perfeitamente, mas quando eu a coloco
dentro de um comando MERGE gera o erro mencionado na postagem inicial.O que fiz no código que vc citou foi uma completa gambiarra pra fazer o código funcionar, portando o código com muitas conversões implícitas É O QUE FUNCIONA.
Gostaria de saber, se for possível, pq o código sem conversões implícitas NAO funciona. Como vc mesmo disse, não existe possibilidade da função LEAD retornar um numero de uma coluna definida como data.Segue o describe das tabelas, como pedido. Não enviei o create table e o insert de dados pq nao sei como colocar um arquivo anexo aqui…
SQL> desc SGI5_TAB_CUS_REP_RAT
Name Type Nullable Default Comments
CUS_FAM NUMBER Familia
CUS_DTA DATE Dta do rateio
CUS_DIA NUMBER(4) Y 0 Dias de producao usados no calculo
CUS_PRO NUMBER(20) Y 0 Qtde produzida
CUS_PPO NUMBER(20,6) Y 0 Producao ponderada
CUS_VLT_RTO NUMBER(20,6) Y 0 Vlr total do rateio total
CUS_VLU_RTO NUMBER(20,6) Y 0 Vlr unitario do rateio total
CUS_VLT_RMO NUMBER(20,6) Y 0 Vlr total do rateio de mao de obra
CUS_VLU_RMO NUMBER(20,6) Y 0 Vlr unitario do rateio de mao de obra
CUS_USU NUMBER Y null Usuario da ultima atualizacao
CUS_DUA DATE Y null Data da ultima atualizacao
CUS_DBA DATE Y Data base atualizacaoSQL> desc SGI5_TAB_CUS_RET_RAT
Name Type Nullable Default Comments
CUS_FAM NUMBER Familia
CUS_DMO DATE Dta do movto
CUS_DIA NUMBER(4) Y 0 Dias processados
CUS_PDI NUMBER(10) Y 0 Producao diaria
CUS_PAC NUMBER(10) Y 0 Producao acumulada nos ultimos X dias
CUS_PPO NUMBER(20,6) Y 0 Producao ponderada
CUS_VLT_RTO NUMBER(20,6) Y 0 Vlr total do rateio total
CUS_VLU_RTO NUMBER(20,6) Y 0 Vlr unitario do rateio total
CUS_VLT_RMO NUMBER(20,6) Y 0 Vlr total do rateio de mao de obra
CUS_VLU_RMO NUMBER(20,6) Y 0 Vlr unitario do rateio de mao de obra
CUS_DIP DATE Y Dta de inicio do processamento
CUS_DFI DATE Y Dta de fim do processamento
CUS_SEM NUMBER Y TO_NUMBER(TO_CHAR(“CUS_DMO”,’YYYYWW’)) Ano/Semana do movto
CUS_MES NUMBER Y TO_NUMBER(TO_CHAR(“CUS_DMO”,’YYYYMM’)) Ano/Mes do movtoEspero te me expressado um pouco melhor agora e muitíssimo obrigado pela atenção.
Emerson
PS: Segue o código do comando MERGE que NÃO esta funcionando. Veja que não existe NENHUMA conversão de data implícita nele.
MERGE INTO SGI5_TAB_CUS_RET_RAT X
USING(SELECT A.CUS_FAM FAM, A.CUS_DTA DMO_INI, NVL(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM ORDER BY A.CUS_FAM, A.CUS_DTA) - 1,TRUNC(SYSDATE)) DMO_FIN, A.CUS_VLT_RTO VLT_RTO, A.CUS_VLU_RTO VLU_RTO, A.CUS_VLT_RMO VLT_RMO, A.CUS_VLU_RMO VLU_RMO FROM SGI5_TAB_CUS_REP_RAT A WHERE A.CUS_DTA >= &V_DTA ) Y ON (X.CUS_FAM = Y.FAM AND X.CUS_DMO BETWEEN Y.DMO_INI AND Y.DMO_FIN) WHEN MATCHED THEN UPDATE SET X.CUS_VLT_RTO = Y.VLT_RTO, X.CUS_VLU_RTO = Y.VLU_RTO, X.CUS_VLT_RMO = Y.VLT_RMO, X.CUS_VLU_RMO = Y.VLU_RMO;24 de julho de 2023 às 4:25 pm #168047José Laurindo ChiappaModeradorBom, a principal suspeita aqui é essa linha :
A.CUS_DTA = V_DTA
Pelo que entendi, essa V_DTA (já que não a vi citada em nenhum lugar abaixo) é uma VARIÁVEL BIND : essa variável BIND ESTÁ criada como DATE ou Não ?? Se não estiver, aí está SIM a porta de entrada para conversão implícita…
De resto, vi que provavelmente vc esbarrou nalgum limite do software do forumn parece que pulou linha no meio : como o seu SQL é um pouquinho grande, sugiro vc criar uma conta gratuita no site da Oracle e deixar teu SQL num LiveSQL, https://livesql.oracle.com/apex/f?p=590:1000 é o link , OU então compartilhar o texto num dos muitos sites de compartilhamento de código grátis, como https://pastebin.com/ , https://sharetxt.live/default ou https://pastelink.net/share-text-online/temporary-private-text-share …. E se vc puder já botar uns INSERTs além do CREATE TABLE< facilita bastante pra gente tentar reproduzir…
24 de julho de 2023 às 9:36 pm #168058MottaParticipanteMas QUAL O PROBLEMA em si ?
O que o código deveria fazer ?
Pessoalnente evito MERGEs , mas é pessoal o bichinho funciona.
Parece ser um totalizador.
Em geral faço
Cursor
Loop
_update xxxx
_if count=0
__insert
_end if
End loopSem entrar no mérito das conversões que também evito deixar implícitas, sempre deckarando formatos etc.
25 de julho de 2023 às 1:10 pm #168099José Laurindo ChiappaModeradorBlz, Motta ? Então, PMFJI mas o ESSanches já respondeu as suas perguntas em publicações anteriores desta mesma thread, e pelo que eu vi , o que ele quer é fazer um UPSERT (ie, obter os dados de um SELECT e se os dados lidos não existem numa determinada tabela fazer um INSERT com os dados lidos e se existir é feito um UPDATE : isso é a DEFINIÇÂO do que faz o comando MERGE, aí por isso ele usou MERGE ao invés de cursor – claro, SEMPRE é POSSÍVEL se escrever um bloco PL/SQL que via cursor e IFs faça o mesmo (até porque o MERGE ** não existiu sempre **, antes de ele ser introduzido era EXATAMENTE ESSA a alternativa que usávamos)…
Quanto ao erro, a msg de erro é clara :ORA-00932: inconsistent datatypes expected NUMBER got DATE
==> OU SEJA, em algum ponto (seja na query que alimenta o MERGE, seja nos INSERTs ou nos UPDATEs executados pelo MERGE, está vindo um datatype inesperado e SIM , tranquilamente isso PODE SER DEVIDO à conversão implícita : não dá pra CRAVAR à seco que é isso, mas é muito MUITO provável que seja isso…
E para podermos tentar ajudar o colega a identificar EXATAMENTE O PONTO / EXATAMENTE A COLUNA que está causando o erro, eu pedi uns INSERTs com alguns dados, junto com o exato texto do MERGE : quando recebermos isso, pode ser que executando no sqlplus consigamos ver um * exatamente abaixo da linha que deu o erro, OU então podemos ativar um TRACE num banco de estudos pra tentar debugar isso…
25 de julho de 2023 às 3:16 pm #168106ESSanchesParticipantePessoal, desculpe a demora nas respostas, mas estou com problemas na postagem no forum.
att
25 de julho de 2023 às 3:21 pm #168107ESSanchesParticipanteRealmente não estou conseguindo postar o codigo da procedure toda aqui. Acho que deve um limite de caracteres, não sei. Parece que so postagens curtas estão indo…….
Mas, o problema gente esta no uso da função NVL no tratamento do retorno da função LEAD. Não sei pq, o oracle não entende que o retorno do LEAD é um data e gera um erro no tratamento do null da função NVL.Vou postar o codigo em algum site sugerido pelo Chiappa e volto aqui……
Mas, por enquanto, muito obrigado pelas respostas…….
25 de julho de 2023 às 7:56 pm #168114José Laurindo ChiappaModeradorOi : 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 600016 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 VITOR16 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:0016 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:0016 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….
26 de julho de 2023 às 9:44 am #168151ESSanchesParticipanteBom dia.
Chiappa, usando o exemplo do select que vc fez, como você colocaria uma data nesse null retornado no campo ” DATA_SEGUINTE_NO_DE”?
DEPTNO EMPNO ENAME HIREDATE DATA_SEGUINTE_NO_DE
10 7934 MILLER 23/01/1982 00:00:00Não seria com a função NVL? Pois é exatamente aí onde o erro que reportei acontece.
Como disse em minha postagem inicial, o erro só acontece quando o select está dentro de um MERGE. Se colocar o NVL tratando o retorno de um LEAD em um consulta comum, funciona!!!Coloquei o código completo no link https://pastelink.net/nkmbmwow.
Tem o código completo da procedure, o script de criação das tabelas e os scripts de inserção de dados.
Para a tabela destino, coloquei scripts para inserção de 100 linhas. Para a tabela fonte, coloquei todos os dados (aprox. 700 linhas) pois achei que seria mais fácil para análise da lógica do código.
28 de julho de 2023 às 7:02 am #168248José Laurindo ChiappaModeradorBlz ? então, asap vou criar as tabelas e inserir os dados que vc forneceu, mas sobre a sua última pergunta : SIM, *** releia ** o segundo SELECT que passei, eu uso SIM o NVl, veja a linha :
NVL(lead(hiredate) over (partition by deptno order by deptno, empno), trunc(sysdate)) as data_seguinte_no_depto
==> Plz VEJA que no resultado desse segundo SELECT , para a linha do MILLER que vc questiona a coluna “DATA_SEGUINTE_NO_DEPTO” (o sqlplus por default corta o Título do campo se ele for maior que o dado, mas sem probs) , que usa SIM um NVL, retornou :
10 7934 MILLER 23/01/1982 00:00:00 25/07/2023 00:00:00
=> e essa data de “25/07/2023 00:00:00” é JUSTAMENTE O TRUNC DO SYSDATE, eu rodei esse exemplo no dia 25….. OU SEJA, funciona SIM, e PROVANDO que ele está SIM retornando um valor do datatype DATE, eu Criei uma tabela onde essa coluna é do DATATYPE DATE e inseri o retorno desse SELECt nessa tabela criada….
COM ABSOLUTA CERTEZA tem algum enrosco no seu código, talvez parêntesis fora de lugar, talvez erro de lógica… Vou deixar pra dizer algo de mais concreto só DEPOIS que ter os dados e rodar teu código, MAS mesmo numa olhada por cima de um minuto, sem executar nada, me parece ter visto algumas coisas em aberto : por exemplo ,vi que seu código TRAZ DADOS DE UMA (suponho) view chamada SGI5_VIEW_FAMILIAS_PAC : essa View ** ESTÁ ** criada com os datatypes CORRETOS ?????? Por falar nisso, o CREATE dela ** ESTÁ ** presente nesse scriptão aí ???
Quando a gente programa em PL/SQL, vc TEM QUE ESTAR CIENTE de TODOS os objetos que o PL/SQl referencia/depende, pois o PL/SQL faz compilação pré-execução, OU SEJA : é ANTES da execução que o compilador do PL/SQL checa se existem e se vc tem permissão EM TODOS OS OBJETOS QUE O CÓDIGO PL/SQL vai usar….Abraços,
Chiappa
28 de julho de 2023 às 10:42 am #168251ESSanchesParticipanteBom dia.
Chiappa entendo seu questionamento. O lance da view SGI5_VIEW_FAMILIAS_PAC, repare que ela é usada e NÃO gera erro na execução. O erro esta no MERGE.O que fico intriga é que ser rodar o codigo abaixo, vai de boa:
SELECT A.CUS_FAM FAM, A.CUS_DTA DMO_INI, NVL(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM ORDER BY A.CUS_FAM, A.CUS_DTA) - 1,TRUNC(SYSDATE)) DMO_FIN, A.CUS_VLT_RTO VLT_RTO, A.CUS_VLU_RTO VLU_RTO, A.CUS_VLT_RMO VLT_RMO, A.CUS_VLU_RMO VLU_RMO FROM SGI5_TAB_CUS_REP_RAT A WHERE A.CUS_DTA >= V_DTARetorna a data atual pro campo DMO_FIN, de boas.
Mas quando coloco essa mesma select como fonte dentro de um MERGEMERGE INTO SGI5_TAB_CUS_RET_RAT X
USING(
SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
TO_DATE(NVL(TO_CHAR(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM
ORDER BY A.CUS_FAM,
A.CUS_DTA) – 1,’DD/MM/YYYY’),
TO_CHAR(TRUNC(SYSDATE),’DD/MM/YYYY’)),
‘DD/MM/YYYY’) DMO_FIN,
A.CUS_VLT_RTO VLT_RTO,
A.CUS_VLU_RTO VLU_RTO,
A.CUS_VLT_RMO VLT_RMO,
A.CUS_VLU_RMO VLU_RMO
FROM SGI5_TAB_CUS_REP_RAT A
WHERE A.CUS_DTA >= V_DTA
) Y ON (X.CUS_FAM = Y.FAM AND
X.CUS_DMO BETWEEN Y.DMO_INI AND Y.DMO_FIN)
WHEN MATCHED THEN
UPDATE SET X.CUS_VLT_RTO = Y.VLT_RTO,
X.CUS_VLU_RTO = Y.VLU_RTO,
X.CUS_VLT_RMO = Y.VLT_RMO,
X.CUS_VLU_RMO = Y.VLU_RMO;Gera um erro na execução, dentro ou fora de uma procedure. Rapare que, fora da procedure que passei, não há menção a view SGI5_VIEW_FAMILIAS_PAC nesse código, e ainda assim, gera erro.
Sei que o erro é estranho, pode ser que esteja fazendo besteira (o mais provável)! Mas o que é mais estranho é que quando faço as gambiarras convertendo para string e reconvertendo para data, funciona!!! Se fosse um problema na view, não funcionaria, já que não mexi nela.
29 de julho de 2023 às 8:57 pm #168318José Laurindo ChiappaModeradorEntão: PRIMEIRO os dois SELECTs absolutamente não são os mesmos, no SQL que roda ok isolado vc escreveu :
SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
NVL(LEAD(A.CUS_DTA,1) OVER ….ENQUANTO QUE no SQL do MERGE , vc escreveu :
SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
TO_DATE(NVL(TO_CHAR(LEAD(A.CUS_DTA,1) OVER…..==> Não São Iguais, não sr, esse TO_DATE de TO_CHAR muda-os Completamente ….
Segundo, Como eu já tinha dito, olhando esse código SQL isolado, sem estar num bloco :
MERGE INTO SGI5_TAB_CUS_RET_RAT X
USING(
SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
TO_DATE(NVL(TO_CHAR(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM
ORDER BY A.CUS_FAM,
A.CUS_DTA) – 1,’DD/MM/YYYY’),
TO_CHAR(TRUNC(SYSDATE),’DD/MM/YYYY’)),
‘DD/MM/YYYY’) DMO_FIN,
A.CUS_VLT_RTO VLT_RTO,
A.CUS_VLU_RTO VLU_RTO,
A.CUS_VLT_RMO VLT_RMO,
A.CUS_VLU_RMO VLU_RMO
FROM SGI5_TAB_CUS_REP_RAT A
WHERE A.CUS_DTA >= V_DTA
) Y ON (X.CUS_FAM = Y.FAM AND
X.CUS_DMO BETWEEN Y.DMO_INI AND Y.DMO_FIN)
WHEN MATCHED THEN
UPDATE SET X.CUS_VLT_RTO = Y.VLT_RTO,
X.CUS_VLU_RTO = Y.VLU_RTO,
X.CUS_VLT_RMO = Y.VLT_RMO,
X.CUS_VLU_RMO = Y.VLU_RMO;*** PERCEBA ** que esses identificador V_DTA ao que parece (julgando pelos CREATEs) ** não existe ** dentro de nenhuma das tabelas, ENTÃO o SQL vai considerar que isso é uma VARIÁVEL… Como essa variável NÂO FOI CRIADA antes de rodar o SQL isoladamente ao que vejo, COMO É que vc está conseguindo rodar SEM receber um erro :
Erro de SQL: ORA-00904: “V_DATA”: identificador inválido
pela frente ??? Alguma coisa está MUITO errada, Alguma coisa vc está OCULTANDO da gente….
29 de julho de 2023 às 8:59 pm #168319José Laurindo ChiappaModeradorBom, vou tentar rodar o CREATE e os INSERts e vamos ver o que veremos…
INCLUSIVE, vi lá que o texto no pastebin termina em :
“insert into ….
insert into SGI5_TAB_CUS_REP_R”OU SEJA, pelo jeito vc NÂO SEGUIU a minha recomendação de passar só uns poucos MÍNIMOS INSERTs e quis enfiar TODOS OS DADOS que vc tinha, aí vc ULTRAPASSOU o limite do site… Mas sem prob, vou rodar esses INSERTs que couberam e vamos ver….
OBSERVAÇÃO : vc não respondeu MAS ESPERO que tenha ficado CLARO na minha resposta anterior, que vc tenha ENXERGADO que no meu segundo SELECT eu usei SIM o NVL no LEAD em cima de uma data, E ESSE NVL retornou SIM o valor data : seja qual for o problema, SE VC ESTIVER usando o NVL apenas em cima de uma coluna data (ou da função LEAD em cima de coluda DATE, que retorna DATE também) , o NVL RESPEITA SIM o datatype da função….
29 de julho de 2023 às 9:01 pm #168320José Laurindo ChiappaModeradorComentando, depois de criar as tabelas, na hora de inserir, primeiro peguei um erro nos INSERTs da SGI5_TAB_CUS_RET_RAT :
insert into SGI5_TAB_CUS_RET_RAT (CUS_FAM, CUS_DMO, CUS_DIA, CUS_PDI, CUS_PAC, CUS_PPO, CUS_VLT_RTO, CUS_VLU_RTO, CUS_VLT_RMO, CUS_VLU_RMO, CUS_DIP, CUS_DFI, CUS_SEM, CUS_MES)
values (3011, to_date(’21-07-2023′, ‘dd-mm-yyyy’), 0, 0, 0, 0.000000, 0.000000, 0.000000, 0.000000, 0.000000, to_date(’25-07-2023 09:29:09′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-07-2023 09:29:15′, ‘dd-mm-yyyy hh24:mi:ss’), 202329, 202307)
Erro na Linha de Comandos : 45 Coluna : 159
Relatório de erros –
Erro de SQL: ORA-54013: A operação INSERT não é permitida em colunas virtuais
54013. 0000 – “INSERT operation disallowed on virtual columns”
*Cause: Attempted to insert values into a virtual column
*Action: Re-issue the statment without providing values for a virtual column==> E REALMENTE , ollando com mais vagar o CREATE eu vi que vc TEM SIM CÓDIGO EXTRA na forma de DEFAULTs para as colunas CUS_SEM e CUS_MES….
INCLUSIVE, VALE MUITO A PENA olharmos com mais vagar esse erro : a tabela tem esta estrutura aqui :COLUMN_NAME DATA_TYPE NULLABLE, DATA_DEFAULT COLUMN_ID
CUS_FAM NUMBER No 1
CUS_DMO DATE No 2
CUS_DIA NUMBER(4,0) Yes 0 3
CUS_PDI NUMBER(10,0) Yes 0 4
CUS_PAC NUMBER(10,0) Yes 0 5
CUS_PPO NUMBER(20,6) Yes 0 6
CUS_VLT_RTO NUMBER(20,6) Yes 0 7
CUS_VLU_RTO NUMBER(20,6) Yes 0 8
CUS_VLT_RMO NUMBER(20,6) Yes 0 9
CUS_VLU_RMO NUMBER(20,6) Yes 0 10
CUS_DIP DATE Yes 11
CUS_DFI DATE Yes 12
CUS_SEM NUMBER Yes TO_NUMBER(TO_CHAR(“CUS_DMO”,’YYYYWW’)) 13
CUS_MES NUMBER Yes TO_NUMBER(TO_CHAR(“CUS_DMO”,’YYYYMM’)) 14não parece ser a sua causa de erro, MAS faz TODO O SENTIDO o erro : isso que vc criou com o coluna AS (expressão) é uma coluna Virtual, não deveria estar presente numa carga de dados, ela é sempre Calculada… Tá bem, Corrigi isso rodando os INSERTs para uma tabela temporária/de trabalho e depois pra tabela definitiva, MAS isso não são simples tabelas escalares, vc tem SIM código extra envolvido : repito, NÂO PARECE TER NADA A VER com o erro em questão, MAS é um ponto de alerta, isso Não Pode ser Esquecido em NENHUMA operação de INSERT que vc vá fazer nessa tabela…
Continuando, os INSERTs na SGI5_TAB_CUS_REP_RAT alguns apresentaram erros tipo :
Erro a partir da linha : 1.414 no comando –
insert into SGI5_TAB_CUS_REP_RAT (CUS_FAM, CUS_DTA, CUS_DIA, CUS_PRO, CUS_PPO, CUS_VLT_RTO, CUS_VLU_RTO, CUS_VLT_RMO, CUS_VLU_RMO, CUS_USU, CUS_DUA, CUS_DBA)
values (3060, to_date(’01-11-2021′, ‘dd-mm-yyyy’), 0, 0, 0.000000, 640503.000000, 3.889800, 311624.000000, 1.892500, 9001, to_date(’22-11-2021 15:04:25′, ‘dd-mm-yyyy hh24:mi:ss’), 30/12/1899)
Erro na Linha de Comandos : 1.415 Coluna : 186
Relatório de erros –
Erro de SQL: ORA-00932: tipos de dados inconsistentes: esperava DATE obteve NUMBER
00932. 00000 – “inconsistent datatypes: expected %s got %s”
*Cause:
*Action:==> OU SEJA, a tool que vc usou pra extrair os INSERTs falhou, pelo jeito, e trouxe um valor 30/12/1899 que é completamente Absurdo, Tanto por não ter aspas quanto pela tool não ter reconhecido a coluna CUS_DBA como DATE, que ela é :SCOTT@xepdb1::CONTAINER=XEPDB1> @desc SGI5_TAB_CUS_REP_RAT
Nome Nulo? Tipo
CUS_FAM NOT NULL NUMBER
CUS_DTA NOT NULL DATE
CUS_DIA NUMBER(4)
CUS_PRO NUMBER(20)
CUS_PPO NUMBER(20,6)
CUS_VLT_RTO NUMBER(20,6)
CUS_VLU_RTO NUMBER(20,6)
CUS_VLT_RMO NUMBER(20,6)
CUS_VLU_RMO NUMBER(20,6)
CUS_USU NUMBER
CUS_DUA DATE
CUS_DBA DATESCOTT@xepdb1::CONTAINER=XEPDB1>
=> corrigi isso alterando o valor no texto via editr de texto, mas fica a obs como um ponto adicional… Continuando, aí sim executo o SQL isoladamente, SEM estar dentro de MERGE, e TIRANDO aquele monte de lixos de TO_CHAR/TO_DATE desnecessários – só não entendi esse “-1” que tinha depois do parêntesis final do LEAD :
… LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM ORDER BY A.CUS_FAM, A.CUS_DTA) – 1, TRUNC(sysdate….
=> isso é algo questionável em termos de lógica, mas Ok : mesmo se o LEAD trouxer um NULL , NULL – 1 retornal NULL, o sysdate troca o null por outra coisa, mas vou colocar sem ele o select :
-
AutorPosts
ESSanches
José Laurindo Chiappa
Motta- Você deve fazer login para responder a este tópico.