Problem with calculation

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
Dualogic
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 16 Aug 2016 11:36 am




I have a problem in a customer's SQL query, which executes a calculation, but even if I change the value of the fields the return is always the same, I need HUMALLY OF YOUR KNOWLEDGE.

follows code.

In this always returns 1.04

Select all

SELECT ROUND (DECODE (a.ie_sexo,'M',((TO_NUMBER('1.112','999.99999999')-(TO_NUMBER('0.00043499','999.99999999')*(TO_NUMBER(TRIM(Aval(b.nr_sequencia,3032)),'999.99999999'))) + (TO_NUMBER('0.00000055','999.99999999') * (TO_NUMBER(TRIM(Aval(b.nr_sequencia,3032)),'999.99999999') * (TO_NUMBER(TRIM(Aval(b.nr_sequencia,3032)),'999.99999999')))) - (TO_NUMBER('0.00028826','999.99999999') * (TO_NUMBER(TRIM(Aval(b.nr_sequencia,2684)),'999.99999999'))))),'F',((TO_NUMBER('1.097','999.99999999')-(TO_NUMBER('0.00046971','999.99999999')*(TO_NUMBER(TRIM(Aval(b.nr_sequencia,3032)),'999.99999999'))) + (TO_NUMBER('0.00000056','999.99999999') * (TO_NUMBER(TRIM(Aval(b.nr_sequencia,3032)),'999.99999999') * (TO_NUMBER(TRIM(Aval(b.nr_sequencia,3032)),'999.99999999')))) - (TO_NUMBER('0.00012828','999.99999999') * (TO_NUMBER(TRIM(Aval(b.nr_sequencia,2684)),'999.99999999'))))),0),2) cp  
FROM atendimento_paciente_v a, med_avaliacao_paciente b  
WHERE b.nr_atendimento = :nr_atendimento  
AND b.nr_atendimento = a.nr_atendimento  
AND b.NR_SEQ_TIPO_AVALIACAO = 227 
ORDER BY b.nr_sequencia DESC

here always returns 26

Select all

SELECT ROUND(DECODE(a.ie_sexo,'M',((TO_NUMBER('4.95','999.99999999')/TO_NUMBER(TRIM(Aval(b.nr_sequencia,3036)),'999.99999999') - TO_NUMBER('4.5','999.99999999')) * 100),'F',((TO_NUMBER('4.95','999.99999999')/TO_NUMBER(TRIM(Aval(b.nr_sequencia,3036)),'999.99999999') - TO_NUMBER('4.5','999.99999999')) * 100),0),1) cp  
FROM atendimento_paciente_v a, med_avaliacao_paciente b  
WHERE b.nr_atendimento = :nr_atendimento  
AND b.nr_atendimento = a.nr_atendimento  
AND b.NR_SEQ_TIPO_AVALIACAO = 227 
ORDER BY b.nr_sequencia DESC
Banco Oracle 11.2.0.4
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Good morning,

What returns when you perform the role access alone?
Dualogic
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 16 Aug 2016 11:36 am

The Aval function, returns the value of the field of an evaluation screen that I have in my system.

The value is powered by the user based on the data collected.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

It's just a hunch in the dark, but it gives the impression that it is function can be with some problem and always returns the same value to always give the same result in the select, regardless of b.nr_sequence.
Dualogic
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 16 Aug 2016 11:36 am

The problem is that this problem occurs only with this specific evaluation, there are other evaluations that the calculations are made normally.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

How can you make the function structure available and some query b.nr_nr_nr?
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

The problem is in the "Aval" function as mentioned. It would be necessary to analyze it. If it were possible to post it to verify.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 11 guests