April 19, 2017 at 10:27 am
I have several tables that I am working with trying to get some data:
SALES_ORDERS S - header data
SALES_ORDER_LINES SL - Order#, Order_line#, Part_Code, Sales_Price
SALES_ORDER_LINE_QTYS SLQ - Order#, Order_line#, Order_Quantity, Unit_of_measure
PRODUCT_MASTER PM - Part_code, descriptions, item specific data
Obviously there are more fields for each table.......
When a PM.PART_CODE is a 'catchweight' item 'C' then there are 2 entries for each SLQ.ORDER_LINE#. I only want to view the order_quantity for the UOM = 'LBS'.
Below is my query....any help is greatly appreciated.....sorry I don't post often so if this belongs somewhere else please let me know.....thanks
SELECT
SO.ORDER_DATE, SOL.SYS_DELIVERY_DATE, SOL.SYS_SHIPPING_DATE, SO.ORDER_NUMBER, SOL.ORDER_LINE_NUMBER, SOL.PART_CODE
, CASE
WHEN PM.IC_PRODUCT_UOM_CONTROL = 'C'
THEN (SELECT TOP 1 SLQ.ORDER_QUANTITY
FROM SALES_ORDER_LINE_QTYS SLQ
WHERE SLQ.ORDER_NUMBER = SO.ORDER_NUMBER AND SLQ.ORDER_LINE_NUMBER = SOL.ORDER_LINE_NUMBER AND SOL.PART_CODE = PM.PART_CODE AND SLQ.UNIT_OF_MEASURE = 'LBS')
ELSE
(SELECT TOP 1 SLQ.ORDER_QUANTITY
FROM SALES_ORDER_LINE_QTYS SLQ
WHERE SLQ.ORDER_NUMBER = SO.ORDER_NUMBER AND SLQ.ORDER_LINE_NUMBER = SOL.ORDER_LINE_NUMBER AND SOL.PART_CODE = PM.PART_CODE AND SLQ.UNIT_OF_MEASURE <> 'LBS')
END
AS 'ORDER_QUANTITY'
, SLQ.UNIT_OF_MEASURE
FROM SALES_ORDERS SO
JOIN SALES_ORDER_LINES SOL ON SOL.ORDER_NUMBER = SO.ORDER_NUMBER
JOIN SALES_ORDER_LINE_QTYS SLQ ON SO.ORDER_NUMBER = SLQ.ORDER_NUMBER AND SLQ.ORDER_LINE_NUMBER = SOL.ORDER_LINE_NUMBER
JOIN PRODUCT_MASTER PM ON PM.PART_CODE = SOL.PART_CODE
WHERE SO.ORDER_NUMBER = 117171
The Results:
2017-04-10 00:00:00.000 2017-04-24 00:00:00.000 2017-04-21 00:00:00.000 117171 3 831 1260.000000 CS5
2017-04-10 00:00:00.000 2017-04-24 00:00:00.000 2017-04-21 00:00:00.000 117171 3 831 1260.000000 LBS
2017-04-10 00:00:00.000 2017-04-24 00:00:00.000 2017-04-21 00:00:00.000 117171 1 632 1470.000000 CS6
2017-04-10 00:00:00.000 2017-04-24 00:00:00.000 2017-04-21 00:00:00.000 117171 1 632 1470.000000 LBS
2017-04-10 00:00:00.000 2017-04-24 00:00:00.000 2017-04-21 00:00:00.000 117171 2 832 1260.000000 CS5
2017-04-10 00:00:00.000 2017-04-24 00:00:00.000 2017-04-21 00:00:00.000 117171 2 832 1260.000000 LBS
April 19, 2017 at 2:32 pm
I'm not sure if this is right, but I've added a condition to the JOIN to the order line table. I also cleaned up the formatting and used IFCODE tags to get it into the window below:SELECT SO.ORDER_DATE, SOL.SYS_DELIVERY_DATE, SOL.SYS_SHIPPING_DATE, SO.ORDER_NUMBER, SOL.ORDER_LINE_NUMBER, SOL.PART_CODE,
CASE
WHEN PM.IC_PRODUCT_UOM_CONTROL = 'C' THEN (
SELECT TOP 1 SLQ.ORDER_QUANTITY
FROM SALES_ORDER_LINE_QTYS AS SLQ
WHERE SLQ.ORDER_NUMBER = SO.ORDER_NUMBER
AND SLQ.ORDER_LINE_NUMBER = SOL.ORDER_LINE_NUMBER
AND SOL.PART_CODE = PM.PART_CODE
AND SLQ.UNIT_OF_MEASURE = 'LBS'
)
ELSE (
SELECT TOP 1 SLQ.ORDER_QUANTITY
FROM SALES_ORDER_LINE_QTYS AS SLQ
WHERE SLQ.ORDER_NUMBER = SO.ORDER_NUMBER
AND SLQ.ORDER_LINE_NUMBER = SOL.ORDER_LINE_NUMBER
AND SOL.PART_CODE = PM.PART_CODE
AND SLQ.UNIT_OF_MEASURE <> 'LBS'
)
END AS ORDER_QUANTITY,
SLQ.UNIT_OF_MEASURE
FROM SALES_ORDERS AS SO
INNER JOIN SALES_ORDER_LINES AS SOL
ON SOL.ORDER_NUMBER = SO.ORDER_NUMBER
AND SOL.UOM = 'LBS'
INNER JOIN SALES_ORDER_LINE_QTYS AS SLQ
ON SO.ORDER_NUMBER = SLQ.ORDER_NUMBER
AND SLQ.ORDER_LINE_NUMBER = SOL.ORDER_LINE_NUMBER
INNER JOIN PRODUCT_MASTER AS PM
ON PM.PART_CODE = SOL.PART_CODE
WHERE SO.ORDER_NUMBER = 117171;
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
April 19, 2017 at 2:35 pm
without some CREATE TABLE statements and some consumable sample data, there's no way for anyone to really help you. Please read this article and follow the instructions so we can help you. Once you have read the article, post the data as Jeff shows, and someone can help, I'm sure.
April 20, 2017 at 7:14 am
Sorry for the bad post, reading article now to get understanding of how to properly post.
April 20, 2017 at 7:17 am
shanegair - Thursday, April 20, 2017 7:14 AMSorry for the bad post, reading article now to get understanding of how to properly post.
another site that may help you https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 20, 2017 at 7:30 am
shanegair - Thursday, April 20, 2017 7:14 AMSorry for the bad post, reading article now to get understanding of how to properly post.
I assure you, it's worth the effort. If you post some code which people can execute for themselves, along with sample data and expected results based on the sample data, they are much more likely to help and provide a working solution for you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 20, 2017 at 7:36 am
Something like this?SELECT
SO.ORDER_DATE,
SOL.SYS_DELIVERY_DATE, SOL.SYS_SHIPPING_DATE,
SO.ORDER_NUMBER, SOL.ORDER_LINE_NUMBER,
SOL.PART_CODE,
SLQ.ORDER_QUANTITY,
SLQ.UNIT_OF_MEASURE
FROM SALES_ORDERS SO
INNER JOIN SALES_ORDER_LINES SOL
ON SOL.ORDER_NUMBER = SO.ORDER_NUMBER
INNER JOIN PRODUCT_MASTER PM
ON PM.PART_CODE = SOL.PART_CODE
INNER JOIN SALES_ORDER_LINE_QTYS SLQ
ON SLQ.ORDER_NUMBER = SOL.ORDER_NUMBER
AND SLQ.ORDER_LINE_NUMBER = SOL.ORDER_LINE_NUMBER
AND (
PM.IC_PRODUCT_UOM_CONTROL <> 'C'
OR
(PM.IC_PRODUCT_UOM_CONTROL = 'C' AND SLQ.UNIT_OF_MEASURE = 'LBS')
)
WHERE SO.ORDER_NUMBER = 117171
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
April 20, 2017 at 7:50 am
ChrisM@Work - Thursday, April 20, 2017 7:36 AMSomething like this?
SELECT
SO.ORDER_DATE,
SOL.SYS_DELIVERY_DATE, SOL.SYS_SHIPPING_DATE,
SO.ORDER_NUMBER, SOL.ORDER_LINE_NUMBER,
SOL.PART_CODE,
SLQ.ORDER_QUANTITY,
SLQ.UNIT_OF_MEASUREFROM SALES_ORDERS SO
INNER JOIN SALES_ORDER_LINES SOL
ON SOL.ORDER_NUMBER = SO.ORDER_NUMBERINNER JOIN PRODUCT_MASTER PM
ON PM.PART_CODE = SOL.PART_CODEINNER JOIN SALES_ORDER_LINE_QTYS SLQ
ON SLQ.ORDER_NUMBER = SOL.ORDER_NUMBER
AND SLQ.ORDER_LINE_NUMBER = SOL.ORDER_LINE_NUMBER
AND (
PM.IC_PRODUCT_UOM_CONTROL <> 'C'
OR
(PM.IC_PRODUCT_UOM_CONTROL = 'C' AND SLQ.UNIT_OF_MEASURE = 'LBS')
)WHERE SO.ORDER_NUMBER = 117171
That did it.....Thank you all for the help, next post will be a better one.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply