Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

oracle query help

select c.unit_id,c.coll_date,c.label_dats,
       c.product_code,vp.inv_product_type,
    vp.product_type,product_formula,c.reject_reason_code
  from components@plab.world c,
       valid_products@plab.world vp
 where trunc(c.label_dats) between '01-jan-2012'
                               and '31-dec-2012'
  and substr(vp.product_code,1,5) = substr(c.product_code,1,5)
  and c.unit_id ='W039712644096'
order by c.coll_date,c.unit_id

-----------------------------------see attached for results

In the query results I have 2 records of the unit.  But the requirement is  to give units
where vp.product_formula != '@04' and c.reject_reason_code = 'AAPD' and vp.product_type = 'RBC'

If I use this query I dont get any results

select c.unit_id,c.coll_date,c.label_dats,
       c.product_code,vp.inv_product_type,
	   vp.product_type,product_formula,c.reject_reason_code
  from components@plab.world c,
       valid_products@plab.world vp
 where trunc(c.label_dats) between '01-jan-2012'
                               and '31-dec-2012'
  and vp.product_type = 'RBC'
  and vp.product_formula != '@04'
  and substr(vp.product_code,1,5) = substr(c.product_code,1,5)
  and c.unit_id ='W039712644096'
  and  c.reject_reason_code = 'AAPD'
order by c.coll_date,c.unit_id

Is there a way to get where product_type = 'RBC' with the reject reason code = 'AAPD'
and vp.product_formula != '@04'  from the 2 records?
AAPD-rejected.xls
Avatar of paquicuba
paquicuba
Flag of United States of America image

You may have some spaces, trim them:


select c.unit_id,c.coll_date,c.label_dats,
       c.product_code,vp.inv_product_type,
         vp.product_type,product_formula,c.reject_reason_code
  from components@plab.world c,
       valid_products@plab.world vp
 where trunc(c.label_dats) between '01-jan-2012'
                               and '31-dec-2012'
  and trim(vp.product_type) = 'RBC'
  and vp.product_formula != '@04'
  and substr(vp.product_code,1,5) = substr(c.product_code,1,5)
  and c.unit_id ='W039712644096'
  and  trim(c.reject_reason_code) = 'AAPD'
order by c.coll_date,c.unit_id
If trimming doesn't work, one thing you can do is to introduce the new filters one at a time to identify which one is not finding a matching value.
Avatar of Am P
Your query is not returning any rows where product_type = 'RBC' with the reject reason code = 'AAPD'..

Have checked the attached sheet..
1 row... the product type is RPFF where reason code is AAPD..
2 row... the reason code is empty..

So, the filter you applied seems correct.. it the problem with data..

Try running the first query by adding a filter one by one..(ie run by adding prod code filter, then add prod type filter and finally add reason code filter).. you will see the difference.
Avatar of anumoses

ASKER

No rows
Avatar of Sean Stuber
Sean Stuber

please post the expected results - the actual data you want to see, not just a description of it

as noted above,  from your description of what you want, no rows seems correct.
just curious,  when you say  you want


 vp.product_formula != '@04'
AND
c.reject_reason_code = 'AAPD'
AND
 vp.product_type = 'RBC'

are you trying to say you want all 3 of those conditions applied to each row you return? If so, then no rows is correct because no row satisfies all 3 conditions.

or

are you trying to say you want all rows that match ANY of those conditions? If so, then you don't want AND, you want OR


select c.unit_id,c.coll_date,c.label_dats,
       c.product_code,vp.inv_product_type,
    vp.product_type,product_formula,c.reject_reason_code
  from components@plab.world c,
       valid_products@plab.world vp
 where trunc(c.label_dats) between '01-jan-2012'
                               and '31-dec-2012'
  and substr(vp.product_code,1,5) = substr(c.product_code,1,5)
  and c.unit_id ='W039712644096'
and (
     vp.product_formula != '@04'
     or
    c.reject_reason_code = 'AAPD'
     or
    vp.product_type = 'RBC'
)
order by c.coll_date,c.unit_id


if this isn't what you intend, then as requested above, please show what results you are expecting
No, because your sample data does not meet the criteria in the "where" clauses.  These two conditions are not true for the same record(s):
  and vp.product_type = 'RBC'
  and  c.reject_reason_code = 'AAPD'

So, that causes this query to not return any records.  To allow those two rows to be returned, you would need to change those two lines to this:

  and (vp.product_type = 'RBC'
   or  c.reject_reason_code = 'AAPD')

Also, you didn't ask about performance, but these two lines in your query could add a *HUGE* performance penalty, especially if either (or both) of these columns are indexed:
   trunc(c.label_dats) between '01-jan-2012' and '31-dec-2012'
  and substr(vp.product_code,1,5) = substr(c.product_code,1,5)

Try to avoid using operators like: trunc, upper, lower, to_date, to_char, trim, nvl, substr, instr, etc. on database columns in your "where" clauses.  You may use these on the literal values or bind variables in "where" clauses with almost no performance penalty, but on database columns, these operators can make queries takes 1000s (or more) times longer to execute.

The first "trunc" can be avoided by writing that line like this instead:
c.label_dats between to_date('01-jan-2012','DD-MON-YYYY') and to_date('31-dec-2012 235959,'DD-MON-YYYY HH24MISS')'.

To avoid a performance penalty for the two "substr" operators on "product_code", you may need to add function-based indexes on those tables based on the same operator that your query uses for those two columns.
What about of using sub query as below:

select * from
(
select c.unit_id,c.coll_date,c.label_dats,
       c.product_code,vp.inv_product_type,
    vp.product_type,product_formula,c.reject_reason_code
  from components@plab.world c,
       valid_products@plab.world vp
 where trunc(c.label_dats) between '01-jan-2012'
                               and '31-dec-2012'
  and substr(vp.product_code,1,5) = substr(c.product_code,1,5)
  and c.unit_id ='W039712644096'
)
where vp.product_formula != '@04' and c.reject_reason_code = 'AAPD' and vp.product_type = 'RBC'
order by c.coll_date,c.unit_id
ASKER CERTIFIED SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks
isn't a split in order?