Help with joining the tables

Hi,
I dont know why how ever way i try i cant get the joins on the tables properly.... well i know i have to work hard....if join is not proper the data i extract is also not proper...

Well now i have 3 tables...

ps_operations

Name Null? Type
----------------------------------------- -------- --------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
OPERATION_NBR NOT NULL VARCHAR2(10)
EFFECTIVE_FROM_DT NOT NULL DATE
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
OPERATION_DESC NOT NULL VARCHAR2(70)
HOURS_PER_PIECE_QTY NOT NULL NUMBER(9,6)
PIECES_PER_HOUR_RATE_QTY NOT NULL NUMBER(15,7)
EFFECTIVE_TO_DT DATE
EXTRACT_IND VARCHAR2(1)

composite primary key ("ASSB_PT_NBR_SEQ_ID", "OPERATION_NBR", "EFFECTIVE_FROM_DT")

FOREIGN KEY ("ASSB_PT_NBR_SEQ_ID")

Assembly_parts

Name Null? Type
----------------------------------------- -------- -------------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
BRAND_ID NOT NULL VARCHAR2(4)
ASSEMBLY_PART_NBR NOT NULL VARCHAR2(35)
ASSB_MFG_ORG_SEQ_ID NUMBER(8)
ASSB_PROD_ORG_SEQ_ID NUMBER(8)
PROD_CODE VARCHAR2(4)
ALT_MFG_BILL_OF_MATERIAL_NBR NUMBER(2)
PART_DESC VARCHAR2(35)
PROD_TRADEMARK_ID VARCHAR2(30)
PRODUCT_NM VARCHAR2(70)
MANUFACTURING_LOCATION_ID NOT NULL VARCHAR2(2)

UNIQUE KEY("ASSEMBLY_PART_NBR", "MANUFACTURING_LOCATION_ID")

PRIMARY KEY ("ASSB_PT_NBR_SEQ_ID")
FOREIGN KEY ("ASSB_MFG_ORG_SEQ_ID")
FOREIGN KEY ("ASSB_PROD_ORG_SEQ_ID")
FOREIGN KEY ("MANUFACTURING_LOCATION_ID")
FOREIGN KEY ("BRAND_ID")


ASSEMBLY_PART_DETAILS

Name Null? Type
----------------------------------------- -------- -------------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
EFFECTIVE_FROM_DT NOT NULL DATE
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
EXPORT_SCHEDULE_B_CLASS_CDE VARCHAR2(12)
NAFTA_BASIS_CDE VARCHAR2(1)
EFFECTIVE_TO_DT DATE
GENERIC_PART_DESC VARCHAR2(35)
MARKING_WAIVER_IND VARCHAR2(1)
MARKING_WAIVER_DT DATE
ASSEMBLY_TOTAL_COST_AMT NUMBER(13,6)
ASSEMBLY_FOREIGN_COST_AMT NUMBER(13,6)
NAFTA_PART_ORIGIN_IND VARCHAR2(1)
NAFTA_DETERMINATION_TYPE_CDE VARCHAR2(1)
PCODE VARCHAR2(17)
EXTRACT_IND VARCHAR2(1)
TOTAL_OVERALL_COST_AMT NUMBER(14,6)
DUTY_MATERIAL_COST_AMT NUMBER(14,6)
NON_DUTY_MATERIAL_COST_AMT NUMBER(14,6)
NON_DUTY_PACKAGING_COST_AMT NUMBER(14,6)
DIRECT_LABOR_COST_AMT NUMBER(14,6)
DIRECT_LABOR_PER_HOUR_RATE NUMBER(5,2)
COSTING_DT DATE
COST_ERROR_IND VARCHAR2(1)
OVERHEAD_RATE_AMT NUMBER(6,3)
TOTAL_OVERHEAD_COST_AMT NUMBER(14,6)

