tag:blogger.com,1999:blog-13265058.post5827184965394735407..comments2023-10-28T23:33:56.980+11:00Comments on Sydney Oracle Lab: COLLECT and DISTINCTSydOraclehttp://www.blogger.com/profile/08828771074492585943noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-13265058.post-5381478352956225262012-12-15T10:12:58.570+11:002012-12-15T10:12:58.570+11:00Good tip. The SET option works in PL/SQL.Good tip. The SET option works in PL/SQL.SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-13265058.post-4215257802562257722012-12-11T20:48:01.732+11:002012-12-11T20:48:01.732+11:00You need to use COLLECT and SET:
select cast(coll...You need to use COLLECT and SET:<br /><br />select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)<br />from HR.employees<br />where job_id = 'SA_REP'<br />and manager_id in (147,145)<br />union all<br />select cast(collect(first_name) as sys.dbms_debug_vc2coll)<br />from HR.employees<br />where job_id = 'SA_REP'<br />and manager_id in (147,145)<br />union all<br />select <b>set (cast(collect(first_name) as sys.dbms_debug_vc2coll))</b><br />from HR.employees<br />where job_id = 'SA_REP'<br />and manager_id in (147,145);Gokhan Atilhttp://www.gokhanatil.comnoreply@blogger.com