Understand Oracle MODEL clause in one sentence

CodeProject

Short Version

Oracle MODEL clause allows 3-D access to a data set, compared to regular SQL functions' 1-D access.

Long Version

Oracle MODEL clause let you access any columns in the same row (1st dimension), and in any other rows (2nd dimension). In addition, you can use the calculated results immediately in the same query, enabling sequential calculation (3rd dimension).

Implication

The 3-D access extends SQL's data calculation capability in a revolutionary leap. It is similar to compare a 3-D printer to a hand-held label printer. It enables implementation of complex business logic within SQL, which previously has to be implemented in custom functions or stored procedures, or even outside of database using other programming languages.

Generate rows and sequence data in Oracle and SQL Server

Oracle

There is a simple and efficient way in Oracle to generate arbitrary number of rows and a sequence of integer numbers. The trick is to (ab)use Oracle's "connect by" clause. From the integer sequence, it is trivial to derive other sequential data, such as datetime or varchar. Here is an example,

SQL Server

SQL Server doesn't support "connect by" clause. However, you can use recursive CTE (common table expression) to get similar results, albeit more verbosely. Here is the same example in T-SQL,

Calculate Fiscal Year in SQL

Fiscal years are usually not aligned with calendar years. Given a calendar date, it is a common task to calculate which fiscal year it is in or what date its fiscal year starts on.

Here is how to calculate them in Oracle. Here is the SQL Server version.

Calculate Observed Holidays in Oracle and SQL Server

There are two types of holidays, weekday-based ones and date-based ones. Martin Luther King (MLK) Day, observed on the 3rd Monday of January, is an example of weekday-based holidays. Date-based holidays include Independence Day (4th of July) and Christmas Day (Dec. 25). When a date-base holiday falls on weekend, it is usually observed one day earlier on Friday, or later on Monday.

Here is an example Oracle implementation for calculating observed MLK Day and Independence Day. Here is the equivalent SQL Server implementation.

SQL Server Lookup Table for Daylight Savings Time (DST)

Oracle is DST-aware since 9i (2001). SQL Server, on the other hand, still has no built-in DST support in the latest version (SQL Server 2012). A quick-and-dirty trick to steal DST-support from Oracle to SQL Server is to create a lookup table in Oracle and copy it to SQL Server. Here is an example table.

Generate Insert/Update SQL for DataStage Oracle Connector Automatically (Updated)

CodeProject
Last year I published a post for automatically generating insert/update SQL used in DataStage Oracle connectors. I used Oracle 11g's new function LISTAGG(), which resulted in 2 major limitations.

1. It only applies to Oracle 11g or later.
2. LISTAGG has 4000-character length limit. So it doesn't work on wide tables with more than 100 columns.

Here is an updated version without using LISTAGG(), hence removing the limitations.

Insert SQL:

Update SQL:

DataStage Netezza Connector Sparse Lookup Error: Count field incorrect

CodeProject
Error Message:

Unexpected ODBC error occured. Reason: [SQLCODE=07002][Native=8] Count field incorrect (CC_NZStatement::executeSelect, file CC_NZStatement.cpp, line 137)

Cause:
 One or more fields in the data stream are used multiple times in the lookup SQL statement.

Work-around:
Duplicate the field(s) so that each is used exactly once.