Home » SQL & PL/SQL » SQL & PL/SQL » make one query from multiple query
| make one query from multiple query [message #217171] |
Wed, 31 January 2007 23:08  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
I have few queries and needs to make a one query?
How can I make it?
Please see the attached file which highlights the table relationship and each query with desired output.
Thanks in advance!
|
|
|
|
|
|
| Re: make one query from multiple query [message #217406 is a reply to message #217305] |
Fri, 02 February 2007 01:27  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
What did you try so far and where did you get stuck?
You can't expect us to go trough all of your code and write the new code for you, some of us do have a job or other things-to-do-today, you know...
|
|
|
|
| Re: make one query from multiple query [message #217512 is a reply to message #217406] |
Fri, 02 February 2007 10:46 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks, sorry about that. I thought I have attached the small set of each sql query but i was not much expert to make one common query as it was getting difficult to make outer query from inner query to include all the fileds from different table set s and each query has it's own criteria which i needs to be use in my subset of query then needs to make one query using all this criteria ot make one query. I can't use the equi join here as it's not that all the condition needs to be satisfied but it should satisfy for individual query, please see the query i was trying build up.
Query
======
select l.location_gid, L.City ||','||L.Province_Code||' '|| L.Postal_Code "Origin",l.Province_Code,
LR.location_gid, lR.Location_Refnum_Value,
x.x_lane_gid, x.dest_province_code, x.source_province_code, x.source_location_gid, x.dest_location_gid,
R.rate_geo_gid
--, R.effective_date, R.expiration_date, rc.charge_action, rc.charge_amount
from X_lane X, location l, location_refnum LR, RATE_GEO R, RATE_GEO_COST_GROUP RG, RATE_GEO_COST RC
where l.location_gid in (Select lr.location_gid from location_refnum LR where lr.Location_Refnum_Value = 'NWL_WAREHOUSE')
and X.source_province_code in (select l.province_code from location l where l.location_gid in
(Select lr.location_gid from location_refnum LR where lr.Location_Refnum_Value = 'NWL_WAREHOUSE')
)
and x.dest_province_code is not null
and substr(R.x_lane_gid, 1, 13) in (select lr.location_gid from location_refnum
where lr.Location_Refnum_Value in ('NWL_TL_CORE')
and lr.location_gid in ('NEWELLCO.HJBT','NEWELLCO.TTFE','NEWELLCO.RBTW','NEWELLCO.SMBK','NEWELLCO.BGXP',
'NEWELLCO.SHPK','NEWELLCO.SCNN','NEWELLCO.CRCR','NEWELLCO.DART','NEWELLCO.ADSJ')
)
and trunc(r.expiration_date) > trunc(sysdate)
and r.rate_geo_gid in
(select rg.rate_geo_gid from RATE_GEO_COST_GROUP rg where rg.rate_geo_gid in
(select r.rate_geo_gid from RATE_GEO r where substr(R.x_lane_gid, 1, 13) in
(select lr.location_gid from location_refnum lr
where lr.Location_Refnum_Value in ('NWL_TL_CORE')
and lr.location_gid in ('NEWELLCO.HJBT','NEWELLCO.TTFE','NEWELLCO.RBTW','NEWELLCO.SMBK','NEWELLCO.BGXP',
'NEWELLCO.SHPK','NEWELLCO.SCNN','NEWELLCO.CRCR','NEWELLCO.DART','NEWELLCO.ADSJ')
)
)
)
and rc.rate_geo_cost_group_gid in
(select rg.rate_geo_cost_group_gid from RATE_GEO_COST_GROUP rg
where rg.rate_geo_gid in
(select r.rate_geo_gid from RATE_GEO r where substr(R.x_lane_gid, 1, 13) in
(select lr.location_gid from location_refnum
where lr.Location_Refnum_Value in ('NWL_TL_CORE')
and lr.location_gid in ('NEWELLCO.HJBT','NEWELLCO.TTFE','NEWELLCO.RBTW','NEWELLCO.SMBK','NEWELLCO.BGXP',
'NEWELLCO.SHPK','NEWELLCO.SCNN','NEWELLCO.CRCR','NEWELLCO.DART','NEWELLCO.ADSJ')
)
)
and rc.charge_action in ('A', 'M'))
Thanks for time nad help! Really appreciated.
|
|
|
|
| Re: make one query from multiple query [message #217529 is a reply to message #217512] |
Fri, 02 February 2007 12:16 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Poratips,
Next time you might want to format your code to make it easier to read, now I've done it for your:
SELECT l.location_gid
,l.city || ',' || l.province_code || ' ' || l.postal_code "Origin"
,l.province_code
,lr.location_gid
,lr.location_refnum_value
,x.x_lane_gid
,x.dest_province_code
,x.source_province_code
,x.source_location_gid
,x.dest_location_gid
,r.rate_geo_gid
--, R.effective_date, R.expiration_date, rc.charge_action, rc.charge_amount
FROM x_lane x
,location l
,location_refnum lr
,rate_geo r
,rate_geo_cost_group rg
,rate_geo_cost rc
WHERE l.location_gid IN
(SELECT lr.location_gid
FROM location_refnum lr
WHERE lr.location_refnum_value = 'NWL_WAREHOUSE')
AND x.source_province_code IN
(SELECT l.province_code
FROM location l
WHERE l.location_gid IN
(SELECT lr.location_gid
FROM location_refnum lr
WHERE lr.location_refnum_value = 'NWL_WAREHOUSE'))
AND x.dest_province_code IS NOT NULL
AND substr(r.x_lane_gid
,1
,13) IN
(SELECT lr.location_gid
FROM location_refnum
WHERE lr.location_refnum_value IN ('NWL_TL_CORE')
AND lr.location_gid IN
('NEWELLCO.HJBT', 'NEWELLCO.TTFE', 'NEWELLCO.RBTW',
'NEWELLCO.SMBK', 'NEWELLCO.BGXP', 'NEWELLCO.SHPK',
'NEWELLCO.SCNN', 'NEWELLCO.CRCR', 'NEWELLCO.DART',
'NEWELLCO.ADSJ'))
AND trunc(r.expiration_date) > trunc(SYSDATE)
AND r.rate_geo_gid IN
(SELECT rg.rate_geo_gid
FROM rate_geo_cost_group rg
WHERE rg.rate_geo_gid IN
(SELECT r.rate_geo_gid
FROM rate_geo r
WHERE substr(r.x_lane_gid
,1
,13) IN
(SELECT lr.location_gid
FROM location_refnum lr
WHERE lr.location_refnum_value IN ('NWL_TL_CORE')
AND lr.location_gid IN
('NEWELLCO.HJBT', 'NEWELLCO.TTFE',
'NEWELLCO.RBTW', 'NEWELLCO.SMBK',
'NEWELLCO.BGXP', 'NEWELLCO.SHPK',
'NEWELLCO.SCNN', 'NEWELLCO.CRCR',
'NEWELLCO.DART', 'NEWELLCO.ADSJ'))))
AND rc.rate_geo_cost_group_gid IN
(SELECT rg.rate_geo_cost_group_gid
FROM rate_geo_cost_group rg
WHERE rg.rate_geo_gid IN
(SELECT r.rate_geo_gid
FROM rate_geo r
WHERE substr(r.x_lane_gid
,1
,13) IN
(SELECT lr.location_gid
FROM location_refnum
WHERE lr.location_refnum_value IN ('NWL_TL_CORE')
AND lr.location_gid IN
('NEWELLCO.HJBT', 'NEWELLCO.TTFE',
'NEWELLCO.RBTW', 'NEWELLCO.SMBK',
'NEWELLCO.BGXP', 'NEWELLCO.SHPK',
'NEWELLCO.SCNN', 'NEWELLCO.CRCR',
'NEWELLCO.DART', 'NEWELLCO.ADSJ')))
AND rc.charge_action IN ('A', 'M'))
So, basically what you're doing in this piece of code is that you're selecting from a number of tables and instead of joining them, you're creating just about one subquery for each table to restrict the results.
(a) since you're not joining the 6 base tables, you'll get a huge cartesian product and (b) most of the subqueries look unnecessary anyway. So my advice would be: get rid of all the subqueries, join the tables properly, add all filter predicates directly and than look if any need for a subquery clearly remains. A reason for a subquery could be two contradicting where clauses, although most of the "duplicate" code in your statement looks as if it can be accomplished using either an OR statement or two aliases for the same table, like this:
select...
from employees emp1
, employees emp2
, departments dept
where emp1.dept_id = dept.dept_id
and emp2.dept_id = dept.dept_id
and emp1.col1 = 'xxx'
and emp2.col1 = 'yyy'
Try drawing a model for you statement; it doesn't have to be a classic ERD, just something to enable you to see a structure.
Good luck!
|
|
|
|
| Re: make one query from multiple query [message #217643 is a reply to message #217529] |
Sat, 03 February 2007 15:58 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks for your detail response and query. Earlier I tried to make little diagram and from that I have build the each set of query but got stuck when tried to make big query as didn't get properly join the query and it was getting little difficulty when I tried to use inner query but having trouble to display more fields from another tables.
Thanks
|
|
|
|
| Re: make one query from multiple query [message #217797 is a reply to message #217529] |
Mon, 05 February 2007 10:09 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Hi skooman,
Thanks for your detail query, i have tried to run it but giving me oracle error: Unable to extend temp segments.
Any idea to get rid of this error?
Thanks once again and really appreciated your response!
|
|
|
|
| Re: make one query from multiple query [message #217825 is a reply to message #217797] |
Mon, 05 February 2007 15:08 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
It could be that you're hitting the limits of the temp tablespace so you might want to enlarge it.
However, I would first examine your code very closely to make sure there are no joins missing (since you started without any joins...). Because a cartesian product requires loads of temp tablespace, chances are that that's the cause of the error.
Again some general advice: start small. Join two or three tables, add some predicates, etc. and check if it works. If that's okay, than add another table (don't forget to join it), and so on, up to the complete solution.
Edit: now I see you say thanks for the detail query, tried to run it and it gives the temp tablespace error. Do you mean that the emp dept example did raise a temp tablespace error? That's just about impossible... Either you missed the join claused or your database has about the smallest temp tablespace ever seen on this planet... 
[Updated on: Mon, 05 February 2007 15:11] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 09:28:10 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
