To remove duplicate lines from the output

[Hariny]

Hi,

Here is my query:

select a.out_tar||'~'|| a.out_csi_id||'~'||a.out_cust_name||'~'||a.out_id||'~'||b.out_id||'~'||a.out_category||'~'||
b.out_category||'~'||
to_char(a.out_start_date, 'fm dd-mon-yyyy hh24:mi') ||'~'||
to_char(a.out_end_date, 'fm dd-mon-yyyy hh24:mi') ||'~'||
to_char(b.out_start_date, 'fmdd-mon-yyyy hh24:mi') ||'~'||
to_char(b.out_end_date, 'fmdd-mon-yyyy hh24:mi')||'~'||c.ots_inst_label  from
out_flat_data_vw a,
out_flat_data_Vw b,
rca_flat_Data_Vw c where
(a.out_tar=b.out_tar)
and (a.out_tar=c.ots_tar)
and (a.out_id!=b.out_id)
and (a.out_type='Unplanned')
and (a.out_csi_id=b.out_csi_id)
and (a.out_Tar_type='TAR')
and (a.out_scope='Individual')
and (a.out_csi_id!='999999')
and (b.out_start_date between a.out_start_Date and a.out_end_date)
and (a.out_start_Date between '01-jul-07' and '01-aug-07')
order by a.out_tar
/
 

I am getting an output in this format
===============================
6375486.993~14465368~MASTER LOCK COMPANY~56599
6375486.993~14465368~MASTER LOCK COMPANY~56599
6381318.993~3511209~HANOVER COMPRESSOR COMPANY
6381318.993~3511209~HANOVER COMPRESSOR COMPANY
6389647.993~13873252~SYMMETRICOM INC~568136~56
6389647.993~13873252~SYMMETRICOM INC~568136~56
6421248.992~13487262~EPC BUSINESS~568339~56847
6421248.992~13487262~EPC BUSINESS~568339~56847
6421248.992~13487262~EPC BUSINESS~568478~56833
6421248.992~13487262~EPC BUSINESS~568478~56833
17765296.6~10551465~DANOSA~568790~568879~Custo
6423423.992~15397472~JOHNSON DIVERSEY INC~5688
6423423.992~15397472~JOHNSON DIVERSEY INC~5688

Here most of the rows are duplicate like first 2 rows. Data is same, but output has come twice. How to get only one output?

[debasisdas]

You are getting exactly what you have asked for in the query.

[rajanbabu23]

Try it....

select DISTINCT a.out_tar||'~'|| a.out_csi_id||'~'||a.out_cust_name||'~'||a.out_id| |'~'||b.out_id||'~'||a.out_category||'~'||
b.out_category||'~'||
to_char(a.out_start_date, 'fm dd-mon-yyyy hh24:mi') ||'~'||
to_char(a.out_end_date, 'fm dd-mon-yyyy hh24:mi') ||'~'||
to_char(b.out_start_date, 'fmdd-mon-yyyy hh24:mi') ||'~'||
to_char(b.out_end_date, 'fmdd-mon-yyyy hh24:mi')||'~'||c.ots_inst_label from
out_flat_data_vw a,
out_flat_data_Vw b,
rca_flat_Data_Vw c where
(a.out_tar=b.out_tar)
and (a.out_tar=c.ots_tar)
and (a.out_id!=b.out_id)
and (a.out_type='Unplanned')
and (a.out_csi_id=b.out_csi_id)
and (a.out_Tar_type='TAR')
and (a.out_scope='Individual')
and (a.out_csi_id!='999999')
and (b.out_start_date between a.out_start_Date and a.out_end_date)
and (a.out_start_Date between '01-jul-07' and '01-aug-07')
order by a.out_tar
/