jcelko212 32090 wrote:
Want to follow the rules and try again?
Thank you for using a giant sized hand to slap this new fellow down.
I have sadly run into the issue where I don't actually know the question to ask to get the answer I need or to be able to formulate a proper search offered on a forum. It is also the reason I despise blanket statements such as 'ask Google'. You're looking on a forum for answers or at least a pointer in the right direction. I am quite happy to look myself when I know what question to ask, but alas, I get stuck every now and then and I had a thought that a forum such as this one might be the best place to start asking questions.
I have inherited a project which I want to get my hands dirty on. I don't want anyone to do the job for me, which is why when I asked my original question, I stated I wanted to understand what was going on. Willingness to learn and all that.
I have worked a little bit with MS Access. That is all. My terminology is limited because I inherited that workload too, so have bit by bit learned a little more to finish the task. I can't even comment on whether or not that task was completed to any particular standard or to a specification.
The program the office works with is Sage 200c On-Premise. The data is sat on MS Server 2016 hosting SQL Server 2016.
As for presuming that the professionals here will probably know the error message I am receiving, you're right I shouldn't presume.
The error is as follows:
Column 'Sage200_mydb.dbo.ScansVw.Priority' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Now, I have done a little digging into this, but from a very limited understanding, you can either make the other columns a max, min, etc or add the column name into the group by clause. Another post suggested that this was the wrong way to do this. Again, a lack of understanding on my part and wanting to understand how to use this Max and Group By properly means I wanted to get the best help and pointers possible so I don't spend another 24 hours scratching my head.
The code I posted in my first message was a copy/paste straight from SQL Server showing me the top 1000 rows for the view I mentioned in my earlier post. If it was wrong for SQL Server to provide me with the query in that format with the commas on each new line, then I will adjust it around next time I post code.
I fully apologize for my lack of awareness on the Netiquette of SQL Forums, that has been around for 30 years. This was my first ever post in this forum, actually in any forum on SQL. I will read up on what you are expecting from me in future.
On that note, could you tell me please what DDL is and where I might find the ANSI/ISO specification for asking for help?
The current View (copied from the 'design') is as follows:
SELECT TOP (100) PERCENT dbo.SOPOrderReturn.Priority, dbo.SOPOrderReturn.PromisedDeliveryDate, dbo.SOPOrderReturn.DocumentNo, dbo.SLCustomerAccount.CustomerAccountNumber,
dbo.SOPOrderReturn.CustomerDocumentNo, dbo.SOPOrderReturn.RequestedDeliveryDate, dbo.SOPOrderReturn.AnalysisCode3, dbo.SOPOrderReturn.InvoiceCreditStatusID,
dbo.SLCustomerAccount.AccountIsOnHold, dbo.SOPOrderReturn.CancelledStatusID, dbo.SOPOrderReturn.DocumentStatusID, dbo.SLCustomerAccount.CustomerAccountName,
dbo.SOPOrderReturn.DocumentTypeID, dbo.SOPOrderReturn.DespatchReceiptStatusID, dbo.Scans.Dept, dbo.Scans.ScanDate
FROM dbo.SOPOrderReturn INNER JOIN
dbo.SLCustomerAccount ON dbo.SOPOrderReturn.CustomerID = dbo.SLCustomerAccount.SLCustomerAccountID FULL OUTER JOIN
dbo.Scans ON dbo.SOPOrderReturn.DocumentNo = dbo.Scans.JobNo
WHERE (dbo.SOPOrderReturn.InvoiceCreditStatusID = 0) AND (NOT (dbo.SOPOrderReturn.DocumentStatusID = '2')) AND (dbo.SOPOrderReturn.DocumentTypeID = '0' OR
dbo.SOPOrderReturn.DocumentTypeID = '2') AND (NOT (dbo.SOPOrderReturn.DespatchReceiptStatusID = 2))
ORDER BY dbo.SOPOrderReturn.Priority DESC, dbo.SOPOrderReturn.PromisedDeliveryDate
Sample output of the query is as follows: (edited as this holds actual customer information)
Priority PromisedDeliveryDate DocumentNo CustomerAccountNumber CustomerDocumentNo RequestedDeliveryDate AnalysisCode3 InvoiceCreditStatusID AccountIsOnHold CancelledStatusID DocumentStatusID CustomerAccountName DocumentTypeID DespatchReceiptStatusID Dept ScanDate
2020-03-10 00:00:00.000 0000115717 ACC003 0000000001 2020-03-10 00:00:00.000 0 0 0 0 Cust 1 0 0 Office 2019-08-24 22:51:00.0000000
2020-03-10 00:00:00.000 0000115717 ACC003 0000000001 2020-03-10 00:00:00.000 0 0 0 0 Cust 1 0 0 Office 2019-08-24 22:52:00.0000000
2020-03-10 00:00:00.000 0000115717 ACC003 0000000001 2020-03-10 00:00:00.000 0 0 0 0 Cust 1 0 0 CAD 2019-08-25 00:13:44.0000000
2020-03-03 00:00:00.000 0000115847 ACC003 0000000002 2020-03-03 00:00:00.000 0 0 0 0 Cust 1 0 0 CAD 2019-08-25 10:44:14.0000000
2020-03-03 00:00:00.000 0000115847 ACC003 0000000002 2020-03-03 00:00:00.000 0 0 0 0 Cust 1 0 0 Laser 2019-08-25 13:09:47.0000000
I hope I have provided enough information now to get me started?
in the results section above, I have two unique numbers (which are actually stored as text in the database, I don't know why Sage chose to go this route) under DocumentNo, this is the main column for the whole thing I am working on. I am wanting to show single DocumentNo that refers to the latest ScanDate. This is why my initial thought was to go straight to Max([ScanDate]) and then Group By [DocumentNo]. In my mind it made sense, but of course I am missing something. If I add [Dept] into the Group By, I get all the columns anyway so was unsure how to proceed further. If I leave it out, I get the error mentioned above.