Oracle SQL Query Question -- Very urgent

irodov
irodov used Ask the Experts™
on
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
       
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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

Author

Commented:
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')

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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 GeerlingsDatabase Administrator

Commented:
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.

Author

Commented:
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

Commented:

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





Author

Commented:
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

Commented:
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

Author

Commented:
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
Commented:
K.. Try this:

First, include column EQUIP_INST_ID in TEMP_IDT and TEMP_POTBAY.
The SELECT statement that I gave u earlier is now modified as follows:(Include equip_inst.equip_inst_id in both statements select list) and create a view VIEW1 as follows:

CREATE OR REPLACE VIEW view1 AS
select
    temp_potbay.site_hum_id
  , temp_idt.descr
  , temp_idt.circ_path_hum_id
  , temp_idt.equip_inst_id
FROM
     (     SELECT
      BDVU.EQUIP_INST.DESCR, CIRC_PATH_INST.CIRC_PATH_HUM_ID, equip_inst.equip_inst_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 ) temp_idt
   , (      select
     BDVU.SITE_INST.SITE_HUM_ID, CIRC_PATH_INST.CIRC_PATH_HUM_ID, equip_inst.equip_inst_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 ) temp_potbay
WHERE
     temp_idt.circ_path_hum_id = temp_potbay.circ_path_hum_id ;
/

Now, update statement should look as follows:

UPDATE
    equip_attr_settings eq
SET
    attr_value            =      ( SELECT
                              site_hum_id
                          FROM
                              view1 v1
                          WHERE
                              v1.equip_inst_id            =      eq.equip_inst_id )
WHERE
                  EXISTS      ( SELECT
                              1
                          FROM
                              val_attr_name van
                          WHERE
                              van.val_attr_inst_id      =      eq.val_attr_inst_id
                          AND van.attr_name            =      'DOWNSTREAM COLO' )
/

Lemme know if that is what u r looking for. Otherwise, need more explanation.
Thanks
Jammalk

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial