Report generates different values ??at each execution ...

alyssonhm · Thu, 16 Dec 2010 1:45 pm

Good afternoon guys ...
I found a researched in the forum and I could not find anything related to my doubt ...
here in the company, when we run in the system certain reports we note that sometimes the values ??of Some columns are different from the result previously presented.
Ex: Generating the 1st time leaves the total value of 450 and, closing and generating the value that is presented in the same column is 480.
These columns do not suffer changes and the period generated is the same.
Spinning Query of the report on SQLDeveloper The problem does not appear.
We noticed this in a report that has the following syntax ...

Select all

 select sum(x), y, z from (select x, y, z from tab1) group by ...;

Someone ever seen a similar problem and managed to solve this case?
Thank you.
help, we use Oracle 11G with Reports 6i.

SergioLBJr · Thu, 16 Dec 2010 2:54 pm

Dude, put a little more information about the problem. Report generating different values ??with each run can be generated by many things.

Post a Query aí.

The fields have formula?

Is the report called a forms? If yes, the forms populates some structure?

Have you performed the report query at the same time when it is on the screen to see if the values ??hit?

alyssonhm · Thu, 16 Dec 2010 3:59 pm

So Sergio The report is called by Forms Yes, where some parameters are filled.
Follow Query:

Select all

 
SELECT grupo1,grupo2,cd_produto, 
       DECODE(SUM(qt_semana_01),0,0,SUM(val_semana_01)/SUM(qt_semana_01)) pm_semana_01, 
       DECODE(SUM(qt_semana_02),0,0,SUM(val_semana_02)/SUM(qt_semana_02)) pm_semana_02, 
       DECODE(SUM(qt_semana_03),0,0,SUM(val_semana_03)/SUM(qt_semana_03)) pm_semana_03, 
       DECODE(SUM(qt_semana_04),0,0,SUM(val_semana_04)/SUM(qt_semana_04)) pm_semana_04, 
       DECODE(SUM(qt_semana_05),0,0,SUM(val_semana_05)/SUM(qt_semana_05)) pm_semana_05, 
       SUM(val_semana_01) val_semana_01, 
       SUM(val_semana_02) val_semana_02, 
       SUM(val_semana_03) val_semana_03, 
       SUM(val_semana_04) val_semana_04, 
       SUM(val_semana_05) val_semana_05, 
       SUM(qt_semana_01)  qt_semana_01, 
       SUM(qt_semana_02) qt_semana_02, 
       SUM(qt_semana_03) qt_semana_03, 
       SUM(qt_semana_04) qt_semana_04, 
       SUM(qt_semana_05) qt_semana_05, 
       DECODE(grupo1,1,'PRODUTOS ACABADOS - MI',4,'GRAXARIA ME',3,'GRAXARIA MI',2,'PRODUTOS ACABADOS - ME') ds_grupo1, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,DECODE(SUM(qt_semana_01),0,0,SUM(val_semana_01)/SUM(qt_semana_01)))) pm_semana_sub_01, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,DECODE(SUM(qt_semana_02),0,0,SUM(val_semana_02)/SUM(qt_semana_02)))) pm_semana_sub_02, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,DECODE(SUM(qt_semana_03),0,0,SUM(val_semana_03)/SUM(qt_semana_03)))) pm_semana_sub_03, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,DECODE(SUM(qt_semana_04),0,0,SUM(val_semana_04)/SUM(qt_semana_04)))) pm_semana_sub_04, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,DECODE(SUM(qt_semana_05),0,0,SUM(val_semana_05)/SUM(qt_semana_05)))) pm_semana_sub_05, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(val_semana_01))) val_semana_sub_01, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(val_semana_02))) val_semana_sub_02, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(val_semana_03))) val_semana_sub_03, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(val_semana_04))) val_semana_sub_04, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(val_semana_05))) val_semana_sub_05, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(qt_semana_01)))  qt_semana_sub_01, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(qt_semana_02))) qt_semana_sub_02, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(qt_semana_03))) qt_semana_sub_03, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(qt_semana_04))) qt_semana_sub_04, 
       DECODE(grupo1,3,0,decode(grupo1,4,0,SUM(qt_semana_05))) qt_semana_sub_05, 
       /************************************************************/ 
       DECODE(grupo1,1,0,decode(grupo1,2,0,DECODE(SUM(qt_semana_01),0,0,SUM(val_semana_01)/SUM(qt_semana_01)))) pm_semana_GRAX_01, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,DECODE(SUM(qt_semana_02),0,0,SUM(val_semana_02)/SUM(qt_semana_02)))) pm_semana_GRAX_02, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,DECODE(SUM(qt_semana_03),0,0,SUM(val_semana_03)/SUM(qt_semana_03)))) pm_semana_GRAX_03, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,DECODE(SUM(qt_semana_04),0,0,SUM(val_semana_04)/SUM(qt_semana_04)))) pm_semana_GRAX_04, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,DECODE(SUM(qt_semana_05),0,0,SUM(val_semana_05)/SUM(qt_semana_05)))) pm_semana_GRAX_05, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(val_semana_01))) val_semana_GRAX_01, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(val_semana_02))) val_semana_GRAX_02, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(val_semana_03))) val_semana_GRAX_03, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(val_semana_04))) val_semana_GRAX_04, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(val_semana_05))) val_semana_GRAX_05, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(qt_semana_01)))  qt_semana_GRAX_01, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(qt_semana_02))) qt_semana_GRAX_02, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(qt_semana_03))) qt_semana_GRAX_03, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(qt_semana_04))) qt_semana_GRAX_04, 
       DECODE(grupo1,1,0,decode(grupo1,2,0,SUM(qt_semana_05))) qt_semana_GRAX_05 
    /**********************************************************/ 
