Select to return only one line

[VinicerasMG]

Greetings everyone.

I have a question here half hairy and I need a little help. I have a table (tb_resultado_indicator) containing the following fields:
CD_Centro (Company Code)
CD_indicador_performance (production line code)
CD_Perode (1 for day, 2 for month and 3 for year)
TP_Dado (real or meta or accumulated meta or accumulated)
DT_Resultado (result date)
vl_indicator (result in them, numerical value)

I need to present the production data for a productive process in a report (Crystal Reports) as follows:

| Day | mês | Plan |
| CD_indicator | Meta | Real | Var | IC% | Meta | Real | Var | IC% | Monthly |

where the query parameter are the company code and date. IC% is the consecutive index. For a given date,
are presented the goal of the day, the actual value produced, the variation (real-goal) and the IC, as well as (from day 1st of the month)
Accumulated until the day, the real production until the day and the IC. Lastly the plan for the month is presented.

The select used (which returns only one line), is the following:

Select all

SELECT A.CD_INDICADOR_PERFORMANCE, A.VL_INDICADOR AS META, B.VL_INDICADOR AS REAL,  
       (B.VL_INDICADOR  - A.VL_INDICADOR) AS VARDIA,  
       ROUND(DECODE(NVL(A.VL_INDICADOR, 0), 0, 0, NVL(B.VL_INDICADOR, 0)/A.VL_INDICADOR * 100), 1) AS ICDIA, 
       C.VL_INDICADOR AS ACUMMETA, D.VL_INDICADOR AS ACUMREAL,  
       (D.VL_INDICADOR  - C.VL_INDICADOR) AS VARACUM,  
       ROUND(DECODE(NVL(C.VL_INDICADOR, 0), 0, 0, NVL(D.VL_INDICADOR, 0)/C.VL_INDICADOR * 100), 1) AS ICACUM, 
       E.VL_INDICADOR AS PLANO 
FROM   TB_RESULTADO_INDICADOR A, TB_RESULTADO_INDICADOR B, TB_RESULTADO_INDICADOR C, TB_RESULTADO_INDICADOR D, TB_RESULTADO_INDICADOR E 
WHERE  A.CD_CENTRO    = {?empresa_filtro} 
AND    A.CD_INDICADOR_PERFORMANCE = 552 
AND    A.CD_PERIODO   = 1 
AND    A.TP_DADO      = 'META' 
AND    A.DT_RESULTADO = {?data} 
AND    B.CD_CENTRO    = {?empresa_filtro} 
AND    B.CD_INDICADOR_PERFORMANCE = 552 
AND    B.CD_PERIODO   = 1 
AND    B.TP_DADO      = 'REAL' 
AND    B.DT_RESULTADO = {?data} 
AND    C.CD_CENTRO    = {?empresa_filtro} 
AND    C.CD_INDICADOR_PERFORMANCE = 552 
AND    C.CD_PERIODO   = 2 
AND    C.TP_DADO      = 'ACUM META' 
AND    C.DT_RESULTADO = {?data} 
AND    D.CD_CENTRO    = {?empresa_filtro} 
AND    D.CD_INDICADOR_PERFORMANCE = 552 
AND    D.CD_PERIODO   = 2 
AND    D.TP_DADO      = 'ACUM REAL' 
AND    D.DT_RESULTADO = {?data} 
AND    E.CD_CENTRO    = {?empresa_filtro} 
AND    E.CD_INDICADOR_PERFORMANCE = 552 
AND    E.CD_PERIODO   = 2 
AND    E.TP_DADO      = 'META' 
AND    E.DT_RESULTADO = LAST_DAY({?data})

The problem occurs when there is no data. When, for example, there is no goal for the day, the entire line
appears blank, even existing the actual value produced. What to do so that the values ??in the table
are made available, even when any data is missing ??

