• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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.
0
anumoses
Asked:
anumoses
  • 2
  • 2
3 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now