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 Go to next message
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 #217305 is a reply to message #217171] Thu, 01 February 2007 10:36 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
If someone needs table script...

Thanks,
poratips
Re: make one query from multiple query [message #217406 is a reply to message #217305] Fri, 02 February 2007 01:27 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
skooman
Messages: 912
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... Wink

[Updated on: Mon, 05 February 2007 15:11]

Report message to a moderator

Re: make one query from multiple query [message #217839 is a reply to message #217825] Mon, 05 February 2007 17:44 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks once again!
I have tried your query for m DB and we have enough temp tablespace but i csn run partial query and test it how it works.

Thanks,
Previous Topic: How do I trace another session into a table?
Next Topic: Return a ref cursor for a PL/SQL table
Goto Forum:
  


Current Time: Sun Dec 04 00:11:35 CST 2016

Total time taken to generate the page: 0.12521 seconds