I'm not sure if ge's a DB pro. But if he's doing that DB work for his company, he's taking DB work from someone else. Not that everyone doesn't start somewhere, but I gave several clues in that answer that just went way over his head.
It takes all kinds, and I probably over-reacted. The whole interaction was just frustrating and I should have given up sooner.
My old company laid off 30 people today. Guess I had them in mind... this guy couldn't understand OUTPUT vs SELECT and my friends didn't even get severance. Just seems a little unbalanced. :-)
even better, those suspect databases are: our email archiver, our HR system and our time-tracking system
We've got full backups and tranlog backups right up until this patching period started, so worst comes to worst we restore those and nobody should be any the wiser
I just want to dig into the cause of the problem a bit first
@gbn To some extent limitations of a query language that's tightly bound to the architecture of the query optimisation system. I did write a missive on this once.
Oracle gets around it to some extent in PL/SQL with some type inference functinality through the %TYPE and %ROWTYPE operators, but I think a set of features like local, paramaterisable synonyms, better functionality to accept table variables or references to tables in stored procedures and so on could be implemented without breaking the query optimiser.
You might get something that needed to recalculate query plans unless it could somehow prove statistical equivalence of what you've supplied as a parameter to whatever the optimiser calculated the original query plan based on.
@gbn If you've got a new database then you can optimise it to play nicely with ORMs - if you've got half-decent modelling skills in-house then you should be able to make an ORM work well.
@gbn It's quite useful if you want to perform some an operation that's tightly coupled to the schema. As an example I did a slowly changing dimension handler once that worked by generating the update and insert queries.
In this case, doing essentially the same thing to a dimension table that could have an arbitrary set of columns (although it did expect the system columns to be in a certain specific format).
One thing I like about ORMs is they really force a well behaved database, although they tend to be a 98% solution where you still need to drop out to custom SQL for some operations.
Dapper.net uses templating - quite a nice approach.
@gbn Yes. It's better to use some custom SQL code for stuff where the ORM doesn't really suit (large batch jobs, complex searches and suchlike) but you should be able to do the vast majority of queries on an operational system through an ORM.
Other situations like distributed transactions if your ORM doesn't support that (or all the targets) properly.
@gbn While I was still in the Brentwood office at my current gig I did quite a lot of maintenance work on a system that had been built on a whole pile of layered views. I think 'encapsulation' has failed when you spend most of your time digging through the views to find where something is calculated.
goddamn logitech's website is a useless piece of shit. it shows me the exact piece of hardware I'm after when I google it (logitech g13), but when I click on the link I get redirected to a local site that can't find the g13
(naturally, straight after complaining about it I figure out I want the second link from google not the first)
@user791022 I looked at your Q earlier. It's not a 5 minute answer so don't expect many responses quickly. It is well written though so I've +1'd it :)
@user791022 From your question it looks you want a cash matching system to track payments against invoiced amounts (and presumably credit control reporting so you can chase unpaid invoices).
@user791022 Doesn't matter how you get the payment. You have the matching process that matches the cash payment record to the invoice.
The payment reference could be a cheque reference, a BACS payment reference, a USM transaction, a credit card payment reference etc.
If it comes with a resolvable invoice reference then you can match automatically, or drop out to a manual process if it's ambiguous. If you do have a manual exception process than wrapping it in some sort of workflow would help as well.
When I say manual I mean that the application can still do the matching, but requires the user to enter in the matching data.
When I sent the invoice to the companies and if they owe me money, it will say somethign like this on the invoice "You should pay on account - sort code: 301411 acc. no: 00110310 last paydate: 21/08-10"
@user791022 OK. Leave some comments on the answer if you want me to clarify something. The answer gives a brief synopsis of how cash matching systems I've worked on work. You can do payments in both directions and overall balances with this midel.
@user791022 So a record of the invoice goes into the invoices table and a 'receivable' record goes into the allocations table.
When you get paid then you enter a cash payment record that records the BACS transaction. The operator then enters one or more payment records into the allocations table linking the cash payment record to the invoice (or invoices) it is matched to, and the amounts allocated against each invoice.
@user791022 I've posted a basic cash matching schema with a couple of scenarios and an aged debt query. Unfortunatley I don't have a running mysql instance to hand so I did it on SQL Server.
@user791022 Yes. And, using this schema the aged debt reporting can take the overall balance into account if you group by customer rather than invoice.
Just make sure that the signs of the amounts on the outgoing and incoming invoices and payments are correct.
Note that I haven't put the customer into this schema, but if you imagine a 1:M relationship between customers and invoices, you can group by the customer ID or the invoice reference. Grouping by the customer ID would get you the overall balance for the customer, grouping by the invoice would give you the outstanding invoices.
It will be generating invoices every 2 weeks using Cron Job (time-based job scheduler). It will check through the orders table (to calculate it) and add into invoice table. Let say invoices has been generated and then I need to make some changes in the orders or orders_product table - it will have update Invoice.Amount field?
@user791022 If you've already issued an invoice you will really want to issue an additional invoice for accounting purposes. Once you get into working with money you want to run the system like a set accounts and build it so it's easy to audit. Post additional adjustment transactions and issue new invoices.
I've been using GROUP BY for all types of aggregate queries over the years. Recently, I've been reverse-engineering some code that uses PARTITION BY to perform aggregations. In reading through all the documentation I can find about PARTITION BY, it sounds a lot like GROUP BY, maybe with a littl...
@ConcernedOfTunbridgeWells Indeed :) I was gonna say I have managed to translate your SQL example into MySQL. I went through and I understood it well now.
@DTest Could be worse, you know. Could be Oracle Warehouse Builder.
ETL Tool (n): a cunning device invented by I.T. vendors to neutralise the capabilities of an otherwise powerful and effective database management system.
@ConcernedOfTunbridgeWells Depends on the job. Better MySQL than paying much cash for licensing on a small budget :) I'm not going to bash NoSQL in general
Well, I have been known to recommend BerkleyDB over MySQL for a job where the client had to integrate usage based billing from a Squid proxy into an antedelluvian photocopier card charging system.
A university. The vendor was crowing about how they could make 200,000 SOAP requests per minute on their interface, but (given that I essentially grew up in a university) I knew that would go down like a bucket of cold sick with the sysadmins at a place like that, who tend to be old-school unix geeks.
Essentially the correct architecture for that would have been to run a sub-process off Squid (Squid has an API for this) that interrogated the BerkeleyDB database for the billing information, and a batched process that polled the photocopy card database for any payments and wrote them into the BerkeleyDB store.
@DTest Actually, the same, I think.
Unless they moved to a different proxy server.
Chances are it's the same crusty old unix geeks running the show. :)
@user791022 'Unix Geek' is a slang term for someone who's an expert or otherwise habitual user of Unix. It can range from being perjorative to a badge of pride depending on who's using it.
OK. Slightly different grammar to English, I presume.
I do remember being told sign language has some context sensitivities pecular to its visual nature, like associating particular places with ownership. You sign something identifying a particular space as relating to a person, and then signing other things in that place assoicates them as being owned by that person. Is that correct?
@gbn One of the joys of working for a business that needs external contractors for intelligence.
@DTest Probably some sort of blanket policy about social networking sites. Often these outfits buy in their firewall policies from third parites and just select tick boxes of categories of stuff to block.
Additionally, if you lot want something to do to help pass the time, go find five unanswered questions and either edit the answers, upvote the answers, or go provide new answers.
What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?
CTE
WITH cte (Column1, Column2, Column3)
AS
(
SELECT Column1, Column2, Column3
FROM SomeTable
)
SELECT * FROM cte
Temp Table
SELECT Column1, Column2, Column3
IN...
I'm looking at a query now where I need to pull a bunch of calculated data, then run calculations on the calculated data. The query was very messy, but I wasn't sure if I should use a temp table or a cte
@JNK forgot about indexes, I rarely deal with sets/operations complicated enough I need temp tables let alone indexed ones
In fact I think I removed the only temp tables our web app used because they were incredibly stupid, basically they joined 5 temp tables instead of running 5 subqueries (which, when written properly, were two subqueries)
Additionally, if you lot want something to do to help pass the time, go find five unanswered questions and either edit the answers, upvote the answers, or go provide new answers.
He could have just used 1 temp table with 5 columns + the key. Still no reason for the temp table (did I mention each temp table contained at most one row?) but still
@DTest It's a pain - usually takes months to get all the permissions I actually need to do my job.
I think I.T. policy in these outfits has a fair dollop of security theatre. Never yet seen a corporate firewall that could prevent me from getting a C compiler onto the machine.
In this case I only had to ask and they installed Visual Studio :)
"Save yourself the headache. I need access to the internet to properly perform my consulting function. If this isn't possible, my rate is now rate*300%."
What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?
CTE
WITH cte (Column1, Column2, Column3)
AS
(
SELECT Column1, Column2, Column3
FROM SomeTable
)
SELECT * FROM cte
Temp Table
SELECT Column1, Column2, Column3
IN...
