Nested Query

BrianWren · January 28th, 2005, 05:48 PM

I have a table, MTL, which has a related table, EST_MH.
The estimated manhours are broken down by discpline (Weldor, Electrician, etc.) so as to know how much time of what type worker the material item requires.

I want to return a set of records with the sum of the different disciplines for each material item.

The query Iâm presenting here restricts the number of records through restricting the IDs in MTL. Thatâs just to get the thing working in the first place. (FS is the schema)

Code:

SELECT A.ID, A.ITM_NO, S.HOURS

FROM   FS.MTL A, (SELECT SUM(HRS) HOURS
                   FROM   FS.EST_MH B
                   WHERE  B.MTL_ID = A.ID
                  ) S

WHERE A.ID IN (332569, 332621, 332951, 332964,
                332993, 498053, 498054);

                  WHERE  B.MTL_ID = A.ID
                                      *
ERROR at line 4:
ORA-00904: invalid column name

(I separated the SQL clauses to make them easier to read.)

Can this even be done?
.

htummala · January 31st, 2005, 11:25 PM

i did not understand the column HRS ,WHAT IT ACTUALLY

CARRIES?IS THE TABLE(EST_MH) STRUCTURE LIKE THIS?
MTL_ID WELDER ELECTRICIAN ----
33293 7 5 ------

thr

BrianWren · February 1st, 2005, 12:09 PM

Columns
MTL_ID
HRS
BR_ID

BR_ID would be like 'ELEC', 'MCHRY', 'HVAC', etc. for Electrical, Heating-Ventilation-Air-Conditioning, and so on.

Material item ID=1 might have 3 items in EST_MH, with 2 hours of HVAC, 1 hour of MCHRY, and 3 hours of Electrical.
Material item ID=2 might have 2 items in EST_MH, with 3 hours of HVAC, and 1 hours of Electrical.

So I want to see for ID=1: HOURS=6.
I want to see for ID=2: HOURS=4.

Code:

    MTL              EST_MH
ID  ITM_NO     MTL_ID  HRS  BR_ID
âââââââââââ   âââââââââââââââââââââ
 1   A2  - - - - 1      2   HVAC
                 1      1   MCHRY
                 1      3   ELEC
 2   F9  - - - - 2      3   HVAC
                 2      1   ELEC

SQL output:

 ID  ITM_NO HOURS
---- ------ -----
  1    A2     6
  2    F9     4

stu9820 · February 1st, 2005, 12:28 PM

Try FS.MTL.ID instead of the alias.

BrianWren · February 1st, 2005, 01:27 PM

Result:

Code:

SELECT A.ID, A.ITM_NO, S.HOURS
FROM   FS.MTL A, (SELECT SUM(HRS) HOURS
                  FROM   FS.EST_MH B
                  WHERE  B.MTL_ID = FS.MTL.ID
                 ) S
WHERE A.ID IN (332569, 332621, 332951, 332964,
               332993, 498053, 498054)

                  WHERE  B.MTL_ID = FS.MTL.ID
                                           *
ERROR at line 4:
ORA-00904: invalid column name

But:

  1  SELECT A.ID, A.ITM_NO
  2  FROM   FS.MTL A
  3  WHERE A.ID IN (332569, 332621, 332951, 332964,
  4*                 332993, 498053, 498054)
SQL> /

        ID ITM_NO
---------- ------
    332569 1
    332621 1
    332951 1
    332964 1
    332993 1
    498053 4
    498054 5

7 rows selected.

(Shows that ID is a valid field...)

azizmasih · February 1st, 2005, 01:45 PM

Hi,

The following testcase shows the query to fetch your required output:

SQL> select * from mtl;

        ID ITM_NO
---------- ----------
         1 A2
         2 F9

SQL>
SQL> select * from est_mh;

    MTL_ID HRS BR_ID
---------- ---------- ----------
         1 2 HVAC
         1 1 MCHRY
         1 3 ELEC
         2 3 HVAC
         2 1 ELEC

SQL>
SQL> select a.id, a.itm_no, sum(b.hrs)
  2 from mtl a, est_mh b
  3 where a.id = b.mtl_id
  4 group by a.id, a.itm_no ;

        ID ITM_NO SUM(B.HRS)
---------- ---------- ----------
         1 A2 6
         2 F9 4

SQL>

HTH
aziz