PRIMARY KEY ("ASSB_PT_NBR_SEQ_ID", "EFFECTIVE_FROM_DT")
FOREIGN KEY ("ASSB_PT_NBR_SEQ_ID")
FOREIGN KEY ("EXPORT_SCHEDULE_B_CLASS_CDE")
FOREIGN KEY ("NAFTA_BASIS_CDE")
FOREIGN KEY ("NAFTA_DETERMINATION_TYPE_CDE")
FOREIGN KEY ("NAFTA_PART_ORIGIN_IND")

It is so compilcated that i need to extract data form these tables to an external text file....and i am not able to join them....

Query i wrote(not working as the join went wrong or something i have no idea)

select a2.assembly_part_nbr,
sum(p.hours_per_piece_qty), a2.part_desc||' '||'SAP'
from ps_operations p,
assembly_part_details a1,
assembly_parts a2
where a1.assb_pt_nbr_seq_id=a2.assb_pt_nbr_seq_id AND
a2.assb_pt_nbr_seq_id= p.assb_pt_nbr_seq_id AND
a2.brand_id= '0002' AND
to_char(p.effective_to_dt) = 'null' AND
rowid<100
group by a2.assembly_part_nbr, p.assb_pt_nbr_seq_id

ERROR
rowid<100
*
ERROR at line 7:
ORA-00918: column ambiguously defined

Please check it out and let me know where i am wrong... i am in a process of learing so please help me.... i do get silly sometimes when i cant figure it out... i have never worked on CPK and UK....so i dont know how to use them to join the tables,.....Let me know if u need some more data,....

Thanks a lot..

NBSR

Well i see two things:

1) you have a table reference misnamed in:
where a1.assb_pt_nbr_seq_id=2.assb_pt_nbr_seq_id (should be A2)

2)
You keys are all whacky. You are linking only one column from each table, but you are listing composite PK, and you even have one that is both a primary AND a foreign key!!!

Actually i wrote it correct while editing here i made that a.2 mistake..... since keys are like that i am not understading how to join them....do u need any more data about the tables let me know... but try to help me out...

select a2.assembly_part_nbr, sum(p.hours_per_piece_qty), a2.part_desc||' '||'SAP'
from ps_operations p, assembly_part_details a1, assembly_parts a2
where a1.assb_pt_nbr_seq_id= a2.assb_pt_nbr_seq_id AND
a2.assb_pt_nbr_seq_id= p.assb_pt_nbr_seq_id AND
a2.brand_id= '0002' AND
to_char(p.effective_to_dt) = 'null'
group by a2.assembly_part_nbr, p.assb_pt_nbr_seq_id;


select a2.assembly_part_nbr, sum(p.hours_per_piece_qty), a2.part_desc||' '||'SAP'
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Thanks
NBSR

Group by all non aggregate columns (all columns selected and not used in an aggregate function), see this blog post for more details.

select a2.assembly_part_nbr, sum(p.hours_per_piece_qty), a2.part_desc||' '||'SAP'
from ps_operations p, assembly_part_details a1, assembly_parts a2
where a1.assb_pt_nbr_seq_id= a2.assb_pt_nbr_seq_id AND
a2.assb_pt_nbr_seq_id= p.assb_pt_nbr_seq_id AND
a2.brand_id= '0002' AND
to_char(p.effective_to_dt) = 'null' AND
a2.manufacturing_location_id='HM'
group by a2.assembly_part_nbr, p.assb_pt_nbr_seq_id, a2.part_desc

Its working but no rows are returing... it much return some rows... atleast 200....

is anything wrong with the joining of the tables...

NBSR

We dont' and can't know why it doesn't return rows, you might want to post the table structure and some meaningful sample data in form of sql statements (create table ... and insert ...) so that we can reproduce the case.
Now for your query Apart from formatting, I added a couple of notes, check them out

Ok... Thx a lot... i kind of figured it out... i will be adding some test data and make it work...

well i wrote a procedure and a function... totally wrong way i know it.... but couldnt figure out how to correct it also...

FUNCTION

