Solved

oracle query help

Posted on 2013-01-18
13
336 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
  • 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
 
LVL 6

Author Comment

by:anumoses
ID: 38805375
No rows
0
 
LVL 73

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 73

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 250 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 73

Expert Comment

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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
syadmin MSSQL 2 58
Optimizing a query 3 34
Update from TABLE-A to TABLE-B 5 39
Exchange 2013 free and busy between mailbox databases 77 46
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now