INNER Join ORACLE -

INNER Join ORACLE -

i having issue inner bring together oracle. hoping guys review. address table houses addresses of incident , person. need select statement incident address inner bring together persons address well, reason not working.

select distinct incident_people_vw.inc_report_number,trunc((to_number(to_char (offense_status_date,'yyyymmdd'))-to_number(to_char(dob,'yyyymmdd')))/10000) age, incident_people_vw.incident_id, offenses.offense_status_date, incident_people_vw.agncy_cd_agency_code, incident_people_vw.status, incident_people_vw.sex_sex_code, incident_people_vw.race_race_code, incident_people_vw.lname, incident_people_vw.fname, incident_people_vw.dob, offenses.remarks, offense_codes.offense_desc, p.longitude,p.latitude, suspicion_codes.description, p.street_number, p.street_name,p.street_cd_street_type_code,p.city,p.state_cd_state_code,p.zip5, ah.street_number, ah.street_name,ah.street_cd_street_type_code,ah.city,ah.state_cd_state_code,ah.zip5 incident_people_vw, officer_incidents,offenses,offense_codes, officers, addresses p, incident_addresses,offender_suspicions,suspicion_codes,person_addresses d inner bring together addresses ah on d.address_id= ah.address_id officer_incidents.inc_incident_id=incident_people_vw.incident_id , incident_people_vw.incident_id=incident_addresses.incident_id , incident_addresses.address_id=p.address_id , incident_people_vw.incident_id = offenses.inc_incident_id , incident_people_vw.incident_id = offender_suspicions.offnse_inc_incident_id , offender_suspicions.suspicn_cd_suspicion_code =suspicion_codes.suspicion_code , offenses.offns_cd_offense_code = offense_codes.offense_code , officer_incidents.off1_officer_id = officers.officer_id , officer_incidents.orc_role_code='r' , incident_people_vw.status='a' , incident_people_vw.role_role_type in ('a','s') , trunc((to_number(to_char(offense_status_date,'yyyymmdd'))-to_number(to_char (dob,'yyyymmdd')))/10000) <= 17

the exact reason query fail 1 @politank-z wrote ...

you mixing ansi syntax of joins "old" syntax bring together predicates in where clause.

if used ansi syntax consistently, spot problem, beingness person_addresses table bring together lacks actual bring together predicate and, such, db server doing cartesian product instead, devouring of temporary tablespace.

here are, slight iterative revamp of query ...

iteration 1 - introducing basic indentation , remaking where-clause bring together predicates ansi joins

select distinct incident_people_vw.inc_report_number, trunc((to_number(to_char(offense_status_date,'yyyymmdd')) - to_number(to_char(dob,'yyyymmdd')))/10000) age, incident_people_vw.incident_id, offenses.offense_status_date, incident_people_vw.agncy_cd_agency_code, incident_people_vw.status, incident_people_vw.sex_sex_code, incident_people_vw.race_race_code, incident_people_vw.lname, incident_people_vw.fname, incident_people_vw.dob, offenses.remarks, offense_codes.offense_desc, p.longitude,p.latitude, suspicion_codes.description, p.street_number, p.street_name,p.street_cd_street_type_code,p.city,p.state_cd_state_code,p.zip5, ah.street_number, ah.street_name,ah.street_cd_street_type_code,ah.city,ah.state_cd_state_code,ah.zip5 incident_people_vw bring together officer_incidents on officer_incidents.inc_incident_id = incident_people_vw.incident_id bring together offenses on offenses.inc_incident_id = incident_people_vw.incident_id bring together offense_codes on offense_codes.offense_code = offenses.offns_cd_offense_code bring together officers on officers.officer_id = officer_incidents.off1_officer_id bring together incident_addresses on incident_addresses.incident_id = incident_people_vw.incident_id bring together addresses p on p.address_id = incident_addresses.address_id bring together offender_suspicions on offender_suspicions.offnse_inc_incident_id = incident_people_vw.incident_id bring together suspicion_codes on suspicion_codes.suspicion_code = offender_suspicions.suspicn_cd_suspicion_code person_addresses d, inner bring together addresses ah on ah.address_id = d.address_id officer_incidents.orc_role_code = 'r' , incident_people_vw.status = 'a' , incident_people_vw.role_role_type in ('a','s') , trunc((to_number(to_char(offense_status_date,'yyyymmdd'))-to_number(to_char(dob,'yyyymmdd')))/10000) <= 17 ;

