You may sometimes want to do a lookup and return it as a column in your query results – but you don’t want to reference the source table in the FROM clause. In this case you may decide to use a column-level query. I’m not arguing the rights or wrongs of column-level queries, either on performance or theoretical grounds. The problem is that if this approach is applied naively it’s a bug. Continue reading A Column-level Query No-No in SELECT statements
This is a basic tip that will save time when a query isn’t returning the expected results (typically, it’s bringing back an empty set) and you need to find out which join or WHERE clause filter is causing the problem. It’s not impossible to do, of course, but it’s nice to have a method that gets a result quickly without too many edits. Take the following query, which should run on your Oracle instance: Continue reading Debugging sql joins and filters in Oracle
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
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)