Filtering in the survey only the current month's competence - 1

[leo_analista]

Good morning people ...

I have a terrible doubt.
I need to filter in the search that shows me the chosen sector, but only the current month's competence -1.
How can I do this?

I have a list of units (sectors), and when I click on the sector appears next to the indicators of this sector, but all competence is appearing, I need only last month, current month-1.

follows list code (search)

Select all

declare 
	x varchar2(100);	 
begin 
x:= 'unidade like '|| chr(39) ||:sistema_agm_hevv.setor || '%' || chr(39); 
set_block_property('sistema_agm_hevv',default_where,x); 
execute_query(); 
end; 

Can anyone help me?
has an attachment figure so you can see more easily.

Thank you.

[spernega]

Leo_analisata,

would have to be more or less like this:

Select all

declare 
   x varchar2(100);    
begin 
 x:= 'unidade like '|| chr(39) ||:sistema_agm_hevv.setor || '%' || chr(39)|| 
      -- 
     ' and '||:parametro_data||' between '||trunc(:parametro_data,'month')||' and '||last_day(:parametro_data); 
     -- 
 set_block_property('sistema_agm_hevv',default_where,x); 
 execute_query(); 
end;

Include the date parameter in the Were Clause.
You only need to see the formation of the comparison.

or still in the WHERE clause in the same block properties

[leo_analista]

Thank you very much for the help. But I still do not finish ... kkkkk

I preferred to put a '=', but I can not put current month - 1, I'vê tried everything ...

Ex:

Select all

 
= TO_DATE('01/'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM/YYYY')) 

Select all

 
declare 
x varchar2(100);  
begin 
x:= 'unidade like '|| chr(39) ||:sistema_agm_hevv.setor || '%' || chr(39)|| 
-- 
' and '||'competencia'||' = '||'''01/07/2016'''; 
------= TO_DATE('01/'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM/YYYY'))----esse não funciona, porque não???? 
 
set_block_property('sistema_agm_hevv',default_where,x); 
execute_query(); 
end; 

Valeu by force !!!

Léo

[spernega]

Beauty,

Any problem let us know.

[leo_analista]

So how do I put current month -1 in the parameter?

I could not do this:

Select all

 
= TO_DATE('01/'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM/YYYY')) 

Léo

[spernega]

Good morning,

Thus:

Select all

declare 
 x      varchar2(100);  
 v_data varchar2(50); 
begin 
  -- 
  v_data :=  'To_Date('||''''||to_char((add_months(trunc(sysdate,'month'),-1)),'dd/mm/yyyy')||''''||','||''''||'dd/mm/yyyy'||''''||')'; 
  -- 
  x:= 'unidade like '|| chr(39) ||:sistema_agm_hevv.setor || '%' || chr(39)|| 
  -- 
  ' and '||:sistema_agm_hevv.competencia||' = '||v_data; 
  -- 
  set_block_property('sistema_agm_hevv',default_where,x); 
  execute_query(); 
end;

[spernega]

Leo,

One more comment, I usually create this type of functionality in a pre-query trigger.

[leo_analista]

Thank you very much spernega, it worked perfectly.
: D: D