Home » SQL & PL/SQL » SQL & PL/SQL » Union - How to populate null field (Oracle 9i)
Union - How to populate null field [message #352660] Wed, 08 October 2008 20:06 Go to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Suppose the result of first query is:
name1  || Name2 || <null>  

and the second query is
name1  || Name2 || Name3  

I use union to merge these results, and therefore I have two records.

Now, I want that null column to be populated with Name3. How should I do that? Is it possible?
Re: Union - How to populate null field [message #352663 is a reply to message #352660] Wed, 08 October 2008 20:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Hi,

Sorry but I do not understand your question as what you describe is ambiguous. Please post some sample data to illustrate what you want.

Kevin
Re: Union - How to populate null field [message #352666 is a reply to message #352663] Wed, 08 October 2008 21:14 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Let's say I pull three columns on my first query, let's say columns firstname, lastname and comp_name.
Then on my second query I only have firstname and lastname, there's no comp_name in it. So I want the retreived comp_name on first query to be copied on second query comp_name.

It's not posible to merge the two queries so I use union.
Re: Union - How to populate null field [message #352683 is a reply to message #352666] Thu, 09 October 2008 00:15 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I supposed if you take all rows and order your results, you can use Oracle Analytics LEAD/LAG functions. Do a google for it and see if this does the trick.

Kevin
Re: Union - How to populate null field [message #352699 is a reply to message #352666] Thu, 09 October 2008 01:15 Go to previous messageGo to next message
Amulyadeep
Messages: 6
Registered: October 2008
Junior Member
Hi wency,

uless you use the update statement..i dont think this is possible.
you can use :

update table 1
set <col1> = (select <col1> from <table2> where <col2.1> = x) where <col1.1> like '<something>'

int he above statement - table 1 has col1, col1.1 - u have col1 as null value.

you will be updating it will col1 valuse from table 2 . you can give condition by placing which record you want to update by where clause.

hope this helps!!!

cheers
Amulya
Re: Union - How to populate null field [message #352702 is a reply to message #352699] Thu, 09 October 2008 01:19 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
what about group by firstname, lastname:
select firstname, lastname, min(comp_name) from  
(
select 'FN1' firstname, 'LN1' lastname, 'CN1' comp_name from dual
UNION ALL
select 'FN2'          , 'LN2'         , 'CN2'           from dual
UNION ALL
select 'FN1'          , 'LN1'         , NULL            from dual
UNION ALL
select 'FN3'          , 'LN3'         , 'CN3'           from dual
UNION ALL
select 'FN2'          , 'LN2'         , NULL            from dual
)
where comp_name is not null
group by firstname, lastname 

gives
FIRSTNAME LASTNAME MIN(COMP_NAME)

FN1        LN1    CN1
FN2	   LN2	  CN2
FN3	   LN1	  CN3
Re: Union - How to populate null field [message #352703 is a reply to message #352702] Thu, 09 October 2008 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think this is not the correct result, you have 5 rows in input and 3 in output.
The point is:
Quote:
So I want the retreived comp_name on first query to be copied on second query comp_name.

The keyword is "copy".

Regards
Michel
Re: Union - How to populate null field [message #352748 is a reply to message #352660] Thu, 09 October 2008 05:38 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Quote:
So I want the retreived comp_name on first query to be copied on second query comp_name.

Is "company_name" retrieved by the first query unique?
Which company_name do you want to copy to the record having it null?
Try Lag And Lead Analytic Functions, it may help.

Rajy

[URL edited by LF, as Rajy has requested]

[Updated on: Fri, 10 October 2008 04:32] by Moderator

Report message to a moderator

Re: Union - How to populate null field [message #352837 is a reply to message #352748] Thu, 09 October 2008 19:25 Go to previous message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Thats it! Thanks to all of you. Razz
Previous Topic: Page Size and Line Size
Next Topic: Update query - ora-01427 error
Goto Forum:
  


Current Time: Tue Dec 06 04:19:19 CST 2016

Total time taken to generate the page: 0.10866 seconds