Sql Query:

Count duplicate value
1.

SELECT REG_NO, COUNT(REG_NO) 
FROM OP_APPOINTMENT
WHERE APPOINT_DATE BETWEEN NVL(:P_DT1,APPOINT_DATE) AND NVL(:P_DT2,APPOINT_DATE)
GROUP BY REG_NO
HAVING ( COUNT(REG_NO) > 1 )
2.

select POLICE_BP_NO, count(POLICE_BP_NO) from op_registration where rel_id='0'

group by POLICE_BP_NO

having count(POLICE_BP_NO)>1

3. Union all

select sum(onetime) one,sum(app_multipl) multiple,sum(app_dependent) dep,sum(app_self) self,apdate
from
((select  count(app_onetime) onetime, 0 as  app_multipl, 0 as app_dependent, 0 as  app_self, apdate
from
(SELECT distinct(REG_NO) app_onetime,0 as app_multiple, 0 as app_dependent,0 as app_self, TO_CHAR(APPOINT_DATE,'yyyy') ||CHR(10) ||TO_CHAR(APPOINT_DATE,'MM') APDATE FROM OP_APPOINTMENT
WHERE APPOINT_DATE BETWEEN NVL(:P_DT1,APPOINT_DATE) AND NVL(:P_DT2,APPOINT_DATE)
)
group by apdate)
union all
(select 0 as app_onetime,count(appoint_no) app_multiple,0 as app_dependent,0 as app_self,apdate
from
(select TO_CHAR(APPOINT_DATE,'yyyy') ||CHR(10) ||TO_CHAR(APPOINT_DATE,'MM') apdate,appoint_no from op_appointment
where appoint_date between NVL(:P_DT1,APPOINT_DATE) AND NVL(:P_DT2,APPOINT_DATE)
order by apdate)
group by apdate)
union all
(select 0 as app_onetime,0 as app_multiple,count(appoint_no) app_dependent,0 as app_self,apdate
from(
select TO_CHAR(APPOINT_DATE,'yyyy') ||CHR(10) ||TO_CHAR(APPOINT_DATE,'MM') apdate,appoint_no from op_appointment
where appoint_date between NVL(:P_DT1,APPOINT_DATE) AND NVL(:P_DT2,APPOINT_DATE)
and reg_no in(select reg_no
from op_registration  where rel_id<>0))
group by apdate)
union all

(select 0 as app_onetime,0 as app_multiple,0 as app_dependent,count(appoint_no) app_self,apdate
from(
select TO_CHAR(APPOINT_DATE,'yyyy') ||CHR(10) ||TO_CHAR(APPOINT_DATE,'MM') apdate,appoint_no from op_appointment
where appoint_date between NVL(:P_DT1,APPOINT_DATE) AND NVL(:P_DT2,APPOINT_DATE)
and reg_no in(select reg_no
from op_registration  where rel_id=0))
group by apdate))

group by apdate

4.  Sub query


This statement will be select thats which is not present others table:

select appoint_no,consult_dt from op_consultation where appoint_no not in (select appoint_no from op_appointment) and consult_DT BETWEEN NVL(:P_DT1,consult_DT) AND NVL(:P_DT2,consult_DT)


5.



delete  from op_consultation where appoint_no not in  (select appoint_no from op_appointment) and consult_DT BETWEEN NVL(:P_DT1,consult_DT) AND NVL(:P_DT2,consult_DT)
commit;


----union all exapmple :

  SELECT SURGERY_NO,TESTNAME,SERVICE
   FROM
  ((SELECT SURGERY_NO,FNC_TESTNAME(TEST_NO) TESTNAME, 'P' AS SERVICE
   FROM OT_PATHOLOGY
   )
   UNION ALL
   (SELECT SURGERY_NO, FNC_TESTNAME(TEST_NO) TESTNAME,'R' AS SERVICE
    FROM  OT_RADIOLOGY
      ))
   
      WHERE SURGERY_NO='P180101000128'
   
   


   Query Optimization :
1.
select distinct hpms_doctor.doctor_no, hpms_doctor.DOCTOR_NAME
 from op_appointment,hpms_doctor
 where hpms_doctor.DOCTOR_NO=op_appointment.DOCTOR_NO
  and trunc(appoint_date) = trunc(sysdate)
  and hpms_doctor.doctor_no in (select consult_by from opv_appexchange)
 
  /
2.
  select distinct D.doctor_no, D.DOCTOR_NAME
from hpms_doctor  D, op_appointment A
where D.DOCTOR_NO=A.DOCTOR_NO
and trunc(a.appoint_date) = trunc(sysdate)
and exists  (select consult_by from opv_appexchange)
order by  D.DOCTOR_NAME


above 2 no query is faster than 1
   

Read More