Solved

oracle query help

Posted on 2013-01-18
13
342 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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 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 74

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exchange 2013 tmp files 3 41
how to find out the count of records based on the subfolders paths 11 32
SQL Syntax 24 45
pl/sql parameter is null sometimes 2 14
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

860 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