oracle query

Original Code
select v.inv_product_type, 
           count(v.inv_product_type)
from shipped_products@plab.world s,
	 components@plab.world c,
	 valid_products@plab.world v
where (to_char(s.insert_date, 'DD-MON-RRRR') between :start_date and :end_date) 
     and s.blood_type not in ('39','08','09','19','20','30','31','53','54','64','65','75','76','86','87','97','98','40','58','Mq','Mb') and
      s.shipped_to in ('STE-CS0001', 'PFI-CS0001', 'NOR-CS0008', 'DAN-CS0001') and
      c.unit_id = s.unit_id and
      substr(c.product_code, 1,5) = substr(s.product_code, 1,5) and
      v.product_code = substr(s.product_code,1,5) and
    ((s.exp_date >= s.insert_date and
     (s.reject_reason = 'EXPR' or s.reject_reason = 'MFR')) or
     (s.exp_date < s.insert_date and
      (s.reject_reason = 'EXPR' or s.reject_reason = 'MFR')) or
     (s.exp_date < s.insert_date and
      s.reject_reason is null and
     (c.reject_reason_code = 'EXPR' or c.reject_reason_code = 'MFR')) or
     (s.exp_date < s.insert_date and
      s.reject_reason is null and
      c.reject_reason_code is null and
      s.unit_id not in (select ci.unit_id
                        from component_interdictions@plab.world ci
                        where ci.unit_id = s.unit_id and
                              ci.product_code = s.product_code) and
      s.unit_id not in (select ui.unit_id
                        from unit_interdictions@plab.world ui
                        where ui.unit_id = s.unit_id)))
group by v.inv_product_type

Open in new window


instead of the insert_date I need to replace with exp_date and user does not want to look at the insert_date at all.
Also we need to add 15 days to the end_date parameter.

So the modified query becomes
select v.inv_product_type, 
           count(v.inv_product_type)
from shipped_products@plab.world s,
	 components@plab.world c,
	 valid_products@plab.world v
where (to_char(s.exp_date, 'DD-MON-RRRR') between :start_date and :end_date+15) 
     and s.blood_type not in ('39','08','09','19','20','30','31','53','54','64','65','75','76','86','87','97','98','40','58','Mq','Mb') and
      s.shipped_to in ('STE-CS0001', 'PFI-CS0001', 'NOR-CS0008', 'DAN-CS0001') and
      c.unit_id = s.unit_id and
      substr(c.product_code, 1,5) = substr(s.product_code, 1,5) and
      v.product_code = substr(s.product_code,1,5) and
    (s.reject_reason = 'EXPR' or s.reject_reason = 'MFR' or
     s.reject_reason = 'EXPR' or s.reject_reason = 'MFR' or
     s.reject_reason is null and
     c.reject_reason_code = 'EXPR' or c.reject_reason_code = 'MFR' or
     (s.reject_reason is null and
      c.reject_reason_code is null and
      s.unit_id not in (select ci.unit_id
                        from component_interdictions@plab.world ci
                        where ci.unit_id = s.unit_id and
                              ci.product_code = s.product_code) and
      s.unit_id not in (select ui.unit_id
                        from unit_interdictions@plab.world ui
                        where ui.unit_id = s.unit_id)))
group by v.inv_product_type

Open in new window


I just wanted to make sure if I am doing as per required since some count of product types are not correct.
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>(to_char(s.exp_date, 'DD-MON-RRRR') between :start_date and :end_date+15)


Why are you converting s.exp_date to a string first?

I'm assuming start_date and end_date are actual dates and not strings?

If they are strings, that is likely the issue.

As we have suggested in many of your previous questions:
When dealing with dates, keep them dates and not strings.

If the dates have a TIME portion, BETWEEN can be bad.

I would suggest:
(s.exp_date >= :start_date and s.exp_date < :end_date+16)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
Thanks. I will follow this.
0
Mark GeerlingsDatabase AdministratorCommented:
I agree that if the insert_date and exp_date columns are actual "date" datatypes, you should *NOT* convert them to character strings in your queries!

Also, if you are able to create a local view in the plab.world database that joins these three table there, you should change this query to select from that view instead.  Depending on the numbers of records in these tables, that approach can be thousands (or millions) of times faster than joining these three remote tables as you are doing now.
0
slightwv (䄆 Netminder) Commented:
I believe you are still using 9i.

To add to Mark's post:  You might try the DRIVING_SITE hint in place of creating views on a remote database:
http://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#5699
0
anumosesAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.