So… that postcard I made a few days ago and wrote about here is ready to be mailed. But the printers say our address list has some bad postal codes; in particular, Canadian postal codes have had the last digit truncated.
A little investigation, and I find out what’s happened. Our obviously US-centric database restricts postal codes to five characters. The salespeople have been putting the overflow in the ZIP+4 field. So I think of a simple fix:
If there is no ZIP4, return the ZIP as the postal code
If the ZIP4 is four digits, return ZIP+’-‘+ZIP4 as the postal code (US nine digit zip)
Otherwise, concatenate them.
Or in SQL:
CASE
WHEN ZIP_4 IS NULL THEN ZIP
WHEN LENGTH(RTRIM(ZIP_4)) = 4 THEN CONCAT(CONCAT(RTRIM(ZIP),'-'),RTRIM(ZIP_4)))
ELSE CONCAT(RTRIM(ZIP),RTRIM(ZIP_4))
END
I could get fancy and add a space between the two halves of Canadian postal codes, but this will get the mail where it needs to go.
(You can see what happened when I discovered that for some reason, all the returned fields were padded out to their full size… and that STR+STR+… didn’t work, and CONCAT only took two arguments…)
First I tried this in Open Office 2.0 – I about six months ago replaced Office 2000 with Open Office to give it a try. Unfortunately, I couldn’t find any way of writing this in a way that OO2 would understand. I went to someone else’s computer and tried it in Access. Nope, an error but they wouldn’t tell me what it was or give any clue.
Back to my computer. Used SQL80 to form the complete SQL query. But this uses Oracle SQL syntax, which neither Access nor OO2 understand. Oracle Discoverer SUPPOSEDLY does, but it wouldn’t read this (nor tell me why).
Well, all my internal web apps use Oracle syntax, so I write a quick web page to do it. I’d write a query to do it on my Linux machine, but I still haven’t gotten PHP or Python to talk to our Oracle database. Anyway, the web page works fine, but it’s nearly 10,000 names so it drags. I save it to a text file and…
It’s *wrapped*. So a good quarter of the lines are continued on the next line. I start fixing this by hand but oh my god, 10,000 lines… no way. I’ll write a filter on Linux.
Copy the file over to my Linux computer (this one, in fact). It’s been so long since I started using Java as my language of choice that I’ve gotten rusty in C. I get through a first pass of my filter and find that the Windows CRLF vs Unix LF difference is killing the logic by effectively inserting a blank line in between every line. Run the filter. Doesn’t work. Realize it’s because I’m stupid, and fix the obvious logic error in my state machine. Way to diagram these things ahead of time.
Finally it works. I’m happy that I could send out a corrected address list, but very unhappy that it took me two different computers to do something I should be able to do easily with one.
Things that suck:
Open Office 2 Base: What a piece of crap. Unable to handle even marginally complicated SQL.
Open Office Calc: Okay, if I am copying a query result from Base into Calc, and the ZIP field from the Base query is a TEXT field, PLEASE don’t assume it’s a numeric field and strip the leading zero! Come on!
Microsoft Access: I know you can handle complicated SQL. But give me a HINT at least why you think my SELECT statement is bad.
Linux: Don’t tell me you can’t install the Oracle server unless I expand the swap partition when there’s no way I can expand it without reformatting on Ubuntu (PLEASE PROVE ME WRONG! I WANT TO BE WRONG ABOUT THIS!)
gedit (Gnome WM editor): Your KDE competition, Kedit, does syntax highlighting and is a pretty darn good programming editor. How come you don’t?
Hey, whatever. Another rant.
I feel better now.
