Sunday, June 08, 2014

a Summary of Blog Posts by Topics

The following is a summary of the blog posts grouped by topics.

Build Predictive Models

Predictive Models vs Business Rules

Predictive Model Variables

Import Text File Into Oracle

Unique Identifiers in Oracle

Recency Frequency Monetary

Oracle Materialized View

Database Link to Oracle on Amazon

Calculate Percent Using SQL

Calculate Cumulative Percent Using SQL

Find Score Cutoff Value Using SQL

Remove Duplicates Using SQL

Calculate Correlation Coefficients Using SQL

Oracle vs SQL Server

NULL Value

Logistic Regression

Random Sampling

Table Insert

Read Only Table

Clustering

Ranking

Find Most Frequent

Median Value

Oracle Source Code

Debug PL/SQL

Hide PL/SQL Scripts

Repair Views

Dump Schema

Move Big Files to Amazon

Sunday, June 01, 2014

MS SQL Server iff function vs "case when"

In SQL server, people sometimes use iff function, which is also an Excel function, to assign different values based on a condition. For example, the following query returns 1 if the amt is less than 50, and 0 otherwise.

select id, iif(amt <50, 1,0) as small_amt from tbl_transaction
However, using iff function is not the best practice as iff is not a standard SQL function. The above query only runs successfully on SQL Server. To make the query work for any relational database, such as Oracle, it is better to use SQL standard "case when" statement as shown below.
select id, case when amt<50 then 1 else 0 end small_amt from tbl_transacton
The above query will run successfully on both Oracle and SQL Server databases as they support SQL standard.