Sunday, September 13, 2015

Oracle learn how to search by date

declare
    v_d1 varchar2(12) := TO_char(:FROM_DATE,'dd-mm-yyyy');
    v_d2 varchar2(12) := TO_char(:TO_DATE,'DD-MM-YYYY');
    blk_id block := find_block('come_leav');
    emp_where varchar2(2000);
    def_where varchar2(2000);
begin
   
    --def_where := 'emp_no = '||''''||upper(:txt_find)||'''';
    emp_where := 'emp_no between '||:txt_find||' and '||:txt_find2;
    if :from_date is not null and :txt_find is not null then
        def_where := emp_where||' and '||'trunc(to_date(work_date,''dd-mm-yyyy'')) between '||''''||v_d1||''''||' and '||''''||v_d2||'''';
    elsif :from_date is not null and :txt_find is null then
        def_where := 'trunc(to_date(work_date,''dd-mm-yyyy'')) between '||''''||v_d1||''''||' and '||''''||v_d2||'''';
    elsif :from_date is null and :txt_find is not null then
        def_where := emp_where;
    elsif :from_date is null and :txt_find is null then
        def_where := '1 = 1';
       
    end if;
   
    set_block_property(blk_id,default_where,def_where);
  go_block('come_leav');
  execute_query;

end;

No comments:

Post a Comment