How do you use a sub query in a join?

Question

How do you use a sub query in a join?

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

dwilliscp (8/15/2012)
... we are guessing that all notifications fall under the highest delivery+item. Still this might be our best shot...

Of course, unless there's something in Notifications to identify which delivery/item a notification belongs to, which you haven't yet noticed. Are you sure there isn't a "link" table missing from the join?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 2

ChrisM@Work (8/16/2012)
dwilliscp (8/15/2012)
... we are guessing that all notifications fall under the highest delivery+item. Still this might be our best shot...
Of course, unless there's something in Notifications to identify which delivery/item a notification belongs to, which you haven't yet noticed. Are you sure there isn't a "link" table missing from the join?

The data is being extraced out from SAP.. but when they enter errors, about a shipment, they are not required to tie those errors to a delivery, much less a delivery + line... WTF, it is not how I would have set up the software... but that is a different department. I have noted why we are doing this, and passing it off to the stake holders (purchasing and procurement), they wanted to also track inter-company shipment errors.. so maybe they can get the ERP changed. I have Delivery and Delivery Line in the extract.. but line is never getting filled in and Delivery only about 5% of the time. Heck not even the sales order line is getting filled in.. so I had to use Material. Material gets filled in about 90% of the time.