SubQuery too complex

[Toshilap]

Please, help me to understand what person tried to see when s/he wrote subquery:
(SELECT TOP 1 ''X''
FROM ORDER_DETAIL OD (NOLOCK)

WHERE

OD.MASTER_CUSTOMER_ID = ORDER_DETAIL.MASTER_CUSTOMER_ID
AND OD.PRODUCT_ID = ORDER_DETAIL.PRODUCT_ID
AND DATEDIFF(DAY,OD.ORDER_DATE, ORDER_DETAIL.ORDER_DATE) BETWEEN 1 AND 180)

I can't seem to understand WHY would you create this OD alias and still have ORDER_DETAIL name and join within table and all of it is part of SP.

Is there known situations when you have to go this way?

Please, sort it out for me if you would,
THANKS


I finally got it all together and forgot where I put it.

 

[hneal98]

It looks like whomever created this query is comparing one instance to another as if there were two tables,however the way it is written is more like you would find in Access.

It could have been written like this as well:

(SELECT TOP 1 ''X'' 
FROM 
	ORDER_DETAIL OD (NOLOCK) 
	INNER JION ORDER_DETAIL OD2 (NOLOCK) ON OD.MASTER_CUSTOMER_ID = OD2.MASTER_CUSTOMER_ID
										AND OD.PRODUCT_ID = OD2.PRODUCT_ID
										AND DATEDIFF(DAY,OD.ORDER_DATE, OD2.ORDER_DATE) BETWEEN 1 AND 180)

 

[Toshilap]

What is the reason? What to compare within table?
I do get your way - this is how I do it but that first version threw me off...espesially if all I am getting at execution is 'unable to parse'

Plus what output expected? Can you tell?

I finally got it all together and forgot where I put it.

 

[hneal98]

Becuase there is no other way to compare one record in a table to another record in the same table. In other words this data base is normalized and they want to find all orders that are between 1 and 180 days old.

Personally I am not sure why they want to compare one order date to another in this instance, but you would normally use it to compare two dates to get a time frame. I supposed what they are looking at there is all those who placed an order within 180 days.

Let me know if that is not clear.

 

[Toshilap]

Sorry, all I am getting on execution is X...

I finally got it all together and forgot where I put it.

 

[hneal98]

It looks like this code goes with some application. I would think the X is used to mark records that represent orders being placed within 180 days of each other. The X would be used on a form or in a report along with someother data. You would need to go back to whomever wrote this or to whomever is using it to see what is going on.

 

[druer]

My only guess looking at the code (although I still have no idea how it could possibly execute as written) is that perhaps they wrote it as a sort of "if exists". Assuming that it is running in some environment that you don't have to include the order_detail in the from clause, perhaps they call the stored procedure simply to see if there are any orders that need processed in some way. Since they are calling TOP 1, it appears to me that they only wanted to know if any exist. And once they found 1 they simply return the X. If none exist the X will not be returned and there will be no results at all. Certainly much easier ways to do that if that was the goal.

 

[vongrunt]

Looks that way - especially because TOP has no ORDER BY.

Why is ORDER_DETAIL basically used twice... both instances of that table are joined on MASTER_CUSTOMER_ID and PRODUCT_ID. So my guess is this subquery translated to English would be "show X if customer ordered the same product within last six months".

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)