Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

join DATA

Status
Not open for further replies.

mangok

Technical User
Joined
Oct 24, 2001
Messages
7
Location
IN
Hi All,
I want to join the data from two tables called data9 and data11

Table Data_9
ID----plan_id------m_desc-------value9
9-- 501-R011-- 1/4/03-- 0000000000000114
9-- 501-R011-- 1/5/03-- 0000000000002394
9-- 501-R052-- 1/5/03-- 0000000000000258
9-- 502-R004-- 1/4/03-- 0000000000000010
9-- 502-R004-- 1/5/03-- 0000000000000006
9-- 502-R007-- 1/4/03-- 0000000000000012
9-- 502-R111-- 1/5/03-- 0000000000000019
9-- 515-P006-- 1/4/03-- 0000000000000500
9-- 515-R003-- 1/7/03-- 0000000000000033
9-- 515-R004-- 1/4/03-- 0000000000000046
9-- 515-R004-- 1/5/03-- 0000000000000020

Table Data 11
Id----plan_id-----m_desc-----value11
11-- 412-N005-- 1/5/03-- 0000000000000116
11-- 501-R011-- 1/5/03-- 0000000000002394
11-- 501-R052-- 1/5/03-- 0000000000000228
11-- 502-R004-- 1/5/03-- 0000000000000010
11-- 502-R007-- 1/5/03-- 0000000000000034
11-- 502-R111-- 1/5/03-- 0000000000000012
11-- 515-P006-- 1/4/03-- 0000000000000500
11-- 515-P006-- 1/5/03-- 0000000000000010
11-- 515-R003-- 1/4/03-- 0000000000000066
11-- 515-R004-- 1/5/03-- 0000000000000046

The Result that I am expecting is a table and a flat file
Plan_id------m_desc-------value9-------------value11
412-N005-- 1/5/03-- 0000000000000000-- 0000000000000116
501-R011-- 1/4/03-- 0000000000000114-- 0000000000000000
501-R011-- 1/5/03-- 0000000000002394-- 0000000000002394
501-R052-- 1/5/03-- 0000000000000258-- 0000000000000228
502-R004-- 1/4/03-- 0000000000000010-- 0000000000000000
502-R004-- 1/5/03-- 0000000000000006-- 0000000000000010
502-R007-- 1/4/03-- 0000000000000012-- 0000000000000000
502-R007-- 1/5/03-- 0000000000000000-- 0000000000000034
502-R111-- 1/5/03-- 0000000000000019-- 0000000000000012
515-P006-- 1/4/03-- 0000000000000500-- 0000000000000500
515-P006-- 1/5/03-- 0000000000000000-- 0000000000000010
515-R003-- 1/7/03-- 0000000000000033-- 0000000000000000
e.g.
if data9.plan_id” and “ data9.m_desc” = data11.plan_id” and “ data11.m_desc” for both the “id”( i.e. for 9 and 11)then it will show both the values ( value9 and value11) in one line ( as 3rd record in above table) if any one value is missing for any one id then it will get filled with “0” ( length = 16)e.g 1st record in above table
I also want output in a flat file , how can I do it with query ,or do I need to use pl/sql?
Pls help me
Mangesh


 
You could do something like:

select t9.plan_id, t9.plan_desc, t9.value, t11.value
from table9 t9, table11 t11
where t9.plan_id = t11.plan_id
and t9.plan_desc = t11.plan_desc
union all
select t9.plan_id, t9.plan_desc, t9.value, 0
from table9 t9, table11 t11
where t9.plan_id = t11.plan_id (+)
and t9.plan_desc = t11.plan_desc (+)
where t11.plan_id is null
union all
select t11.plan_id, t11.plan_desc, 0, t11.value
from table9 t9, table11 t11
where t9.plan_id (+) = t11.plan_id
and t9.plan_desc(+) = t11.plan_desc
where t9.plan_id is null
order by plan_id, plan_desc

To put it to a flat value, use spool in SQL*Plus.


 
On Oracle 9i you may FUUL JOIN:


select
nvl(Data_9.plan_id,Data_11.plan_id) plan_id, nvl(Data_9.m_desc,Data_11.m_desc) m_desc,
nvl(value9,'0000000000000000') value_9,
nvl(value11,'0000000000000000') value_11
from Data_9 full join Data_11
on Data_9.plan_id=Data_11.plan_id
and Data_9.m_desc=Data_11.m_desc

On previous try this (not very efficient though):

select
u.plan_id, u.m_desc,
nvl(value9,'0000000000000000') value_9,
nvl(value11,'0000000000000000') value_11
from Data_9,Data_11,(select plan_id,m_desc from Data_9 union select plan_id,m_desc from Data_11) u
where u.plan_id=Data_9.plan_id(+)
and u.plan_id=Data_11.plan_id(+)
and u.m_desc=Data_9.m_desc(+)
and u.m_desc=Data_11.m_desc(+)


Regards, Dima
 
HI Dagon
How can u use two where clauses in one query is it possible ?
i.e. in ur example :

select t9.plan_id, t9.plan_desc, t9.value, 0
from table9 t9, table11 t11
where t9.plan_id = t11.plan_id (+)
and t9.plan_desc = t11.plan_desc (+)
where t11.plan_id is null

regds
Mangesh

 
Sorry, that is a misprint - should be "and".
 
Hi Dagon and Sen,
I just want the result in the flat file , without banne(column heading)..but if i use sool my sql query will also get added to spool )
How can i do that? Is there any way to do that ?

Thanks
Mangesh
 
Hi Dagon and Sen,
I want the result in the flat file , without banner(column heading)and query..but if i use sool my sql query will also get added to spool )
How can i do that? Is there any way to do that ?

Thanks
Mangesh
 
If you need to get rid of headings only, use :

SET HEADING OFF

To eliminate page breaks (along with headers) use:

SET PAGES 0

Regards, Dima
 
I want to get rid of sql query which get displayed in the flat file
mangesh
 
You may either call your query from file as @script.sql :

set termout off
set feedback off
set pages 0
spool ...
select ...
/
spool off

or just launch sql*plus in silent mode: sqlplus -s



Regards, Dima
 
You may need "set echo off" as well.
 
Right, Dagon.

Regards, Dima
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top