-- Thanks for the question regarding "SQL Query", version 10.2.0.3.0

Submitted on 5-Aug-2009 15:21 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 19-Jul-2010 13:23

You Asked

Good afternoon Tom,

Recently I got a request, where two column values in a table should be displayed under one column. I know, with using UNION clause we can display the two column values under one column. Is there any other way we can do this in SQL without using a function.

create table t (vno number , email_addr1 varchar2(30), email_addr2 varchar2(30));

insert into t values(1100,'ss@hotmail.com','james@yahoo.com');

SELECT vno, email_addr1 email_addr from t
union
SELECT vno, email_addr2 from t
;

VNO EMAIL_ADDR
------- ------------------------------
1100 james@yahoo.com
1100 ss@hotmail.com

Thank you so much for your time.

and we said...

ops$tkyte%ORA10GR2> select vno, decode(r,1,email_addr1,2,email_addr2) from t, (select 1 r 
from dual union all select 2 r from dual);

       VNO DECODE(R,1,EMAIL_ADDR1,2,EMAIL
---------- ------------------------------
      1100 ss@hotmail.com
      1100 james@yahoo.com

You meant to use UNION ALL - not UNION by the way.

A UNION B = distinct( A+B )
A UNION ALL B = (A+B)

you don't want an expensive UNION when you meant to use the inexpensive UNION ALL

Reviews

Thank you August 5, 2009 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

Thank you so much Tom for the quick response. I am learning new things every day from your site.

Once again thanks a lot !!!!

July 16, 2010 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

great explanation July 16, 2010 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: hariom from india

great explanation

July 16, 2010 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: hariom from india

But sir your query is returning duplicate rows when i ran this query?

       VNO DECODE(R,1,EMAIL_ADDR1,2,EMAIL
---------- ------------------------------
      1100 ss@hotmail.com
      1100 ss@hotmail.com
      1100 james@yahoo.com
      1100 james@yahoo.com

Followup July 19, 2010 - 1pm Central time zone:

no it isn't, you have the row duplicated in your system.

that is, you ran:

create table t (vno number , email_addr1 varchar2(30), email_addr2 varchar2(30));
insert into t values(1100,'ss@hotmail.com','james@yahoo.com');
select vno, decode(r,1,email_addr1,2,email_addr2) from t,
(select 1 r from dual union all select 2 r from dual);

twice, the first time you ran it - one row:

ops$tkyte%ORA10GR2> create table t (vno number , email_addr1 varchar2(30), email_addr2 
varchar2(30));
insert into t values(1100,'ss@hotmail.com','james@yahoo.com');
Table created.

ops$tkyte%ORA10GR2> insert into t values(1100,'ss@hotmail.com','james@yahoo.com');

1 row created.

ops$tkyte%ORA10GR2> select vno, decode(r,1,email_addr1,2,email_addr2) from t,
  2  (select 1 r from dual union all select 2 r from dual);

       VNO DECODE(R,1,EMAIL_ADDR1,2,EMAIL
---------- ------------------------------
      1100 ss@hotmail.com
      1100 james@yahoo.com

the second time you just didn't realize it failed on the create (you didn't drop it)

ops$tkyte%ORA10GR2> create table t (vno number , email_addr1 varchar2(30), email_addr2 
varchar2(30));
create table t (vno number , email_addr1 varchar2(30), email_addr2 varchar2(30))
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


ops$tkyte%ORA10GR2> insert into t values(1100,'ss@hotmail.com','james@yahoo.com');

1 row created.

ops$tkyte%ORA10GR2> select vno, decode(r,1,email_addr1,2,email_addr2) from t,
  2  (select 1 r from dual union all select 2 r from dual);

       VNO DECODE(R,1,EMAIL_ADDR1,2,EMAIL
---------- ------------------------------
      1100 ss@hotmail.com
      1100 james@yahoo.com
      1100 ss@hotmail.com
      1100 james@yahoo.com

so, operator error.

Write a Review