Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 28th, 2005, 05:48 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default Nested Query

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?
.
 
Old January 31st, 2005, 11:25 PM
Registered User
 
Join Date: Jan 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 1st, 2005, 12:09 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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
 
Old February 1st, 2005, 12:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Try FS.MTL.ID instead of the alias.

 
Old February 1st, 2005, 01:27 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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...)
 
Old February 1st, 2005, 01:45 PM
Authorized User
 
Join Date: Jul 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested query deb1980 ASP.NET 2.0 Basics 2 April 10th, 2007 04:38 AM
Nested Query Help Needed Corey Access 2 April 26th, 2006 02:17 PM
Nested Query or Join? rlull SQL Server 2000 4 November 10th, 2005 05:23 PM
Nested Query teclo MySQL 1 March 22nd, 2005 07:20 AM
Nested Query Trouble ashley_y ADO.NET 2 December 23rd, 2003 01:32 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.