I'm not sure if this is possible, I couldn't find any information on it. It's possible I overlooked it if it is posted somewhere on the forum or in the manual. This could just be some Python scripting that I just don't know. I am not well versed in Python or SQL.
I have a Named Query:
EX:
SELECT Production_Report_Data.Time_Stamp,
Production_Report_Data.[Mayflower Total Parts Ran],
Production_Report_Data.[Mayflower Downtime (Minutes)],
Production_Report_Data.[Final Dial Total Parts Ran],
Production_Report_Data.[Final Dial Downtime (Minutes)],
Production_Report_Data.[Frame Dial Total Parts Ran],
Production_Report_Data.[Frame Dial Downtime (Hours)],
Production_Report_Data.[Shaft Machine Total Parts Ran],
Production_Report_Data.[Rivet Machine Total Parts Ran]
FROM Production_Report_Data
Order by Production_Report_Data.Time_Stamp DESC;
In this Query I have it ordered by the Time Stamp in descending order. This works fine, it's much nicer than scrolling to the bottom of my power table to see the latest data.
After making this change, when logging data, it's easier to have it in the default or ascending order when exporting it to an Excel file. My code for this is:
sDate = system.date.now() #pulls today's date/time
mydate = system.date.format (sDate,'MM_dd_yyyy') #formats today's date
Title = "Production Report Data "
Data = system.db.runNamedQuery("Production Report Query") # Gets SQL Database Data
system.dataset.exportExcel(Title + mydate, 1, Data) #creates xl sheet with todays date
My question is, can I change my Python script to change the Named Query from Acend to Decend? Or do I need to build a seperate named query? OR is there a better way to default my Power Table so it's set-up for ascend without changing it in my named query? This was the way I found which works, but causes me issues on the other end.
The sort order as part of the query is not something that can be changed by parameterization (outside the 'query string' parameter type, which has drawbacks), and databases are really good at sorting operations.
If you are only talking about a few hundreds-thousands of rows, it's absolutely possible to sort the data locally on your machine - there's a sortDataset expression function or the system.dataset.sort scripting function. Whether it makes sense to do that vs creating a near duplicate name query is up to you.
Be aware that querystrings leave you vulnerable to SQL injection but since there is no way for the user to inject into your scripted sort order I think you should be safe.
@dkhayes117 I think that is similar to what Transistor is suggesting, except I'm using MSSQL and it didn't like the MySQL code
@PGriffith I will go with the second Named Query, I think the sort function would work but we may end up with too many data points before the database starts overwriting itself when it hits the 5 year mark. Maybe not on this specific Query but I have about 7 different ones I have working the same way and some of them have a significant amount of different data points compared to this one.
@Transistor I had it set as DESC in my original code, but it's static unlike what I believe you're suggesting. I have this named query running a power table so I don't think I can have it where I use the system.db.runNamedQuery define the sortorder parameter and have the power table display as 'DESC' without having one not work. I did try it, but I may have missed something, I couldn't get them to coexist.
Thank you for all the responses! I really appreciate it.