SQL> create or replace
2 FUNCTION dump_data2 ( p_separator in varchar2 ,
3 p_dir in varchar2 ,
4 p_filename in varchar2,
5 mfg_location in varchar2)
6 return number is
7 l_output utl_file.file_type;
8 l_theCursor integer default dbms_sql.open_cursor;
9 l_columnValue varchar2(2000);
10 p_query varchar2;
11 l_status integer;
12 l_colCnt number default 0;
13 l_separator varchar2(10) default '';
14 l_cnt number default 0;
15 begin
16 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
17
18 p_query := select a2.assembly_part_nbr, sum(p.hours_per_piece_qty), a
19 from ps_operations p, assembly_part_details a1, assembly_parts a2
20 where a1.assb_pt_nbr_seq_id= a2.assb_pt_nbr_seq_id AND
21 a2.assb_pt_nbr_seq_id= p.assb_pt_nbr_seq_id AND
22 a2.brand_id= '0002' AND
23 a2.manufacturing_location_id= mfg_location
24 group by a2.assembly_part_nbr, a2.part_desc;
-- I know the error is here... i know that i cant assign a select statement to a variable... but i cant have it in a cursor as it need to take value of mfg_location for procedure and the query it... that the actual and final problem to my knowledge,... any ideas...
25
26 dbms_sql.parse( l_theCursor, p_query,
27 dbms_sql.native );
28
29 for i in 1 .. 255 loop
30 begin
31 dbms_sql.define_column( l_theCursor, i,
32 l_columnValue, 2000 );
33 l_colCnt := i;
34 exception
35 when others then
36 if ( sqlcode = -1007 ) then exit;
37 else
38 raise;
39 end if;
40 end;
41 end loop;
42
43 dbms_sql.define_column( l_theCursor, 1,
44 l_columnValue, 2000 );
45
46 l_status := dbms_sql.execute(l_theCursor);
47
48 loop
49 exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
50 l_separator := '';
51 for i in 1 .. l_colCnt loop
52 dbms_sql.column_value( l_theCursor, i,
53 l_columnValue );
54 utl_file.put( l_output,
55 l_separator || l_columnValue );
56 l_separator := p_separator;
57 end loop;
58 utl_file.new_line( l_output );
59 l_cnt := l_cnt+1;
60 end loop;
61 dbms_sql.close_cursor(l_theCursor);
62
63 utl_file.fclose( l_output );
64 return l_cnt;
65 end dump_data2;
66 /

Errors for FUNCTION DUMP_DATA2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
17/19 PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set
specification>
<an alternatively-quo

LINE/COL ERROR
-------- -----------------------------------------------------------------

25/8 PLS-00103: Encountered the symbol "DBMS_SQL"
26/62 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
. ( , * % & - + / at mod remainder rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || multiset bulk



PROCEDURE


SQL> create or replace
2 procedure Total_hours_per_peice(v_mfg_location in varchar2) as
3 l_rows number;
4 l_mfg_location VARCHAR2;
5 begin
6 l_mfg_location := v_mfg_location;
7 l_rows := dump_data2(' ' ,
8 '/d014/oradata/temp' ,
9 'Total_hours_per_peice.txt', l_mfg_location );
10 dbms_output.put_line( to_char(l_rows) ||
11 'rows extracted to ascii file' );
12 end;
13 /


Errors for PROCEDURE TOTAL_HOURS_PER_PEICE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/23 PLS-00215: String length constraints must be in range (1 ..
32767)

6/7 PL/SQL: Statement ignored
6/17 PLS-00905: object MPC_SOURCE.DUMP_DATA2 is invalid

Honestly speaking i really dont know why its not working.... any ideas let me know....

v_mfg_location is given form Unix when the procedure is called in bracets it value is written....
like Total_hours_per_peice (HM)

if u have any other ways or methods of getting data to external text file... using UTL file... easy way...please give me some examples... i tried many ways finally end up in wrong way... I adopted this method of a procedure call a function and extract data form an example give to me by a friend... it used to work now i dont know i am messing it up so badly that i am totally lost....

I will look forward for replays...

Thanks

NBSR

Have you tried just using less retrictive filters?

I am sorry i dint get u...

I am sorry i dint get u...

You have extra filters like "a2.brand_id= '0002' "

Sometimes its not the query, it the data.