In a Mental Status Examination a psychiatrist will assess the patient, often to decide whether they should be admitted to hospital. Quoting the article linked above, “Orientation is assessed by asking the patient … what time it is (time, day, date)”. So the patient is asked what day of the week it is.
What if the psychiatrist were to ask instead, “What is the number of today, based upon where it falls among the days of the week”? That might indicate that the doctor himself was under too much pressure and needed to take some time off, and it would also be quite unfair to the patient. We need to know what day it is, we often need to know when the week starts or finishes, but we don’t usually ask ourselves what number the day is.
There are database functions that provide the day number: in Sql Server it’s:
SELECT DATEPART(weekday, SYSDATETIME()), and in Oracle it would be
TO_CHAR('d', SYSDATE), which you can cast to a number of course. Do we ever need to know it? Continue reading Just Say No… to Day Number Functions
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
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
An ETL batch should not fail because of minor source data errors. In data warehousing it is assumed that there will be data quality issues. These should be dealt with. If they are not handled properly the batch may break on flaws which have no importance in reporting terms.
Oracle’s DML Error Logging feature has been around for a while and can prevent batch failures Continue reading Planning the use of Oracle DML Error Logging to make an ETL Batch more reliable
In a data warehouse you may have to drop columns on very large tables (try to avoid it if you can), and the size of the table may cause complications. The most obvious is the time it may take to do a straightforward ALTER TABLE DROP COLUMN statement. You probably don’t want to wait for an hour in the middle of your implementation for the DROP statement to execute. Continue reading Dropping columns from large tables in Oracle
I work in data warehousing in banking, where we handle large amounts of data and have to change complex sql statements to fix bugs etc.. Individual queries tend to get bigger and bigger. The danger is that they become so complex that fixing one bug can break other parts of the logic. Continue reading Validation of Data Using Set-Based Sql Operations