asked on

Oracle SQL Query Question -- Very urgent

Hi All,

Would appreciate any urgent help here pls..

I have a 2 queries..

First query as below:

SELECT
BDVU.EQUIP_INST.DESCR, CIRC_PATH_INST.CIRC_PATH_HUM_ID
FROM
BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST
WHERE
BDVU.EQUIP_INST.TYPE = 'IDT'
AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID
AND BDVU.CIRC_PATH_INST.CIRC_PATH_HUM_ID = '6172273678'

gives 1 row as :

DESCR CIRC_PATH_HUM_ID
IDT 103 BOWDOIN 6172273678

Second query:::

select
BDVU.SITE_INST.SITE_HUM_ID, CIRC_PATH_INST.CIRC_PATH_HUM_ID
from
BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST
WHERE
BDVU.EQUIP_INST.TYPE = 'POT BAY'
AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
AND BDVU.CIRC_PATH_INST.CIRC_PATH_HUM_ID = '6172273678'
AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID

gives 1 row as below..

SITE_HUM_ID CIRC_PATH_HUM_ID
BSTNMABOHBF 6172273678

I want to union them to display result as below

SITE_HUM_ID Descr CIRC_PATH_HUM_ID
BSTNMABOHBF IDT 103 BOWDOIN 6172273678

But, please note that ...

I want to remove hard-coded CIRC_PATH_INST.CIRC_PATH_HUM_ID from both queries...
and want to make such that -- for each value of CIRC_PATH_INST.CIRC_PATH_HUM_ID (%)..
it should execute the top query first -- and then will execute the bottom query for same
CIRC_PATH_INST.CIRC_PATH_HUM_ID and then show the results as shown in the above example..

Any Ideas how to do this?

would appreciate quick help with this pls..

best regards

jammalk

If you are using Oracle9i, I think you can use the following query to get the output u want.

SELECT
x.SITE_HUM_ID
, x.descr
, x.CIRC_PATH_HUM_ID
FROM
( WITH a AS (
      SELECT
       BDVU.EQUIP_INST.DESCR, CIRC_PATH_INST.CIRC_PATH_HUM_ID
      FROM
       BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
       BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST
      WHERE
       BDVU.EQUIP_INST.TYPE = 'IDT'
       AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
       AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
       AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
       AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
       BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
       AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID
--       AND BDVU.CIRC_PATH_INST.CIRC_PATH_HUM_ID = '6172273678'
                         )
      select
       BDVU.SITE_INST.SITE_HUM_ID, a.descr, CIRC_PATH_INST.CIRC_PATH_HUM_ID
      from
       BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
       BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST, a
      WHERE
       BDVU.EQUIP_INST.TYPE = 'POT BAY'
       AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
       AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
       AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
       AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
       BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
--       AND BDVU.CIRC_PATH_INST.CIRC_PATH_HUM_ID = '6172273678'
       AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID
       AND a.circ_path_hum_id = CIRC_PATH_INST.CIRC_PATH_HUM_ID ) x

To make it easier to understand, lets call the first query q1 and second query q2 (with out BDVU.CIRC_PATH_INST.CIRC_PATH_HUM_ID = '6172273678' clause).

Then, the query I gave u looks as follows:
select
x.SITE_HUM_ID
, x.descr
, x.CIRC_PATH_HUM_ID
from
( WITH a AS (q1) q2 ) x.

Let me know if this works for ya.
Cheers
Jammalk

irodov

ASKER

Sorry buddy,

I am using Oracle 8.1.7

Any help in 8.1.7 please..

Seems like I want something as like the below query -- Dont' know how to make it work though..

SELECT
BDVU.SITE_INST.SITE_HUM_ID, z, CIRC_PATH_INST.CIRC_PATH_HUM_ID
FROM
BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST
WHERE
BDVU.EQUIP_INST.TYPE = 'POT BAY'
AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID
AND BDVU.CIRC_PATH_INST.CIRC_PATH_HUM_ID =
(
SELECT
CIRC_PATH_INST.CIRC_PATH_HUM_ID, BDVU.EQUIP_INST.DESCR z
FROM
BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST
WHERE
BDVU.EQUIP_INST.TYPE = 'IDT'
AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID
AND BDVU.CIRC_PATH_INST.CIRC_PATH_HUM_ID = '6172273678')

jammalk

Try this:
select
y.site_hum_id
, x.descr
, x.circ_path_hum_id
FROM
(      SELECT
       BDVU.EQUIP_INST.DESCR, CIRC_PATH_INST.CIRC_PATH_HUM_ID
      FROM
       BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
       BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST
      WHERE
       BDVU.EQUIP_INST.TYPE = 'IDT'
       AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
       AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
       AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
       AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
       BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
       AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID ) x
, (       select
      BDVU.SITE_INST.SITE_HUM_ID, CIRC_PATH_INST.CIRC_PATH_HUM_ID
      from
      BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
       BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST
      WHERE
       BDVU.EQUIP_INST.TYPE = 'POT BAY'
       AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
       AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
       AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
       AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
       BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
       AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID ) y
WHERE
      x.circ_path_hum_id = y.circ_path_hum_id ;

Cheers
Jammalk

irodov

ASKER

I am trying it..

seems like it worked for 1 row (when i hard-coded circ_path_hum_id)

just want to make sure if it works for % (all data also..)

It is just taking lot of time -- since some of them are huge table..

will tell you the results in a while..

regards

Mark Geerlings

