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_claimAs to the fix-width columns, that was relatively easy to figure out, I just followed the docs.
DEFAULT DIRECTORY ag_file_dir
RECORDS DELIMITED BY NEWLINE
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)
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.