CODEWITHSUNDEEP
oracle-database
Aravind
Aravind

Reputation: 2198

Oracle inner query

My query

select kc.prod_id, kc.prod_actv_ts 
from   kit_cmpnt kc ,kit_cmpnt_stock kcs, prod p
where kc.cmpnt_cd='016' 
and   kcs.kit_cmpnt_nbr= kc.kit_cmpnt_nbr 
and   kcs.stock_id=1 
and   kcs.prod_id=kc.prod_id 
and   kcs.prod_actv_ts=kc.prod_actv_ts 
and   p.prod_id= kc.prod_id 
and   p.prod_actv_ts= kc.prod_actv_ts 
and   p.prod_inactv_ts is null;

I want to get a distinct combination of kc.prod_id, kc.prod_actv_ts

like distinct(kc.prod_id, kc.prod_actv_ts)

But what i am getting is a combination of repeated prod_id and prod_actv_ts

please help

Upvotes: 0

Views: 486

Answers (3)

Gary Myers
Gary Myers

Reputation: 35401

I'd restructure the query as follows :

select kc.prod_id, kc.prod_actv_ts 
from   kit_cmpnt kc 
where  kc.cmpnt_cd='016' 
and exists
       (select 1 from kit_cmpnt_stock kcs 
        where kcs.stock_id=1 
        and    kcs.kit_cmpnt_nbr= kc.kit_cmpnt_nbr 
        and   kcs.prod_id=kc.prod_id 
        and   kcs.prod_actv_ts=kc.prod_actv_ts)
 and exists
       (select 1 from prod p
        where   p.prod_id= kc.prod_id 
        and   p.prod_actv_ts= kc.prod_actv_ts 
        and   p.prod_inactv_ts is null);

General principle is that you shouldn't have something in the FROM clause unless you are taking something from it. If you aren't taking anything from it, it is a filter and should be in the WHERE clause as a subquery.

Upvotes: 2

Avi
Avi

Reputation: 1155

use distinct to filter unique combination... eg.. select distinct(a,b,c) from table where some condition

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36922

Try using select * to find the reason for the duplicates.

Upvotes: 0

Related Questions