SELECT date,emp_no,LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)) Name,
DECODE(mail_to,'R',NVL(enc_add_1,' ')||NVL(enc_add_2,' ')||NVL(enc_add_3,' ')||NVL(enc_add_4,' '),
'O',NVL(enc_mail_add_1,' ')||NVL(enc_mail_add_2,' ')||NVL(enc_mail_add_3,' ')||NVL(enc_mail_add_4,' '),
'L',NVL(leg_add_1,' ')||NVL(leg_add_2,' ')||NVL(leg_add_3,' ')||NVL(leg_add_4,' ')) Address,
DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),
'L',NVL(ph_no_4,' ')||NVL(ph_no_5,' ')) Phone_No,
trim(t_pic) Tin_No,
TO_CHAR(d_o_b,'DD-MON-YYYY') DOB,
trim(dr_lc_no) Driving_Licence,
trim(pass_no) Passport_No,
trim(email_id) Email_Id1,
trim(email_id1) Email_Id2
FROM emp WHERE
 LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name))
IN
(SELECT Name
--NVL(LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)),' ')
 FROM (SELECT COUNT(*), LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)) Name
           FROM emp where trunc(date)='26-FEB-2007'
       GROUP BY LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name))
         HAVING COUNT(*)>1)
) 		 
		 OR
DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),'L',NVL(ph_no_4,' ')||NVL(ph_no_5
,' '))
IN
(SELECT ph_no
--DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),'L',NVL(ph_no_4,' ')||NVL(ph_no_5,' '))
   FROM (SELECT DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),'L',NVL(ph_no_4,' ')||NVL(ph_no_5,' ')) ph_no ,COUNT(*)
           FROM emp WHERE TRUNC(date)='26-FEB-2007'
       GROUP BY DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),'L',NVL(ph_no_4,' ')||NVL(ph_no_5,' '))
         HAVING COUNT(*)>1)
) AND
TRUNC(date) ='26-FEB-2007' 
ORDER BY auth_dt

SQL> select count(*) from emp where trunc(date)='26-FEB-2007';

  COUNT(*)
----------
        11

SQL> 

select count(*)
FROM emp WHERE
LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name))
IN
(SELECT Name
--NVL(LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)),' ')
FROM (SELECT COUNT(*), LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)) Name
FROM emp
GROUP BY LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name))
HAVING COUNT(*)>1)
) and TRUNC(date) ='26-FEB-2007' 

trunc(date)=to_date('26-FEB-2007'