I was reading all about joins and couldnt figure out how to join my tables...i have 3 tables
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)
PK ASSB_PT_NBR_SEQ_ID
FK BRAND_ID
ASSB_MFG_ORG_SEQ_ID
ASSB_PROD_ORG_SEQ_ID
MANUFACTURING_LOCATION_ID
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
FK ASSB_PT_NBR_SEQ_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)
composite primary key ASSB_PT_NBR_SEQ_ID
EFFECTIVE_FROM_DT
FK ASSB_PT_NBR_SEQ_ID
NAFTA_BASIS_CDE
NAFTA_PART_ORIGIN_IND
NAFTA_DETERMINATION_TYPE_CDE
EXPORT_SCHEDULE_B_CLASS_CDE
I tried many ways to join these tables but they are not working....
Please help me out
Query
select a2.assembly_part_nbr as Assembly_Part_NBR,
sum(p.hours_per_piece_qty) As Total_hrs_per_piece,
a2.part_desc||' '||'SAP' as Part_DESC
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
to_char(p.effective_to_dt) is null
group by a2.assembly_part_nbr, a2.part_desc
please explain me where i was wrong and why its not working...
Thanks a lot
Sree