Home » SQL & PL/SQL » SQL & PL/SQL » Query improvement (avoiding code duplication, etc)
Query improvement (avoiding code duplication, etc) [message #350288] Wed, 24 September 2008 10:31 Go to next message
aadwight
Messages: 9
Registered: September 2008
Junior Member
Hi.

With some helpful advice from these forums, I wrote a SQL query which appears to do what I want it to do. However, it's pretty bulky and difficult to maintain. It also duplicates the same code in two places, which I'd prefer to avoid. I'd appreciate any suggestions for how to do improve upon what I currently have.

I've written up some context for what I need the query to return. And, before you ask, I cannot change the table structure. The actual query is below, if you just want to skip to it.
====
Let's say I have an 'order' table containing some orders, and a 'special_orders' table which contains additional information for special orders. If an order is a special order, then exactly one entry exists in the special_orders table with the corresponding order_id; otherwise, no special_orders entry exists. This is the only way to tell if an order is a special order.

If more than one unfilled order is to be shipped to the same customer at the same address, I want to see it so that I can combine the two orders to save on shipping costs. However, 'fragile' orders can never be combined, and should therefore not be returned by this search.

In other words, if a record meets the following criteria, it should be returned:

1) The order is not a 'fragile' order. (An order is 'fragile' only if a special_order with a 'handling' column value of 'fragile' exists for that order.)
2) The order has not yet shipped (shipped_date is null).
3) Another record exists which meets 1) and 2) and also shares the same customer_id and shipping_address as this order.

SELECT A.customer, A.address, A.order_number
FROM (SELECT orders.*, special_orders.handling
          FROM orders LEFT JOIN special_orders.handling
          ON orders.order_id=special_orders.order_id
          WHERE NOT special_orders.handling='fragile'
          AND orders.shipped_date IS NULL) A, 
     (SELECT orders.*, special_orders.handling
          FROM orders LEFT JOIN special_orders.handling
          ON orders.order_id=special_orders.order_id
          WHERE NOT special_orders.handling='fragile'
          AND orders.shipped_date IS NULL) B 
WHERE A.shipping_address = B.shipping_address 
AND A.customer_id = B.customer_id 
AND A.main_id <> B.main_id
ORDER BY A.customer, A.address


So, how can I make this better?
Re: Query improvement (avoiding code duplication, etc) [message #350328 is a reply to message #350288] Wed, 24 September 2008 14:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Read up on the WITH clause

Kevin Meade's Blog

The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code)

Good luck, Kevin
Re: Query improvement (avoiding code duplication, etc) [message #350342 is a reply to message #350328] Wed, 24 September 2008 15:27 Go to previous message
aadwight
Messages: 9
Registered: September 2008
Junior Member
That looks like exactly what I need to do. Thanks.

Incorporating a WITH clause, the query looks like this. Can any further improvements be made toward efficiency, maintainability, or readability?

WITH (SELECT orders.*, special_orders.handling
          FROM orders LEFT JOIN special_orders.handling
          ON orders.order_id=special_orders.order_id
          WHERE NOT special_orders.handling='fragile'
          AND orders.shipped_date IS NULL) AS combinable_orders
SELECT A.customer, A.address, A.order_number
FROM combinable_orders A, combinable_orders B 
WHERE A.shipping_address = B.shipping_address 
AND A.customer_id = B.customer_id 
AND A.main_id <> B.main_id
ORDER BY A.customer, A.address
Previous Topic: Using internal procedures and spooling output
Next Topic: question regarding grants
Goto Forum:
  


Current Time: Sun Dec 11 00:08:00 CST 2016

Total time taken to generate the page: 0.07723 seconds