anumoses
asked on
oracle query help
select c.unit_id,c.coll_date,c.la bel_dats,
c.product_code,vp.inv_prod uct_type,
vp.product_type,product_fo rmula,c.re ject_reaso n_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
-------------------------- ---------s ee 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.la bel_dats,
c.product_code,vp.inv_prod uct_type,
	 vp.product_type,product_fo rmula,c.re ject_reaso n_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
c.product_code,vp.inv_prod
vp.product_type,product_fo
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
and c.unit_id ='W039712644096'
order by c.coll_date,c.unit_id
--------------------------
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.la
c.product_code,vp.inv_prod
	 vp.product_type,product_fo
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
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
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.
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.
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.
ASKER
No rows
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.
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.la bel_dats,
c.product_code,vp.inv_prod uct_type,
vp.product_type,product_fo rmula,c.re ject_reaso n_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
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.la
c.product_code,vp.inv_prod
vp.product_type,product_fo
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
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.
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
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-
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.la bel_dats,
c.product_code,vp.inv_prod uct_type,
vp.product_type,product_fo rmula,c.re ject_reaso n_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
select * from
(
select c.unit_id,c.coll_date,c.la
c.product_code,vp.inv_prod
vp.product_type,product_fo
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
isn't a split in order?
select c.unit_id,c.coll_date,c.la
c.product_code,vp.inv_prod
vp.product_type,product_fo
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
and c.unit_id ='W039712644096'
and trim(c.reject_reason_code)
order by c.coll_date,c.unit_id