Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

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
0
anumoses
Asked:
anumoses
  • 3
  • 2
  • 2
  • +3
1 Solution
 
paquicubaCommented:
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
0
 
paquicubaCommented:
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.
0
 
Amitkumar PSr. ConsultantCommented:
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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
anumosesAuthor Commented:
No rows
0
 
sdstuberCommented:
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.
0
 
sdstuberCommented:
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
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
Devinder Singh VirdiCommented:
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
0
 
Mark GeerlingsDatabase AdministratorCommented:
This will still prevent those two records from being returned:
"and c.reject_reason_code = 'AAPD' and vp.product_type = 'RBC'"

(And there is no performance improvement with this suggestion either.)

The only way to allow them to be returned is like this:
"and (c.reject_reason_code = 'AAPD' or vp.product_type = 'RBC')"

Note: the parentheses are *VERY* important here when an "or" condition is added in the "where" clause.
0
 
anumosesAuthor Commented:
thanks
0
 
sdstuberCommented:
isn't a split in order?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now