Here are some issues which may catch you out from time to time. In each case Sql Server and Oracle, or Oracle Sql and PL/Sql, or versions of Oracle, work differently. Continue reading SELECT statement gotchas in Oracle and Sql Server
As you probably realise, this post deals with apportionment in finance data warehouses, not the U.S. Constitutional Apportionment Problem, interesting though that topic is. Continue reading Apportionment calculations in Sql Server and Oracle
Have you ever needed to know how often a stored procedure is called, or whether it is used at all? This is easy to find out within the database, but of course an unknown number of outside applications may check in from time to time and they may be have been written by end users. It would be useful to be able to switch on logging of executions for a period of time (prior to an upgrade of the database for example) so that obsolete code can be removed and problems pre-empted. Continue reading Logging the execution of stored procedures in Sql Server
The requirement here is to concatenate multiple rows into a single string, not simply to do a concatenation of columns within a row, which is easy with the the “+” operator or T-Sql’s CONCAT function.
Concatenation of rows is now very straightforward – I believe the change came in with Sql 2012. Continue reading Concatenation of row values in Sql Server 2012 and 2014
This is the last posting in my brief discussion of SQL tricks. I’m dealing here with an issue which causes quite a bit of confusion to newer developers. What’s the point in having LEFT and RIGHT joins defined in the SQL standard, how do you decide which to use, and is there a way to keep things simple? Continue reading Sql Tricks for Tricky Logic (3)
I’ve been working with Linq in .NET lately and there have been a few posts I would have liked to add but since I said I was going to blog SQL tricks I’m putting other topics to one side for the moment.
So… another situation where it would be useful to have an SQL trick is the case of the filtered outer join. Continue reading SQL Tricks for Tricky Logic (2)
One reason occasionally offered for using dynamic SQL widely, or even for keeping business logic out of database stored procedures, is the difficulty of coding optional filters in SQL. Sometimes we may want a row from table X where column A = this, column B = this and column C = this. Other times we may want all the rows where column A = this but we’ll accept any value in columns B and C. Continue reading SQL Tricks for Tricky Logic
I’d like to enforce the user-defined table type returned by a sql function, making the following code possible (I haven’t of course defined the function so it won’t execute): Continue reading Enforcing the Table Type Returned by a Sql Function