iteration 2 - identifying mistakenly forgotten cartesian products (i.e. joins without bring together predicate, because when set bring together predicates clause, easily forget some)

in case it's person_addresses table.

iteration 3 - fixing missing bring together predicates

select distinct incident_people_vw.inc_report_number, trunc((to_number(to_char(offense_status_date,'yyyymmdd')) - to_number(to_char(dob,'yyyymmdd')))/10000) age, incident_people_vw.incident_id, offenses.offense_status_date, incident_people_vw.agncy_cd_agency_code, incident_people_vw.status, incident_people_vw.sex_sex_code, incident_people_vw.race_race_code, incident_people_vw.lname, incident_people_vw.fname, incident_people_vw.dob, offenses.remarks, offense_codes.offense_desc, p.longitude,p.latitude, suspicion_codes.description, p.street_number, p.street_name,p.street_cd_street_type_code,p.city,p.state_cd_state_code,p.zip5, ah.street_number, ah.street_name,ah.street_cd_street_type_code,ah.city,ah.state_cd_state_code,ah.zip5 incident_people_vw bring together officer_incidents on officer_incidents.inc_incident_id = incident_people_vw.incident_id bring together offenses on offenses.inc_incident_id = incident_people_vw.incident_id bring together offense_codes on offense_codes.offense_code = offenses.offns_cd_offense_code bring together officers on officers.officer_id = officer_incidents.off1_officer_id bring together incident_addresses on incident_addresses.incident_id = incident_people_vw.incident_id bring together addresses p on p.address_id = incident_addresses.address_id bring together offender_suspicions on offender_suspicions.offnse_inc_incident_id = incident_people_vw.incident_id bring together suspicion_codes on suspicion_codes.suspicion_code = offender_suspicions.suspicn_cd_suspicion_code bring together person_addresses d on d.<some column> = <some table above ones>.<some column table> bring together addresses ah on ah.address_id = d.address_id officer_incidents.orc_role_code = 'r' , incident_people_vw.status = 'a' , incident_people_vw.role_role_type in ('a','s') , trunc((to_number(to_char(offense_status_date,'yyyymmdd'))-to_number(to_char(dob,'yyyymmdd')))/10000) <= 17 ;

iteration 4 - fixing wonderfully unusual age calculation, introducing consistent formatting , consistent table aliases improve readability

select distinct ip.incident_id, ip.inc_report_number, -- trunc((to_number(to_char(ofs.offense_status_date,'yyyymmdd')) - to_number(to_char(ip.dob,'yyyymmdd')))/10000) age, months_between(ofs.offense_status_date, ip.dob) / 12 age ofs.offense_status_date, ip.agncy_cd_agency_code, ip.status, ip.sex_sex_code, ip.race_race_code, ip.lname, ip.fname, ip.dob, ofs.remarks, oc.offense_desc, aia.longitude, aia.latitude, sc.description, aia.street_number, aia.street_name, aia.street_cd_street_type_code, aia.city, aia.state_cd_state_code, aia.zip5, paa.street_number, paa.street_name, paa.street_cd_street_type_code, paa.city, paa.state_cd_state_code, paa.zip5 incident_people_vw ip bring together officer_incidents oi on oi.inc_incident_id = ip.incident_id bring together offenses ofs on ofs.inc_incident_id = ip.incident_id bring together offense_codes oc on oc.offense_code = ofs.offns_cd_offense_code bring together officers o on o.officer_id = oi.off1_officer_id bring together incident_addresses ia on ia.incident_id = ip.incident_id bring together addresses aia on aia.address_id = ia.address_id bring together offender_suspicions os on os.offnse_inc_incident_id = ip.incident_id bring together suspicion_codes sc on sc.suspicion_code = os.suspicn_cd_suspicion_code bring together person_addresses pa on pa.<some column> = <some table alias above ones>.<some column table> bring together addresses paa on paa.address_id = pa.address_id oi.orc_role_code = 'r' , ip.status = 'a' , ip.role_role_type in ('a','s') , -- trunc((to_number(to_char(ofs.offense_status_date,'yyyymmdd')) - to_number(to_char(ip.dob,'yyyymmdd')))/10000) <= 17 months_between(ofs.offense_status_date, ip.dob) / 12 <= 17 ;

enjoy. , on always utilize ansi bring together syntax.

oracle inner-join