?
Solved

oracle query help

Posted on 2013-01-18
13
Medium Priority
?
348 Views
Last Modified: 2013-02-11
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
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
13 Comments
 
LVL 23

Expert Comment

by:paquicuba
ID: 38794744
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 38794764
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
 
LVL 21

Expert Comment

by:Amitkumar Panchal
ID: 38799999
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 6

Author Comment

by:anumoses
ID: 38805375
No rows
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38816058
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38816101
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 38816252
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 38816355
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
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 1000 total points
ID: 38816416
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 38875997
thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38876011
isn't a split in order?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question