Home » SQL & PL/SQL » SQL & PL/SQL » Oracle function within a subquery
Oracle function within a subquery [message #261832] Thu, 23 August 2007 14:24 Go to next message
njgirl
Messages: 8
Registered: August 2007
Junior Member
OS :SunOS5.8
DB : oracle 10.2.0.3

Hi


I have query 1 and within that query, i have to call query 2 to populate w.fallout_type. How do i do that? Any suggestions?

query 1

SELECT f.svc_Order_Id,
w.Fallout_WorkList_Id,
w.WorkList_Type,
w.Fallout_Type, To_char(w.Entered_Date,'YYYY-MM-DD HH24:MI:SS'),
w.Order_Number,
w.ckl_Number,
To_char(w.Assigned_Date,'YYYY-MM-DD HH24:MI:SS'),
w.Assign_To_Name,
To_char(w.Completed_Date,'YYYY-MM-DD HH24:MI:SS'),
To_char(w.Pickup_Date,'YYYY-MM-DD HH24:MI:SS'),
w.Active_Time
FROM IdAt.Order_Event_Fact f,
IdAt.Tb_Event_Type e,
IdAt.Tb_Service_Dim s,
IdAt.Design_WorkList_mv w,
IdAt.Tb_Day d
WHERE f.svc_Order_Id IN (SELECT svc_Order_Id
FROM Design_WorkList_mv w
WHERE WorkList_Type IN (1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
26)
AND (Completed_Date IS NULL
OR Status = 5))
AND f.Event_Type_Id = e.Event_Type_Id
AND f.svc_Order_Id = w.svc_Order_Id
AND f.Service_Dim_Id = s.Service_Dim_Id
AND f.Completed_Date_Id = d.Id
AND e.Event_Type = 100
AND e.Event_Level = 'O'
--and d.the_date_week='03-AUG-2004'
AND d.The_Date_Month = '01-AUG-2004' -- this can be replaced with the right date range
AND s.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 s.Sots_ta IN ('N',
'C',
'D')


Query 2

SELECT c.NAME AS Fallout_Type
FROM IdAt.Design_WorkList_mv w,
IdAt.Const2Name c
WHERE w.Fallout_Type = c.VALUE
AND c.Group_num = 12

Re: Oracle function within a subquery [message #261833 is a reply to message #261832] Thu, 23 August 2007 14:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Any suggestions?
Write a function.

How many rows get returned by Query 2?
It appears that Query 2 is invariant, so just hard code the results into Query 1.

[Updated on: Thu, 23 August 2007 14:28] by Moderator

Report message to a moderator

Re: Oracle function within a subquery [message #261834 is a reply to message #261832] Thu, 23 August 2007 14:38 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
njgirl wrote on Thu, 23 August 2007 15:24

AND d.The_Date_Month = '01-AUG-2004' -- this can be replaced with the right date range



Do not compare a DATE column to a character string. Please use the TO_DATE function appropriately.

NB: A formatted piece of code will get you a quicker answer. Please read the sticky.
Re: Oracle function within a subquery [message #261835 is a reply to message #261832] Thu, 23 August 2007 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to follow the guidelines as we asked you in your previous posts?

Regards
Michel
Re: Oracle function within a subquery [message #261837 is a reply to message #261833] Thu, 23 August 2007 14:39 Go to previous messageGo to next message
njgirl
Messages: 8
Registered: August 2007
Junior Member
Query 2 returned around 1,974,678 rows. How do i hard code the results in to query 1? pls help
Re: Oracle function within a subquery [message #261913 is a reply to message #261837] Fri, 24 August 2007 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'll be glad to help you when you'll follow the guidelines.

Regards
Michel
Re: Oracle function within a subquery [message #262101 is a reply to message #261913] Fri, 24 August 2007 08:55 Go to previous messageGo to next message
njgirl
Messages: 8
Registered: August 2007
Junior Member
Thank you Michel for your response. I have used the oracle formatter as suggested in the guidelines. Embarassed I hope i got it right this time.As i mentioned earlier i need to include query 2 in query 1 to populate fallout_type column in query 1. I have tried doing as below but getting an error
"RA-01427: single-row subquery returns more than one row". can you pls help



SELECT f.svc_Order_Id,
w.Fallout_WorkList_Id,
w.WorkList_Type,
(SELECT y.NAME
FROM IdAt.Design_WorkList x,
IdAt.Const2Name y
WHERE x.Fallout_Type = y.VALUE
AND y.Group_num = 12) Fallout_Type,
To_char(w.Entered_Date,'YYYY-MM-DD HH24:MI:SS'),
w.Order_Number,
w.ckl_Number,
To_char(w.Assigned_Date,'YYYY-MM-DD HH24:MI:SS'),
w.Assign_To_Name,
To_char(w.Completed_Date,'YYYY-MM-DD HH24:MI:SS'),
To_char(w.Pickup_Date,'YYYY-MM-DD HH24:MI:SS'),
w.Active_Time
FROM IdAt.Order_Event_Fact f,
IdAt.Tb_Event_Type e,
IdAt.Tb_Service_Dim s,
IdAt.Design_WorkList_mv w,
IdAt.Tb_Day d
WHERE f.svc_Order_Id IN (SELECT svc_Order_Id
FROM Design_WorkList_mv w
WHERE WorkList_Type IN (1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
26)
AND (Completed_Date IS NULL
OR Status = 5))
AND f.Event_Type_Id = e.Event_Type_Id
AND f.svc_Order_Id = w.svc_Order_Id
AND f.Service_Dim_Id = s.Service_Dim_Id
AND f.Completed_Date_Id = d.Id
AND e.Event_Type = 100
AND e.Event_Level = 'O'
AND s.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 s.Sots_ta IN ('N',
'C',
'D')
Re: Oracle function within a subquery [message #262103 is a reply to message #262101] Fri, 24 August 2007 09:10 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Guidelines also explain how to use [code][/code] tags around your code and that you can see what you will post clicking on "Preview Message" before "Submit Reply".

Regards
Michel
Previous Topic: Grouping DATA
Next Topic: how to find all packages and procedures form user_objects
Goto Forum:
  


Current Time: Sun Dec 04 22:34:16 CST 2016

Total time taken to generate the page: 0.09958 seconds