Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join two derieved table with where clause
Outer Join two derieved table with where clause [message #327152] Sat, 14 June 2008 06:15 Go to next message
ps_kaushik1
Messages: 3
Registered: June 2008
Location: India
Junior Member
I want to left outer join three derieved tables. But It is giving me only the result of inner join as a result I am missing many rows. Can any body help :

Here is the query

select qry1.critq_id,qry1.cmnts,qry1.trn_dt,
tab1.demogrph_val as Train_Loc,tab2.demogrph_val as Fleet
from
(SELECT critq.critq_id,
critq.cmnts,critq.trn_dt
FROM critq
WHERE
LENGTH(TRIM(critq.cmnts)) > 1
AND critq.trn_dt >= to_date('01-Dec-2007', 'DD-MON-YYYY')
AND critq.trn_dt <= to_date('01-Mar-2008', 'DD-MON-YYYY')
AND (critq.questnre_id = 3 or critq.questnre_id= 10)
union all
SELECT critq.critq_id,
critq_ans.cmnts as cmnts,critq.trn_dt
FROM critq,critq_ans
WHERE LENGTH(TRIM(critq_ans.cmnts)) > 1 AND
critq.critq_id=critq_ans.critq_id and
critq.trn_dt >= to_date('01-Dec-2007', 'DD-MON-YYYY')
AND critq.trn_dt <= to_date('01-Mar-2008', 'DD-MON-YYYY')
AND (critq.questnre_id = 3 or critq.questnre_id= 10))qry1,
critq_demogrph tab1,critq_demogrph tab2
where
qry1.critq_id = tab1.critq_id(+) and
qry1.critq_id = tab2.critq_id(+) and
upper(tab1.demogrph_id)='TRAIN_LOC' and
upper(tab2.demogrph_id)='FLEET'
Re: Outer Join two derieved table with where clause [message #327159 is a reply to message #327152] Sat, 14 June 2008 06:43 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

ps_kaushik1 wrote on Sat, 14 June 2008 06:15
I want to left outer join three derieved tables. But It is giving me only the result of inner join as a result I am missing many rows. Can any body help :

Here is the query

select qry1.critq_id,qry1.cmnts,qry1.trn_dt,
tab1.demogrph_val as Train_Loc,tab2.demogrph_val as Fleet
from
(SELECT critq.critq_id,
critq.cmnts,critq.trn_dt
FROM critq
WHERE
LENGTH(TRIM(critq.cmnts)) > 1
AND critq.trn_dt >= to_date('01-Dec-2007', 'DD-MON-YYYY')
AND critq.trn_dt <= to_date('01-Mar-2008', 'DD-MON-YYYY')
AND (critq.questnre_id = 3 or critq.questnre_id= 10)
union all
SELECT critq.critq_id,
critq_ans.cmnts as cmnts,critq.trn_dt
FROM critq,critq_ans
WHERE LENGTH(TRIM(critq_ans.cmnts)) > 1 AND
critq.critq_id=critq_ans.critq_id and
critq.trn_dt >= to_date('01-Dec-2007', 'DD-MON-YYYY')
AND critq.trn_dt <= to_date('01-Mar-2008', 'DD-MON-YYYY')
AND (critq.questnre_id = 3 or critq.questnre_id= 10))qry1,
critq_demogrph tab1,critq_demogrph tab2
where
qry1.critq_id = tab1.critq_id(+) and
qry1.critq_id = tab2.critq_id(+) and
upper(tab1.demogrph_id)='TRAIN_LOC' and
upper(tab2.demogrph_id)='FLEET'






what is this ??
im not understanding anything.. can u format this code and paste the result chart ??
Re: Outer Join two derieved table with where clause [message #327160 is a reply to message #327159] Sat, 14 June 2008 06:49 Go to previous messageGo to next message
ps_kaushik1
Messages: 3
Registered: June 2008
Location: India
Junior Member
Here is the formated query
SELECT qry1.critq_id,
qry1.cmnts,
qry1.trn_dt,
tab1.demogrph_val AS
train_loc,
tab2.demogrph_val AS
fleet
FROM
(SELECT critq.critq_id,
critq.cmnts,
critq.trn_dt
FROM critq
WHERE LENGTH(TRIM(critq.cmnts)) > 1
AND critq.trn_dt >= to_date('01-Dec-2007', 'DD-MON-YYYY')
AND critq.trn_dt <= to_date('01-Mar-2008', 'DD-MON-YYYY')
AND(critq.questnre_id = 3 OR critq.questnre_id = 10)
UNION ALL
SELECT critq.critq_id,
critq_ans.cmnts AS
cmnts,
critq.trn_dt
FROM critq,
critq_ans
WHERE LENGTH(TRIM(critq_ans.cmnts)) > 1
AND critq.critq_id = critq_ans.critq_id
AND critq.trn_dt >= to_date('01-Dec-2007', 'DD-MON-YYYY')
AND critq.trn_dt <= to_date('01-Mar-2008', 'DD-MON-YYYY')
AND(critq.questnre_id = 3 OR critq.questnre_id = 10))
qry1,
critq_demogrph tab1,
critq_demogrph tab2
WHERE qry1.critq_id = tab1.critq_id(+)
AND qry1.critq_id = tab2.critq_id(+)
AND UPPER(tab1.demogrph_id) = 'TRAIN_LOC'
AND UPPER(tab2.demogrph_id) = 'FLEET'
Re: Outer Join two derieved table with where clause [message #327161 is a reply to message #327160] Sat, 14 June 2008 06:53 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

ps_kaushik1 wrote on Sat, 14 June 2008 06:49
Here is the formated query
SELECT qry1.critq_id,
qry1.cmnts,
qry1.trn_dt,
tab1.demogrph_val AS
train_loc,
tab2.demogrph_val AS
fleet
FROM
(SELECT critq.critq_id,
critq.cmnts,
critq.trn_dt
FROM critq
WHERE LENGTH(TRIM(critq.cmnts)) > 1
AND critq.trn_dt >= to_date('01-Dec-2007', 'DD-MON-YYYY')
AND critq.trn_dt <= to_date('01-Mar-2008', 'DD-MON-YYYY')
AND(critq.questnre_id = 3 OR critq.questnre_id = 10)
UNION ALL
SELECT critq.critq_id,
critq_ans.cmnts AS
cmnts,
critq.trn_dt
FROM critq,
critq_ans
WHERE LENGTH(TRIM(critq_ans.cmnts)) > 1
AND critq.critq_id = critq_ans.critq_id
AND critq.trn_dt >= to_date('01-Dec-2007', 'DD-MON-YYYY')
AND critq.trn_dt <= to_date('01-Mar-2008', 'DD-MON-YYYY')
AND(critq.questnre_id = 3 OR critq.questnre_id = 10))
qry1,
critq_demogrph tab1,
critq_demogrph tab2
WHERE qry1.critq_id = tab1.critq_id(+)
AND qry1.critq_id = tab2.critq_id(+)
AND UPPER(tab1.demogrph_id) = 'TRAIN_LOC'
AND UPPER(tab2.demogrph_id) = 'FLEET'




if u are posting like this none can look back your code.

Re: Outer Join two derieved table with where clause [message #327162 is a reply to message #327161] Sat, 14 June 2008 06:57 Go to previous messageGo to next message
ps_kaushik1
Messages: 3
Registered: June 2008
Location: India
Junior Member
I have already posted the formated output
Re: Outer Join two derieved table with where clause [message #327168 is a reply to message #327162] Sat, 14 June 2008 07:56 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you enclose your code within [code]..[/code] tags, it is easier to read because it preserves indentation.

You should read the Forum Guide. If you spend 10 minutes reading it (and following the suggestions it contains) then you will get better answers much faster.

Ross Leishman
Previous Topic: Query to display prime numbers
Next Topic: Problem in creating view
Goto Forum:
  


Current Time: Sun Dec 04 08:23:09 CST 2016

Total time taken to generate the page: 0.19941 seconds