Home » SQL & PL/SQL » SQL & PL/SQL » Inner and Left outer join query help
Inner and Left outer join query help [message #257243] Tue, 07 August 2007 20:25 Go to next message
njgirl
Messages: 8
Registered: August 2007
Junior Member
Hi

I have created query 1 joining 4 dim tables to oef fact table as inner joins and 4dim tables as left outer joins. I have to give a filter criteria along with the joins. What i did here was to apply the filters at the end. I want to apply the filters in the beginning as i join the tables. Can someone suggest me a way? Please help Embarassed

query 1

SELECT sod.svc_order_id,
sod.status,
tet.event_type,
tet.event_level,
tsd.SOTS_TA,
tsd.SERVICE_NAME,
dwm.worklist_type,
td.id
FROM ((((((((idat.tb_event_type tet INNER JOIN idat.order_event_fact oef ON tet.event_type_id = oef.event_type_id)
INNER JOIN idat.tb_service_dim tsd ON oef.service_dim_id=tsd.service_dim_id)
INNER JOIN idat.customer_mv cmv ON oef.customer_id= cmv.customer_id)
INNER JOIN idat.svc_order_dtl sod ON oef.svc_order_id=sod.svc_order_id)
left OUTER JOIN idat.design_worklist_mv dwm on oef.svc_order_id= dwm.svc_order_id)
left OUTER JOIN idat.tb_day td on oef.completed_date_id= td.id)
left OUTER JOIN idat.cust_order_extension_mv coem on oef.svc_order_id= coem.svc_order_id)
left OUTER JOIN idat.pon_mv pm ON oef.svc_order_id=pm.svc_order_id)
where sod.status not in (2,5,6)
and tet.event_type = 100
and tet.event_level ='O'
and tsd.SOTS_TA IN ('N', 'C', 'D')
and tsd.SERVICE_NAME NOT IN (
'INCS Data',
'INCS Nodal',
'International FR',
'International PL',
'Nodal',
'Outbound Nodal',
'SPM Nodal',
'SW AFSC',
'SW Voice Inbound',
'SW Voice Other')
and dwm.worklist_type IN (1,2,3,4,5,6,7,8,9,10,11,12,26,81,82,84,126)
and pm.cancel_del_ind is null;
Re: Inner and Left outer join query help [message #257246 is a reply to message #257243] Tue, 07 August 2007 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
No Operating System name or version.
No Oracle version to 4 decimal places.
No code formatting (see http://www.orafaq.com/forum/t/59964/74940/)
No DDL.
Re: Inner and Left outer join query help [message #257248 is a reply to message #257246] Tue, 07 August 2007 21:14 Go to previous messageGo to next message
njgirl
Messages: 8
Registered: August 2007
Junior Member
Hi

I am using oracle 10g version on unix.
Re: Inner and Left outer join query help [message #257249 is a reply to message #257243] Tue, 07 August 2007 21:18 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
No Operating System name or version.
No Oracle version to 4 decimal places.
>I am using oracle 10g version on unix.
Your response provided NO useful information.

You're On Your Own (YOYO)!

[Updated on: Tue, 07 August 2007 21:20] by Moderator

Report message to a moderator

Re: Inner and Left outer join query help [message #257252 is a reply to message #257249] Tue, 07 August 2007 21:47 Go to previous messageGo to next message
njgirl
Messages: 8
Registered: August 2007
Junior Member
Oracle version -Oracle Database 10g Release 2 (10.2.0.1)

Operating System = UNIX (do not have the version details) Embarassed
Re: Inner and Left outer join query help [message #257254 is a reply to message #257243] Tue, 07 August 2007 21:53 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
uname -a
Re: Inner and Left outer join query help [message #257255 is a reply to message #257252] Tue, 07 August 2007 21:55 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just add the filter criteria to the ON clause of the relevant table.

left OUTER JOIN idat.design_worklist_mv dwm 
on oef.svc_order_id= dwm.svc_order_id
AND dwm.worklist_type IN (1,2,3,4,5,6,7,8,9,10,11,12,26,81,82,84,126)


Ross Leishman
Previous Topic: SQL Oracle update - append (merged by LF)
Next Topic: DEFAULT SETTINGS OF SQL
Goto Forum:
  


Current Time: Sat Dec 03 16:04:13 CST 2016

Total time taken to generate the page: 0.09417 seconds