4 ms·
After a coworker left, I was - among other things - put in charge of maintaining a bunch of database views he added to our ERP system to do some reports (on Mic
by krylon 11y ago
After a coworker left, I was - among other things - put in charge of maintaining a bunch of database views he added to our ERP system to do some reports (on Microsoft SQL Server, in case it matters).
It wasn't even that it was so exceptionally clever, but the SQL code made my eyes bleed at times.
So while I know basic SQL fairly well, I am by no means a wizard, and I tend more and more to keep the queries I write as simple as possible and to do the heavy lifting on the client side. Obviously, there are limits - to get the sum of a column, for example, it makes much more sense to do that on the server instead of transferring lots of data to the client and have it compute the sum.
As soon as I want to do more sophisticated stuff, my queries tend to get fairly ugly, and some day some other poor soul might have to look at and understand the code. I'd rather write a simple query and do the more complicated transformations using a PivotTable in Excel.
So while it's nice to know that there are these tricks, I try not to use them (not more than I have, anyway).
- lukaseder 11y agoSo. Rather than having everything in one place (single view) you now integrated some "easier to understand" VB scripts in Microsoft Excel and tied that to the SQL Server? This might have been easier for you at that very moment, but it doesn't sound like an easier to maintain solution... Why not instead take your SQL skills to the next level? It will be very rewarding, and you won't look back to MS Excel based solutions.
- krylon 11y agoWhen I have a program issuing SQL queries, I have to write both anyway, and it seems unlikely that either is going to be reused without the other. So I had to draw a line in dividing the work between my program (a Perl script) and the SQL queries it uses. Using simpler SQL queries and letting Perl sort out the rest resulted (IMHO) in lower total complexity. The same goes for Pivot tables where using simpler queries and then organizing and processing the data in Excel again results in a simpler and more flexible solution in the sense that our controlling / accounting department can play around with the data themselves, not requiring any programming skills. If I have to write a View for some external application, I have little choice but resort to more advanced techniques and readily do so. I never said it isn't fun. ;-) But with SQL, as with all programming languages, I think avoiding unneccessary complexity is desirable. Sometimes using these advanced techniques makes things clearer, sometimes not.