FROM( 
SELECT -------------------------------------------------------------------------------------------------------------- 
       ----------GRUPO 1 - TIPO DO PRODUTO 
       -------------------------------------------------------------------------------------------------------------- 
       DECODE(d.venda_exportacao_sn,'S',DECODE(graxaria_sn,'S',4,2),DECODE(graxaria_sn,'S',3,1)) grupo1, 
       --DECODE(g.tipo_mercado,'E',2,'I',DECODE(graxaria_sn,'S',3,1)) grupo1, 
       -------------------------------------------------------------------------------------------------------------- 
       ----------GRUPO 2 - GRUPO DO PRODUTO 
       -------------------------------------------------------------------------------------------------------------- 
       g.cd_grupo grupo2, 
       -------------------------------------------------------------------------------------------------------------- 
       ----------PRODUTO 
       -------------------------------------------------------------------------------------------------------------- 
		   g.cd_produto, 
			 -------------------------------------------------------------------------------------------------------------- 
			 ----------SEMANA 01 
			 -------------------------------------------------------------------------------------------------------------- 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_01 AND :data_fim_semana_01 THEN SUM(DECODE(d.tp_fatur,'S',c.vl_liquido,'E',-c.vl_liquido,0) 
-(:frete_10*qt_produto*DECODE(NVL(i.cd_frete_nacional,b.tp_frete),'C',DECODE(b.cd_pedido,NULL,0,NVL(func_frete_cidade(e.cd_cidade,NULL,'MAX',b.cd_pedido,d.venda_deposito_sn,c.cd_produto),0)),'F',0)) 
) 
						ELSE 0 END val_semana_01, 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_01 AND :data_fim_semana_01 THEN SUM(DECODE(d.tp_fatur,'S',c.qt_produto,'E',-c.qt_produto,0)) 
				    ELSE 0 END qt_semana_01, 
			 -------------------------------------------------------------------------------------------------------------- 
			 ----------SEMANA 02 
			 -------------------------------------------------------------------------------------------------------------- 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_02 AND :data_fim_semana_02 THEN SUM(DECODE(d.tp_fatur,'S',c.vl_liquido,'E',-c.vl_liquido,0) 
-(:frete_10*qt_produto*DECODE(NVL(i.cd_frete_nacional,b.tp_frete),'C',DECODE(b.cd_pedido,NULL,0,NVL(func_frete_cidade(e.cd_cidade,NULL,'MAX',b.cd_pedido,d.venda_deposito_sn,c.cd_produto),0)),'F',0)) 
) 
						ELSE 0 END val_semana_02, 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_02 AND :data_fim_semana_02 THEN SUM(DECODE(d.tp_fatur,'S',c.qt_produto,'E',-c.qt_produto,0)) 
				    ELSE 0 END qt_semana_02, 
			 -------------------------------------------------------------------------------------------------------------- 
			 ----------SEMANA 03 
			 -------------------------------------------------------------------------------------------------------------- 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_03 AND :data_fim_semana_03 THEN SUM(DECODE(d.tp_fatur,'S',c.vl_liquido,'E',-c.vl_liquido,0) 
-(:frete_10*qt_produto*DECODE(NVL(i.cd_frete_nacional,b.tp_frete),'C',DECODE(b.cd_pedido,NULL,0,NVL(func_frete_cidade(e.cd_cidade,NULL,'MAX',b.cd_pedido,d.venda_deposito_sn,c.cd_produto),0)),'F',0)) 
) 
						ELSE 0 END val_semana_03, 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_03 AND :data_fim_semana_03 THEN SUM(DECODE(d.tp_fatur,'S',c.qt_produto,'E',-c.qt_produto,0)) 
				    ELSE 0 END qt_semana_03, 
			 -------------------------------------------------------------------------------------------------------------- 
			 ----------SEMANA 04 
			 -------------------------------------------------------------------------------------------------------------- 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_04 AND :data_fim_semana_04 THEN SUM(DECODE(d.tp_fatur,'S',c.vl_liquido,'E',-c.vl_liquido,0) 
-(:frete_10*qt_produto*DECODE(NVL(i.cd_frete_nacional,b.tp_frete),'C',DECODE(b.cd_pedido,NULL,0,NVL(func_frete_cidade(e.cd_cidade,NULL,'MAX',b.cd_pedido,d.venda_deposito_sn,c.cd_produto),0)),'F',0)) 
) 
						ELSE 0 END val_semana_04, 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_04 AND :data_fim_semana_04 THEN SUM(DECODE(d.tp_fatur,'S',c.qt_produto,'E',-c.qt_produto,0)) 
				    ELSE 0 END qt_semana_04, 
			 -------------------------------------------------------------------------------------------------------------- 
			 ----------SEMANA 05 
			 -------------------------------------------------------------------------------------------------------------- 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_05 AND :data_fim_semana_05 THEN SUM(DECODE(d.tp_fatur,'S',c.vl_liquido,'E',-c.vl_liquido,0) 
-(:frete_10*qt_produto*DECODE(NVL(i.cd_frete_nacional,b.tp_frete),'C',DECODE(b.cd_pedido,NULL,0,NVL(func_frete_cidade(e.cd_cidade,NULL,'MAX',b.cd_pedido,d.venda_deposito_sn,c.cd_produto),0)),'F',0)) 
) 
						ELSE 0 END val_semana_05, 
			 CASE WHEN TRUNC(b.dh_emissao) BETWEEN :data_inicio_semana_05 AND :data_fim_semana_05 THEN SUM(DECODE(d.tp_fatur,'S',c.qt_produto,'E',-c.qt_produto,0)) 
				    ELSE 0 END qt_semana_05 
FROM   frete i,pedido h,produto g,/*regiao_cidade f,*/emitente e,item_nf_comercial c,nf_comercial b,tp_faturamento d 
WHERE  TRUNC(b.dh_emissao)        BETWEEN  :data_inicio AND :data_fim 
AND    b.bo_cancelada = 'N' 
AND    b.nr_nota_fiscal NOT LIKE '%*%' 
AND    c.nr_nf_lancamento = b.nr_nf_lancamento 
AND    d.cd_tpfatur       = b.cd_tpfatur 
AND    e.cd_emite      = b.cd_emite_nota 
--AND    f.cd_cidade(+)      = e.cd_cidade 
AND    c.cd_produto       = g.cd_produto 
AND    b.cd_pedido = h.nr_reg(+) 
AND    h.bo_cif_fob = i.cd_frete(+) 
AND    EXISTS (SELECT 1 
                          FROM    grupo 
                          WHERE   cd_grupo      =  g.cd_grupo 
                          START   WITH cd_grupo IN ('00000060','00000061') 
                          CONNECT BY   PRIOR cd_grupo = cd_grp_pai) 
&filtro 
GROUP  BY TRUNC(b.dh_emissao),g.cd_produto, 
                     --DECODE(g.tipo_mercado,'E',2,'I',DECODE(graxaria_sn,'S',3,1)),g.cd_grupo) 
                     DECODE(d.venda_exportacao_sn,'S',DECODE(graxaria_sn,'S',4,2),DECODE(graxaria_sn,'S',3,1)) ,g.cd_grupo) 
GROUP BY grupo1,grupo2,cd_produto

SergioLBJr · Thu, 16 Dec 2010 4:31 pm

What fields are changing value ??

What exists in & filter ??

alyssonhm · Tue, 28 Dec 2010 9:12 pm

Good evening Sergio.
Sorry for the delay in responding, I had small problems on the internet at home and this traveling to work.
Then we did an analysis in the database and we discovered a likely bug in Oracle 11g, where it generates problems in Group BY clauses.
We pass the responsible DBA that will be analyzing the problem further. We will still make a downgrade of the bank on a machine for simultaneous testing.
Having news will put on the forum.
Hugs and thank help.
I went

SergioLBJr · Wed, 29 Dec 2010 12:44 pm

What I know is that the one from some version of Banco Oracle (I can not remember exactly which) he stops the results of querys by Group BY, and you would have to include the Clause Order by this.

But of course, that this is just a kick the blind ...

Report generates different values ??at each execution ...

Who is online