oracle - SQL statement IN condition returns wrong results -
oracle: 11g
i have following (2) queries evaluate same data returns different results.
query 1:
select p.plant_code, p.description plant_desc, e.emp_id, e.division_code, m.module_code, c.course_code plant p, employee e, courses c, modules_revision m, temp_qual_saved_result tqsr e.plant_code = p.plant_code , tqsr.emp_id = e.emp_id , tqsr.course_code = c.course_code , tqsr.module_code = m.module_code , m.current_revision = 'y' , c.course_status = 'a' , tqsr.emp_status = 'a' , e.plant_code in ('002088', '002096', '002105', '009247') , m.module_status = 'a' , e.division_code in (select ud.division_code user_division ud ud.user_id = 'gmd-ad-02') , qual_type = 'm' order p.plant_code,e.emp_id,m.module_code,c.course_code; 
query : 2
select p.plant_code, p.description plant_desc, e.emp_id, e.division_code, m.module_code, c.course_code plant p, employee e, courses c, modules_revision m, temp_qual_saved_result tqsr e.plant_code = p.plant_code , tqsr.emp_id = e.emp_id , tqsr.course_code = c.course_code , tqsr.module_code = m.module_code , m.current_revision = 'y' , c.course_status = 'a' , tqsr.emp_status = 'a' , e.plant_code in ('002088', '002096', '002105', '009247') , m.module_status = 'a' , e.division_code in ('utn1','unc1','bcu2','deu6') , qual_type = 'm' order p.plant_code,e.emp_id,m.module_code,c.course_code; 
query1 results display same value (009247) under plant_code column though brings employees other (3) columns.
query2 results display correct value under plant_code column each employee.
why query1 displaying 1 value under plant_code though brings out employee plant_code contained in ('002088', '002096', '002105', '009247') ?
running subquery "select ud.division_code user_division ud ud.user_id = 'gmd-ad-02'" returns values 'utn1','unc1','bcu2','deu6' same division list.
what difference when indicating list of values vs using select statement?
Comments
Post a Comment