Home » SQL & PL/SQL » SQL & PL/SQL » union query
union query [message #257496] Wed, 08 August 2007 08:37 Go to next message
ora4dev
Messages: 7
Registered: October 2005
Junior Member
This query returns 2 rows as below.

select * From
(select 'Permanent',Address1,Address2 from add_tab where addtyp ='A1'
and id = 100
union
select 'Present',Address1,Address2 from add_tab where addtyp ='A2'
and id = 100
)


Permanent	BLOCK-A, ROAD-25A HOUSE-7A (3RD. FLOOR)	MUMBAI
Present	PRO-VICE CHANCELLOR BM UNIVERSITY	66, KOLKATA


If one address is not present the output should be

Permanent	BLOCK-A, ROAD-25A HOUSE-7A (3RD. FLOOR)	MUMBAI
Present	


Two rows are required in the output. Do not have clue how to do this.

Thanks

Re: union query [message #258421 is a reply to message #257496] Sat, 11 August 2007 17:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
You could use UNION or UNION ALL to add additional selects of 'Permanent' and 'Present' followed by null values for the other column values WHERE NOT EXISTS your specified condition. The general syntax is as shown below. The code below would always produce at least one row.

SELECT 'literal_value', some_column 
FROM   your_table
WHERE  <conditions>
UNION ALL
SELECT 'literal_value', NULL
FROM   DUAL
WHERE  NOT EXISTS
       (SELECT *
        FROM   your_table
        WHERE  <conditions>);     

Re: union query [message #258460 is a reply to message #258421] Sun, 12 August 2007 03:37 Go to previous messageGo to next message
ora4dev
Messages: 7
Registered: October 2005
Junior Member
Thanks Barbara.

I think i did not put my requirement properly.

Table creation and Insert scripts:

CREATE TABLE ADD_TAB(ID NUMBER(4),ADDRESS1 VARCHAR2(30), ADDRESS2 VARCHAR2(30), ADDTYP VARCHAR2(2))

INSERT INTO ADD_TAB VALUES(100, 'BLOCK-A, ROAD-25A', 'HOUSE-7A (3RD. FLOOR) MUMBAI', 'A1')

INSERT INTO ADD_TAB VALUES(100, 'PRO-VICE CHANCELLOR', 'BM UNIVERSITY 66, KOLKATA', 'A2')


SQL> select * From
  2  (select decode(addtyp, 'A1', 'Permanent'),Address1,Address2 from add_tab where addtyp ='A1'
  3  and id = 100
  4  union 
  5  select decode(addtyp, 'A2', 'Present') ,Address1,Address2 from add_tab where addtyp ='A2'
  6  and id = 100
  7  );

DECODE(AD ADDRESS1                       ADDRESS2
--------- ------------------------------ ------------------------------
Permanent BLOCK-A, ROAD-25A              HOUSE-7A (3RD. FLOOR) MUMBAI
Present   PRO-VICE CHANCELLOR            BM UNIVERSITY 66, KOLKATA

SQL> delete from add_tab where addtyp = 'A1';

1 row deleted.

SQL> select * From
  2  (select decode(addtyp, 'A1', 'Permanent'),Address1,Address2 from add_tab where addtyp ='A1'
  3  and id = 100
  4  union 
  5  select decode(addtyp, 'A2', 'Present') ,Address1,Address2 from add_tab where addtyp ='A2'
  6  and id = 100
  7  );

DECODE(AD ADDRESS1                       ADDRESS2
--------- ------------------------------ ------------------------------
Present   PRO-VICE CHANCELLOR            BM UNIVERSITY 66, KOLKATA

SQL> select * From
  2  (select decode(addtyp, 'A1', 'Permanent'),Address1,Address2 from add_tab where addtyp ='A1'
  3  and id = 100
  4  union 
  5  select decode(addtyp, 'A2', 'Present') ,Address1,Address2 from add_tab where addtyp ='A2'
  6  and id = 100
  7  union all 
  8  select decode(addtyp, 'A1', 'Permanent', 'A2', 'Present'), address1, address2 from add_tab 
  9   where not exists (select addtyp from add_tab where addtyp in('A1', 'A2')) 
 10  );

DECODE(AD ADDRESS1                       ADDRESS2
--------- ------------------------------ ------------------------------
Present   PRO-VICE CHANCELLOR            BM UNIVERSITY 66, KOLKATA

SQL> 


Required output is:
DECODE(AD ADDRESS1                       ADDRESS2
--------- ------------------------------ ------------------------------
Present   PRO-VICE CHANCELLOR            BM UNIVERSITY 66, KOLKATA
Permanent 



Whichever address type is not present for that blank row should be displayed.
If 'A1' is not present in table  output put should be:

DECODE(AD ADDRESS1                       ADDRESS2                       
--------- ------------------------------ ------------------------------ --
Present   PRO-VICE CHANCELLOR            BM UNIVERSITY 66, KOLKATA     
Permanent 

If 'A2' is not present in table output should be:

DECODE(AD ADDRESS1                       ADDRESS2                       AD
--------- ------------------------------ ------------------------------ --
Permanent BLOCK-A, ROAD-25A              HOUSE-7A (3RD. FLOOR) MUMBAI   A1
Present

If both 'A1' and 'A2' is not in table then output should be:

Present
Permanent


Thanks in Advance.

Raju M
Re: union query [message #258461 is a reply to message #258460] Sun, 12 August 2007 03:55 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
with t as
(select 'A1' add_type from dual
union
select 'A2' add_type from dual
)
select decode(add_type,'A1','PERMANENT','A2','PRESENT') address_type, address1, address2 from add_tab, t
where t.add_type = add_tab.addtyp (+) order by id;


Thanks for providing the create and insert script

Regards

Rajaram

Re: union query [message #258465 is a reply to message #258461] Sun, 12 August 2007 04:29 Go to previous messageGo to next message
ora4dev
Messages: 7
Registered: October 2005
Junior Member
Thanks a lot Rajaram!
That works perfectly.

Regards

Raju M
Re: union query [message #258487 is a reply to message #258465] Sun, 12 August 2007 12:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Raju M,

You explained your problem sufficiently. You just did not apply what I gave you correctly. I was trying to just give you the general syntax, so that you would think it through and understand what you were doing, rather than just spoon-feeding you a solution. I have provided a demonstration of the proper application of what I suggested below, just to show that it works. However, I believe I prefer Rajaram's solution that uses the sub-query factoring clause and outer join.

SCOTT@10gXE> CREATE TABLE ADD_TAB(ID NUMBER(4),ADDRESS1 VARCHAR2(30), ADDRESS2 VARCHAR2(30), ADDTYP VARCHAR2(2))
  2  /

Table created.

SCOTT@10gXE> INSERT INTO ADD_TAB VALUES(100, 'BLOCK-A, ROAD-25A', 'HOUSE-7A (3RD. FLOOR) MUMBAI', 'A1')
  2  /

1 row created.

SCOTT@10gXE> INSERT INTO ADD_TAB VALUES(100, 'PRO-VICE CHANCELLOR', 'BM UNIVERSITY 66, KOLKATA', 'A2')
  2  /

1 row created.

SCOTT@10gXE> select decode (addtyp, 'A1', 'Permanent', 'A2', 'Present') as add_type,
  2  	    Address1, Address2
  3  from   add_tab
  4  where  id = 100
  5  union all
  6  select 'Permanent' as add_type, null, null
  7  from   dual
  8  where  not exists
  9  	    (select *
 10  	     from   add_tab
 11  	     where  addtyp = 'A1'
 12  	     and    id = 100)
 13  union all
 14  select 'Present' as add_type, null, null
 15  from   dual
 16  where  not exists
 17  	    (select *
 18  	     from   add_tab
 19  	     where  addtyp = 'A2'
 20  	     and    id = 100)
 21  /

ADD_TYPE  ADDRESS1                       ADDRESS2
--------- ------------------------------ ------------------------------
Permanent BLOCK-A, ROAD-25A              HOUSE-7A (3RD. FLOOR) MUMBAI
Present   PRO-VICE CHANCELLOR            BM UNIVERSITY 66, KOLKATA

SCOTT@10gXE> delete from add_tab where addtyp = 'A1'
  2  /

1 row deleted.

SCOTT@10gXE> select decode (addtyp, 'A1', 'Permanent', 'A2', 'Present') as add_type,
  2  	    Address1, Address2
  3  from   add_tab
  4  where  id = 100
  5  union all
  6  select 'Permanent' as add_type, null, null
  7  from   dual
  8  where  not exists
  9  	    (select *
 10  	     from   add_tab
 11  	     where  addtyp = 'A1'
 12  	     and    id = 100)
 13  union all
 14  select 'Present' as add_type, null, null
 15  from   dual
 16  where  not exists
 17  	    (select *
 18  	     from   add_tab
 19  	     where  addtyp = 'A2'
 20  	     and    id = 100)
 21  /

ADD_TYPE  ADDRESS1                       ADDRESS2
--------- ------------------------------ ------------------------------
Present   PRO-VICE CHANCELLOR            BM UNIVERSITY 66, KOLKATA
Permanent

SCOTT@10gXE> 

Re: union query [message #258593 is a reply to message #258487] Mon, 13 August 2007 02:35 Go to previous message
ora4dev
Messages: 7
Registered: October 2005
Junior Member
Thanks Barbara!
Now I know where I went wrong in implementing your solution.
Thanks Again.
Previous Topic: Not able to drop users (merged)
Next Topic: use Flashback
Goto Forum:
  


Current Time: Mon Dec 05 12:35:01 CST 2016

Total time taken to generate the page: 0.08469 seconds