November 26, 2018 at 7:16 am
Hello,
I've written a query that is giving me the correct data, but now I'd like it to be sorted by
dbo.Salesman_Code.Salesman_Name
SELECT DISTINCT
CAST ((CASE WHEN dbo.Order_Detail.Part_Family_Code IS NOT NULL then LEFT(dbo.Order_Detail.Part_Family_Code,2) else 0 end) as decimal (10,2)) AS "Estimated",
ROUND(dbo.Quantity_Break_Detail.Profit_Percent, 2) AS "Budgeted",
ROUND(dbo.Order_Detail.Profit_Percent, 2) AS "Actual",
dbo.Order_Header.Order_Number AS "Order_Number",
dbo.Order_Detail.Work_Code AS "Work_Code",
dbo.Order_Detail.Job_Number AS "Job_Number",
dbo.Order_Detail.Customer_Part_Number AS "Customer_Part_Number",
dbo.Salesman_Code.Salesman_Name AS "Salesman"
FROM
((((dbo.Order_Detail LEFT OUTER JOIN dbo.Order_Header ON dbo.Order_Detail.Order_Header_ID = dbo.Order_Header.Order_Header_ID) LEFT OUTER JOIN dbo.Quantity_Break_Detail ON dbo.Order_Detail.Order_Detail_ID = dbo.Quantity_Break_Detail.Order_Detail_ID) LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders ON dbo.Order_Detail.Job_Number = dbo.vQV_SalesmanCode_Orders.Job_Number) LEFT OUTER JOIN dbo.Salesman_Code ON dbo.vQV_SalesmanCode_Orders.Salesman_Code_ID = dbo.Salesman_Code.Salesman_Code_ID)
WHERE
dbo.Quantity_Break_Detail.Profit_Percent <> 0 AND
dbo.Order_Detail.Profit_Percent <> 0 AND
dbo.Order_Detail.Work_Code LIKE '27%' AND
LEFT(dbo.Order_Detail.Part_Family_Code,2) <> 99
) AS a;
I've attempted to add the ORDER BY to the primary query and subquery and I continue to receive an error.
What would be the proper way to sort this data? Any help would be appreciated.
Thanks
November 26, 2018 at 7:24 am
lonnie.hull - Monday, November 26, 2018 7:16 AMHello,I've written a query that is giving me the correct data, but now I'd like it to be sorted by
dbo.Salesman_Code.Salesman_Name
SELECT DISTINCTCAST ((CASE WHEN dbo.Order_Detail.Part_Family_Code IS NOT NULL then LEFT(dbo.Order_Detail.Part_Family_Code,2) else 0 end) as decimal (10,2)) AS "Estimated",
ROUND(dbo.Quantity_Break_Detail.Profit_Percent, 2) AS "Budgeted",
ROUND(dbo.Order_Detail.Profit_Percent, 2) AS "Actual",
dbo.Order_Header.Order_Number AS "Order_Number",
dbo.Order_Detail.Work_Code AS "Work_Code",
dbo.Order_Detail.Job_Number AS "Job_Number",
dbo.Order_Detail.Customer_Part_Number AS "Customer_Part_Number",
dbo.Salesman_Code.Salesman_Name AS "Salesman"FROM
((((dbo.Order_Detail LEFT OUTER JOIN dbo.Order_Header ON dbo.Order_Detail.Order_Header_ID = dbo.Order_Header.Order_Header_ID) LEFT OUTER JOIN dbo.Quantity_Break_Detail ON dbo.Order_Detail.Order_Detail_ID = dbo.Quantity_Break_Detail.Order_Detail_ID) LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders ON dbo.Order_Detail.Job_Number = dbo.vQV_SalesmanCode_Orders.Job_Number) LEFT OUTER JOIN dbo.Salesman_Code ON dbo.vQV_SalesmanCode_Orders.Salesman_Code_ID = dbo.Salesman_Code.Salesman_Code_ID)
WHERE
dbo.Quantity_Break_Detail.Profit_Percent <> 0 AND
dbo.Order_Detail.Profit_Percent <> 0 AND
dbo.Order_Detail.Work_Code LIKE '27%' ANDLEFT(dbo.Order_Detail.Part_Family_Code,2) <> 99
) AS a;
I've attempted to add the ORDER BY to the primary query and subquery and I continue to receive an error.
What would be the proper way to sort this data? Any help would be appreciated.
Thanks
Encapsulate the query in a CTE and apply the order by when selecting from the CTE.
😎
November 26, 2018 at 7:42 am
Please post your code your code (and for the sake of your colleagues and future self, lay it out) in a format that's easy to read. It's difficult for others to care about it if you don't.
You might try something like this:SELECT DISTINCT
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) AS decimal (10,2)) AS Estimated,
ROUND(q.Profit_Percent, 2) AS Budgeted,
ROUND(o.Profit_Percent, 2) AS Actual,
h.Order_Number AS Order_Number,
o.Work_Code AS Work_Code,
o.Job_Number AS Job_Number,
o.Customer_Part_Number AS Customer_Part_Number,
s.Salesman_Name AS Salesman
FROM dbo.Order_Detail o
LEFT OUTER JOIN dbo.Order_Header h ON o.Order_Header_ID = h.Order_Header_ID
JOIN dbo.Quantity_Break_Detail q ON o.Order_Detail_ID = q.Order_Detail_ID -- the WHERE clause turns this into an inner join
LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders v ON o.Job_Number = v.Job_Number
LEFT OUTER JOIN dbo.Salesman_Code s ON v.Salesman_Code_ID = s.Salesman_Code_ID
WHERE q.Profit_Percent <> 0
AND o.Profit_Percent <> 0
AND o.Work_Code LIKE '27%'
AND o.Part_Family_Code NOT LIKE '99%'
ORDER BY s.Salesman_Name;
John
November 26, 2018 at 8:10 am
John,
I thought I had gotten it in a readable format. Thanks for showing me that it can be compacted even more.
I'm trying to determine exactly what changes you made to the query. By adding the where clause it eliminates the need for the subquery.
I appreciate your time and help / direction you could give me would be greatly appreciated.
And BTW, it appears to be working great.
November 26, 2018 at 9:02 am
It was that long list of table joins without a line break that spooked me!
Here are the changes I made:
(1) COALESCE instead of CASE makes the code simpler
(2) Table aliases instead of using the name of each table in full
(3) Remove unnecessary parentheses
(4) LIKE instead of LEFT is not only simpler, but it's sargable (meaning that any index on the column is more likely to be used)
(5) Any WHERE clause on the right-hand side of a left join turns the join into an inner join, so removed the LEFT OUTER from that join
(6) Removed unnecessary double quotes. If you avoid (as you did) using special characters and reserved keywords in column aliases then you don't need the double quotes
Pleased to hear it'w working for you now.
John
November 26, 2018 at 10:06 am
John,
Thanks again.
I've been trying to find a better way to look at the FROM statement. I've always had a problem trying to determine exactly which records are going to be pulled from what table.
If I read it correctly, this query is starting at the order_detail table, then linking through Joins to that table. It gets fuzzy when I have to link to a table three joins away.
Is there an easy / efficient / good way to layout and visualize how the joins will work? I'm using KnowledgeSync on an SQL database, but I have access to the SQL SMS, if that has a tool.
Thanks for your time and effort,
November 26, 2018 at 1:43 pm
Tables are not sorted, by definition. Queries return tables, by definition. Well, actually that’s not true. I could do a query to produce a non-table by not including a key. The ORDER BY clause is what’s called an implicit cursor; it converts a table into a cursor which can be ordered. Ordering by a subquery makes no sense. A sort has to be done on a complete set of complete rows.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 27, 2018 at 1:48 am
lonnie.hull - Monday, November 26, 2018 10:06 AMJohn,Thanks again.
I've been trying to find a better way to look at the FROM statement. I've always had a problem trying to determine exactly which records are going to be pulled from what table.
If I read it correctly, this query is starting at the order_detail table, then linking through Joins to that table. It gets fuzzy when I have to link to a table three joins away.
Is there an easy / efficient / good way to layout and visualize how the joins will work? I'm using KnowledgeSync on an SQL database, but I have access to the SQL SMS, if that has a tool.
Thanks for your time and effort,
Not sure exactly what you're looking for, but you might try the Database Diagram functionality in SSMS. Do your tables have foreign key constraints? If not, that feature may not work properly.
Ignore what Mr C said. He's jumped on the title of this topic and what you said in your original post, but doesn't appear to have read the rest of the thread. My solution doesn't even have a subquery.
John
Edit: fixed typo
November 27, 2018 at 10:39 am
Thanks John, I'll take a look at it.
Thanks again for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply