join DATA

[mangok]

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

 

[Dagon]

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.

 

[sem]

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

 

[mangok]

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

 

[Dagon]

Sorry, that is a misprint - should be "and".

 

[mangok]

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

 

[mangok]

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

 

[sem]

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

 

[mangok]

I want to get rid of sql query which get displayed in the flat file
mangesh

 

[sem]

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

 

[Dagon]

You may need "set echo off" as well.

 

[sem]

Right, Dagon.

Regards, Dima