Oracle – for when it was like that when you got there

Main menu

Post navigation

Defaulting Null values in SQL*Loader

Having spent the last couple of months working with a South African ( hello Trevor) and being reminded, oh so gently, of that nation’s recent cricketing success, I was hoping to get the opportunity to return the favour via the medium of Rugby Union.

The Springboks narrow defeat of England in the Autumn International did little to derail this cunning plan. Unfortunately, my emergency backup nationality failed at a crucial moment as the hitherto invincible All Blacks were roundly thrashed by England a week later.
People in glass-houses…

Anyway, what follows is a solution to a fairly specific problem we were confronted with recently.

The problem

Imagine you have a flat file which is loaded by means of SQL*Loader into a database table.
For operational reasons, it is not possible to alter the structure of the table.

The data is the fixture list for this year’s Rugby Championship. No, I’m not making any comment about any of the results.

Notice a problem ? Yes, the HOME_POINTS and AWAY_POINTS columns in the table are mandatory, but the file itself contains nulls for these values in some of the records.
So the question is – how do we load this file directly into the table using only SQL*Loader ?

The SQL*Loader Control File

These days, you’ll often find SQL*Loader used for External Tables.
In this instance, we’re going to be using this utility in it’s more traditional command-line mode.
Fortunately, the command itself is fairly simple, because most of the messy stuff can be written separately in a control file.

The control file specifies

the action ( LOAD DATA)

the file containing the data (INFILE)

the table to load into

The delimiter used in the file (FIELDS TERMINATED BY)

treat any missing values at the end of a line as null (TRAILING NULLCOLS)

Finally, and most significantly from our point of view, it specifies how any data needs to be modified before loading.

The formatting of date data is fairly common place in control files.
However, you can also manipulate individual data items in more sophisticated ways such as, for example, assigning a default value when a null exists…

This is by no means the neatest way of manipulating data during upload into Oracle and there may well be performance penalties using this method. However, when you’re stuck for a better alternative, it’s worth remembering that SQL*Loader does have a fair amount of flexibility.

4 thoughts on “Defaulting Null values in SQL*Loader”

Hi! I was very intrigued by the foregoing article and it gave me some ideas for further development of a database that I run that holds the match statistics for a local youth rugby (i.e. rugby union/football) league.

However, I was wondering do you, or other readers, have any ideas or pointers on how best to handle rugby’s bonus points; i.e. 1 BP for 4 tries and/or 1 BP for losing team coming within 7 points or less of the winning score.

I have written a view for Standings that reads from a Master Results table. The Standings table is correct except for one statistic: Bonus Points. Unfortunately, as written, the logic in the View does not distinguish between the losing team coming within 7 points of the winner but awards a bonus point to both teams. I’ve been tearing my hair out trying to fix this.

I’ve posted on a couple of Forums but have not had a response.
Apologies if this is not the right place or correct protocol for posting.

… for Wales, we can see that they’ll get One point for the loss to Australia ( less than 7 point margin), nothing from their defeat to New Zealand, and four points from their win over France.
This makes a total of 5 points, including 1 bonus points.

As for South Africa, they’ll get five points from their win over Australia ( 4 plus a bonus point for tries scored),
One for their defeat to Australia ( 7 points or less)
And two points from their draw against England.
This give them a total of 8 points (including 2 bonus points).

To derive this from the raw data, we can use the CASE statement in our query.
We’re also using an in-line view to tidy up the calculation of overall points

I think the short answer to your question about bonus point calculation is to
use the CASE statement, which as you can see, can handle compound conditions
(lost, but by 7 points or less).

There are a couple of posts which may be of interest ( although they’re based
on the round-ball game, rather than the oval one).This post outlines some possibilities when designing the database.This one makes use of Oracle’s RANK function so that you can order your result
set by multiple criteria easily.

HTH

Mike

P.S. Sorry some of the formatting is a bit wonky. Not quite sure why this is but I hope you get the gist.

First, apologies for not responding sooner! It has been a busy week and I’m only now getting to my “weekend job” as volunteer webmaster & database manager for our local youth rugby football league.

BTW – I’m using MS SQL 2012 managed through MS Visual Studio 2012.

Many thanks for the suggested schema. At first reading, it looks like just what I need. I plan to take a crack this weekend at adapting your scripts to my existing schema. I’ll let you know how I get on.

One observation from a NON-expert when it comes to relational databases. Local, volunteer run, sports leagues, whether rugby, soccer, or whatever sport you like, are ubiquitous around the world. I did a fair bit of research online looking for a SQL script that basically amounted to a cheat sheet on how to set up tables/views and queries for a rugby football Results table and a Standings table – such as the topic of this thread. I could not find anything that I could easily adapt for the Standings. I am, in fact, quite surprised that some sports mad academic in the IT department at some university somewhere has not published online a set of “sports league” scripts for use by volunteers & amateurs like me.