a Summary of Blog Posts by Topics

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

Build Predictive Models

• How to build predictive models that win competitions.
• Build Predictive Models Using PL/SQL
• Oracle On The Fly Model- Clustering
• Build Best Direct Marketing Predictive Models
• Zip Code and Predictive Models
• More On Zip Code and Predictive Models - Variable of High Cardinality

Predictive Models vs Business Rules

• Predictive Modeling vs Intuitive Business Rules
• Comparison of Business Rules and Predictive Models
• A Real World Case Study: Business Rule vs Predictive Model

Predictive Model Variables

• Find the Most Important Variables In Predictive Models
• How to build predictive models that win competitions. (continued)
• Abraham Lincoln and Predictive Modeling Project
• Build Best Direct Marketing Predictive Models
• Recency, Frequency, Monetary (RFM) Analysis: Part 1
• Recency, Frequency, Monetary (RFM) Analysis: Part 2
• Recency, Frequency, Monetary (RFM) Analysis: Part 3
• Recency, Frequency, Monetary (RFM) Analysis: Part 4
• Recency, Frequency, Monetary (RFM) Analysis: Part 5. RFM Code

Import Text File Into Oracle

• Five Ways of Loading Text Files Into Oracle Database
• A Quick Way to Import Spreadsheet Into a Relational Database
• "Fix" a Text File that Is in Unix Format
• Add Line Number to Text File
• How to Change Delimiters of Text File
• Analyze Text Files in Real Time Using SQL Without Loading Them into Database
• Why Sometime It Is Not a Good Idea to Export Database Tables as Text Files?
• Calculate Histogram for a Text File
• How to Query a Text File In Oracle
• More On Oracle External Table

Unique Identifiers in Oracle

• Five Ways of Creating Unique Record Identifier For Oracle Tables
• How to Generate Globally Unique Identifier on the Fly

Recency Frequency Monetary

• Recency, Frequency, Monetary (RFM) Analysis: Part 1
• Recency, Frequency, Monetary (RFM) Analysis: Part 2
• Recency, Frequency, Monetary (RFM) Analysis: Part 3
• Recency, Frequency, Monetary (RFM) Analysis: Part 4
• Recency, Frequency, Monetary (RFM) Analysis: Part 5. RFM Code

Oracle Materialized View

• Take Advantage of Materialized View
• More On Oracle External Table
• Get the source code for Oracle database views and materialized view
• Refresh an Oracle Materialized View

Database Link to Oracle on Amazon

• Create Database Link to DB on Amazon EC2 Instance
• Compare Oracle Tables in Different Databases: Find an ETL Issue
• Why Sometime It Is Not a Good Idea to Export Database Tables as Text Files?

Calculate Percent Using SQL

• Three Ways of Calculating Percentage of Counts Using Oracle SQL
• Three Ways of Calculating Percentage of Quantity Using Oracle SQL

Calculate Cumulative Percent Using SQL

• Calculate cumulative percentage in Oracle

Find Score Cutoff Value Using SQL

• How to Find Out the Cutoff Value for Certain Percentile
• Select top percent in Oracle

Remove Duplicates Using SQL

• Remove Duplicates From Data
• Rank in Oracle: rank() and dense_rank()

Calculate Correlation Coefficients Using SQL

• Calculate correlation coefficient between two variables using SQL
• Calculate the Correlation Coefficient Using SQL
• Calculate correlation matrix using SQL
• Calculate correlation matrix using SQL (continued)

Oracle vs SQL Server

• MS SQL Server iff function vs "case when"
• Oracle Create Table As Select vs SQL Server Select Into

NULL Value

• Calculate Average Value - Watch Out NULL Values
• NULL Value and Logical Comparison
• NULL Value and Order By
• NULL Value and Arithmetic Operations

Logistic Regression

• Data preparation for building logistic regression models
• More on Data Preparation for Logistic Regression Models
• Categorical Variables in Logistic Regression

Random Sampling

• A random sampling method for medical claim audit
• Random Sampling using SQL
• More on random sampling in Oracle

Table Insert

• Insert Records Into an Oracle Table and Rollback
• Oracle Multiple Table Insert
• Oracle Direct Path Insert

Read Only Table

• Make Oracle Table Read Only

Clustering

• Build K-Means Clustering Models Using PL/SQL
• Oracle On The Fly Model- Clustering
• Impute Missing Values Using Clustering Algorithms

Ranking

• Rank in Oracle
• Rank in Oracle: rank() and dense_rank()
• SAS proc rank and its Oracle SQL equivalent

Find Most Frequent

• Find the most frequent items using SQL
• Find the most frequent items using SQL (continued)

Median Value

• Calculate Median value in Oracle

Oracle Source Code

• More on Get Source Code for Oracle Database Objects
• Get the source code for Oracle database views and materialized view

Debug PL/SQL

• Debug Oracle PL/SQL scripts

Hide PL/SQL Scripts

• Hide PL/SQL Scripts For Function or Procedure

Repair Views

• Fix Views That Stop Working

Dump Schema

• Dump the Oracle Schema

Move Big Files to Amazon

• Move Big Files to Amazon Cloud

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.