I do not know if I was clear enough in the explanation of the problem, but from now on, I thank the attention. [/ Img] [url=http://upload8.postimage.org/339480/photo_hosting.html]

[Trevisolli]

Was it more or less, brother?

If it does not pass this date below:

Select all

 
 AND E.DT_RESULTADO = LAST_DAY({?data})  

Bring all records?

Select all

 
  AND E.DT_RESULTADO = NVL ( LAST_DAY({?data}), E.DT_RESULTADO )  

I do not know if I understand correctly, if not that, send it again, okay?

[VinicerasMG]

Actually this is not this Trevisolli.
The date and company parameters will always be provided (and the same everywhere that are required ... For example, always the same date where the {? Data} parameter appears). Running is that when there is no of the values ??in the table (real daytime, day goal, accumulated goal ....) The entire line is blank.

In the case of

Select all

AND E.DT_RESULTADO = LAST_DAY({?data})

serves to select the month's plan (plan on the last day of the month). {? Data} and {? Company_filtro} will always be supplied.

Thanks

[Trevisolli]

Well, brother, from what I understand, you have an exit:

You can treat a when_no_data_found on this query.

For example, if the passage below does not return anything:

Select all

 
AND    E.CD_CENTRO    = {?empresa_filtro}  
AND    E.CD_INDICADOR_PERFORMANCE = 552  
AND    E.CD_PERIODO   = 2  
AND    E.TP_DADO      = 'META'  
AND    E.DT_RESULTADO = LAST_DAY({?data}) 

You can in the when_no_data_found, make the crash again (Without this stretch) and, on, on the when_no_data_found of this, do with others.

Whatever it sends there.

[TBou]

Good morning, gentlemen. For the case of the table not to have corresponding records, I placed the comment on SELECT explaining.

See the codes below:

Select all

 
SELECT A.CD_INDICADOR_PERFORMANCE, A.VL_INDICADOR AS META, B.VL_INDICADOR AS REAL, 
       (B.VL_INDICADOR  - A.VL_INDICADOR) AS VARDIA, 
       ROUND(DECODE(NVL(A.VL_INDICADOR, 0), 0, 0, NVL(B.VL_INDICADOR, 0)/A.VL_INDICADOR * 100), 1) AS ICDIA, 
       C.VL_INDICADOR AS ACUMMETA, D.VL_INDICADOR AS ACUMREAL, 
       (D.VL_INDICADOR  - C.VL_INDICADOR) AS VARACUM, 
       ROUND(DECODE(NVL(C.VL_INDICADOR, 0), 0, 0, NVL(D.VL_INDICADOR, 0)/C.VL_INDICADOR * 100), 1) AS ICACUM, 
       E.VL_INDICADOR AS PLANO 
FROM   TB_RESULTADO_INDICADOR A, TB_RESULTADO_INDICADOR B, TB_RESULTADO_INDICADOR C, TB_RESULTADO_INDICADOR D, TB_RESULTADO_INDICADOR E 
WHERE  C.CD_CENTRO    = B.CENTRO 
AND    C.CD_INDICADOR_PERFORMANCE = B.CD_INDICADOR_PERFORMANCE 
AND    C.CD_PERIODO   = 2 
AND    C.TP_DADO      = 'ACUM META' 
AND    C.DT_RESULTADO = B.DT_RESULTADO 
AND    D.CD_CENTRO    = B.CENTRO 
AND    D.CD_INDICADOR_PERFORMANCE = B.CD_INDICADOR_PERFORMANCE 
AND    D.CD_PERIODO   = 2 
AND    D.TP_DADO      = 'ACUM REAL' 
AND    D.DT_RESULTADO = B.DT_RESULTADO 
AND    E.CD_CENTRO    = B.CONTRO 
AND    E.CD_INDICADOR_PERFORMANCE = B.CD_INDICADOR_PERFORMANCE 
AND    E.CD_PERIODO   = 2 
AND    E.TP_DADO      = 'META' 
AND    E.DT_RESULTADO = LAST_DAY(B.DT_RESULTADO) 
/* SEGUNDO   -- ADICIONEI JUNCAO EXTERNA NOS RELACIONAMENTOS DA TABELA 'A' COM A TABELA 'B' */ 
/*           -- COLOQUEI NVL PARA A SELECT TRAZER AS OUTRAS INFORMAÇÕES 
/*              MESMO QUE NÃO TENHA REGISTROS DO RELACIONAMENTO AxB */ 
AND    A.CD_CENTRO                = (+) B.CENTRO 
AND    A.CD_INDICADOR_PERFORMANCE = (+) B.CD_INDICADOR_PERFORMANCE 
AND    A.DT_RESULTADO             = (+) B.DT_RESULTADO 
AND    NVL(A.CD_PERIODO,1)        = 1 
AND    NVL(A.TP_DADO,'META')      = 'META' 
/* PRIMEIRO  -- EU RELACIONEI AS TABELAS COM A TABELA 'B' E USEI OS FILTROS COMUNS UMA VEZ APENAS*/ 
/*           -- COLOQUEI OS FILTROS APENAS NA TABELA 'B' E COLOOQUEI EM BAIXO PARA FILTRAR PRIMEIRO. */ 
/*           -- ISSO SO PARA MELHORAR A ESTRUTURA DA SELECT...não INFLUI NO RESULTADO */ 
AND    B.CD_CENTRO    = {?empresa_filtro} 
AND    B.CD_INDICADOR_PERFORMANCE = 552 
AND    B.CD_PERIODO   = 1 
AND    B.TP_DADO      = 'REAL' 
AND    B.DT_RESULTADO = {?data} 
 

I hope that right ... See more

[TBou]

PAHO .... Sorry reversed the external junction .... I always end up confusing.

Fix a test here and you need to change the junction

Select all

 
AND    A.CD_CENTRO                (+) = B.CENTRO 
AND    A.CD_INDICADOR_PERFORMANCE (+) = B.CD_INDICADOR_PERFORMANCE 
AND    A.DT_RESULTADO             (+) = B.DT_RESULTADO 

The code (+) You should stay on the table side that will not bring the records.

[VinicerasMG]

Hello, too

Thank you for your post.
I took a test here and continues not returning anything. In the table there are only the real records of the day and real accum.

I actually gave up this select and made the report otherwise. I gave up the idea of ??bringing only one line and having to set up the report "on the arm". Refizes the select bringing the values ??of the indicators filtering by department. It was much better to maintain the report.

The solution to the problem was the wedding of the parameters of the tb_resultado table_indicator with another that calls tb_visao_system by adding the (+) at all joints.

Thanks to everyone who answered and a hug