Tuesday, February 1, 2011

An External Table Definition

Yes, I know these examples are everywhere...but this is:
1. The first time I had used fixed-width column specifications

I'll trim it down for the post, but you can find the file here.

I may have mentioned before, but I'm working on putting together a dimensional model for an old Florida Medicaid database. The goal is to provide this to researchers so that they can, umm, research, Autism. Of course this one is near-and-dear to my heart.

So here we go...
CREATE TABLE ext_claim
recipient_id VARCHAR2(12),
month_of VARCHAR2(4),
bucket VARCHAR2(2),
date_of_service DATE,
date_of_payment DATE,
claim_count INTEGER,
units INTEGER,
ndc_code VARCHAR2(11),
record_type VARCHAR2(1),
county VARCHAR2(2)
recipient_id (1:12) CHAR(12),
month_of (13:16) CHAR(4),
bucket (17:18) CHAR(2),
date_of_service (19:26) CHAR(8) DATE_FORMAT DATE mask "YYYYMMDD",
date_of_payment (27:34) CHAR(8) DATE_FORMAT DATE mask "YYYYMMDD",
claim_count (35:35) CHAR(1),
units (36:40) CHAR(5) NULLIF( units = '*****' ),
ndc_code (143:153) CHAR(11),
record_type (154:154) CHAR(1),
county (155:156) CHAR(2)
LOCATION ('CLAIM_200811.txt')
As to the fix-width columns, that was relatively easy to figure out, I just followed the docs.

After creating the definition, I would issue a SELECT COUNT(*) FROM ext_claim to see how many records came back (and to see if any of them didn't match my specification). At first I just changed all the INTEGER, DATE and NUMBER fields to VARCHAR2 data types, it was easier. Of course that's cheating and I have about 100 of these files to load.

At first I wondered if I could include an Oracle function like REPLACE, but searching through the docs didn't return anything, nor did The Google Machine. I looked at the list of Reserved Words though, and found NULLIF. Could it be that easy?

Sure enough it was. After the column specification I just added
NULLIF( units = '*****' )
and it worked. Too easy.


Tom said...

Did you "try" replace to see if it worked? I know in sql loader it does. Haven't "tried" it myself but don't see why it wouldn't work.... then again maybe it doesn't. Don't know until you try it.

oraclenerd said...

I didn't try it as I couldn't find any examples out in the wild, nor could I find something specific in docs.

I might give it a go for the other files I have to load though...