Just a clarification of terms, I think you don't want a union, you want a join, right? A union combines multiple rows from two queries into one result set where each query returns the same number of columns and the number of records in the result set equals the sum of the records returned by each query. A join combines columns from two (or more) different tables into the same row(s) where the number of records in the result set is the same as (or less than) the number of records in the larger table.

irodov

ASKER

Jammlk,

Your code is working.. and it works great..It is slow because of huge DB..

But is does work great..

But I actually prepared 2 views for each of two selects, and then ran my select as

select distinct
SITE_HUM_ID, DESCR
FROM TEMP_IDT,
TEMP_POTBAY
WHERE
TEMP_IDT.CIRC_PATH_HUM_ID = TEMP_POTBAY.CIRC_PATH_HUM_ID

TEMP_IDT is a view from top select
TEMP_POTDAY is a view for bottom select..

I have another urgent Q, if you can help pls.. and I will close this Q..

With the above data, I get some 400 rows right ?(from views)

I want to run following roughly following update --

update equip_attr_settings
set attr_value=<site_hum_id> -- i get from above select
where
and c.attr_name= 'DOWNSTREAM COLO'
and c.EQUIP_INST_ID =d.EQUIP_INST_ID
and d.DESCR=<desc> -- i got from above select

In this,
equip_attr_settings c, EQUIP_INST d

Any ideas how to accomplish this please..

I appreciate your help..

Regards

Danielzt

Try this:

This is the simple way just use your twos SQL as a on-line view and then join them.

select b.SITE_HUM_ID,a.Desc,a.CIRC_PATH_HUM_ID
from
(
SELECT
BDVU.EQUIP_INST.DESCR, CIRC_PATH_INST.CIRC_PATH_HUM_ID
FROM
BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST
WHERE
BDVU.EQUIP_INST.TYPE = 'IDT'
AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID
-- AND BDVU.CIRC_PATH_INST.CIRC_PATH_HUM_ID = '6172273678'
) a,
(
select
BDVU.SITE_INST.SITE_HUM_ID, CIRC_PATH_INST.CIRC_PATH_HUM_ID
from
BDVU.CARD_INST, BDVU.CIRC_PATH_INST, BDVU.EQUIP_INST, BDVU.EPA,
BDVU.CIRC_PATH_ELEMENT, BDVU.SITE_INST
WHERE
BDVU.EQUIP_INST.TYPE = 'POT BAY'
AND BDVU.EQUIP_INST.EQUIP_INST_ID = BDVU.CARD_INST.EQUIP_INST_ID
AND BDVU.CARD_INST.CARD_INST_ID = BDVU.EPA.CARD_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.PORT_INST_ID = BDVU.EPA.PORT_INST_ID
AND BDVU.CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
BDVU.CIRC_PATH_INST.CIRC_PATH_INST_ID
-- AND BDVU.CIRC_PATH_INST.CIRC_PATH_HUM_ID = '6172273678'
AND BDVU.SITE_INST.SITE_INST_ID = BDVU.EQUIP_INST.SITE_INST_ID ) b

where a.CIRC_PATH_HUM_ID = b.CIRC_PATH_HUM_ID

irodov

ASKER

Danielzt,

Jammlk has already given the same solution .. I appreciate your help with this anyway..

Can you or Jammlk or anybody can help me with my other question please ..

How to update based on this select.. please read my second question just before Danielzt's comment..

I would really appreciate your help with this last Question .... please...

regards

jammalk

Hmm.. Question is not clear..!

R u trying to update a new table here called EQUIP_ATTR_SETTINGS ?

Can u paste the column structure of this table here ?

Also, let me know if u r trying to update any other tables..? Coz.. in the where clause I see "equip_attr_settings c, EQUIP_INST d" tables being used.

Jammalk

irodov

ASKER

Jammlk,
Thanks for response.. Sorry for not explaining it clearly..

okay..

there are 2 views --
TEMP_IDT and TEMP_POTBAY (from the selects you helped me with already)..

TEMP_IDT with following columns

DESCR
CIRC_PATH_HUM_ID

TEMP_POTBAY with following columns

SITE_HUM_ID
CIRC_PATH_HUM_ID

Lets assume, I create a table as below:

CREATE TABLE TEMP_IDT_POTBAY_MAPPING AS
( select distinct SITE_HUM_ID, DESCR FROM TEMP_IDT, TEMP_POTBAY WHERE TEMP_IDT.CIRC_PATH_HUM_ID = TEMP_POTBAY.CIRC_PATH_HUM_ID)

So now

TEMP_IDT_POTBAY_MAPPING has 2 columns --

SITE_HUM_ID AND DESCR (Mapping)

I want to write an update as below:

update EQUIP_ATTR_SETTINGS
set EQUIP_ATTR_SETTINGS.ATTR_VALUE = <SITE_HUM_ID> -- from above mapping table
WHERE
EQUIP_ATTR_SETTINGS.EQUIP_INST_ID=
(select EQUIP_ATTR_SETTINGS.EQUIP_INST_ID
from EQUIP_ATTR_SETTINGS, EQUIP_INST, VAL_ATTR_NAME
where VAL_ATTR_NAME.ATTR_NAME='DOWNSTREAM COLO'
and EQUIP_ATTR_SETTINGS.EQUIP_INST_ID=EQUIP_INST.EQUIP_INST_ID
and EQUIP_INST.DESCR= <DESCR> -- from above mapping table
and VAL_ATTR_NAME.VAL_ATTR_INST_ID = EQUIP_ATTR_SETTINGS.VAL_ATTR_INST_ID
)

Got it?

Let me know if it still not clear..

Regards

ASKER CERTIFIED SOLUTION

jammalk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial