Things you can't do...

Today I ran across an issue that frustrated me to no end. I had a dollar value in a column that was part of a fixed length record extract I am currently working on. I was attempting to create a scenario wherein the last digit of the dollar value would be an alphanumeric representation of the numeric value in that position when the whole value was negative. For example an eight bit string representing '-9871' would be something akin to '0000987B'.

The primary problem I ran across was an error that I found most perplexing (a problem with DB2 errors in general), but not one that was merely ambiguous as if often the case with DB2 SQLSTATE errors. No this was an altogether more frustrating animal. I was told by DB2 that REPLACE was not a valid function. What was even more frustrating was that a quick verbatim Google search yielded only seven results none of which was even remotely related to the problem. Also threatening to my sanity was the failure of SUBSTR to work in this situation. Imagine my dismay to find something so magnificently eloquent as:

SUBSTR(REPLACE(REPLACE(REPLACE(Tab.Col, '$', ''), ',', ''), 1, 7) REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTR(REPLACE(REPLACE(REPLACE(Tab.Col, '$', ''), ',', ''), 1, 7), '0', 'A'), '1', 'B'), '2', 'C'), '3', 'D'), '4', 'E'), '5', 'F'), '6', 'G'), '7', 'H'), '8', 'I'), '9', 'J')

Did not work.

I was afraid I was going to have to wrestle a beast of a case statement; I just couldn't understand why this wouldn't work. It was perfect in its subtle avoidance of excessive reads, elegant and refined in a way I am too humble to admit, and worse than it not working at all, it only worked for deductions and not for premium. Why god, Why? I nearly mumbled to myself as a sat at my workstation sulking.

It wasn't until I had patted myself on the back for trying to get ahead of the project by going off for some coffee hoping that on this epiphany I would be granted revelation, if only some small glimmer of truth flickering in the corner of my mind's eye to remind me that I am only a junior DBA who forgets simple lessons from school and not a madman who writes scripts to block his SQL from working according to some predictable standard.

As I sat there watching the black Colombian go juice java drip from the filter assembly, I was struck by inspiration. I rushed back to my terminal and without hesitation typed into the search bar "DB2 command 'valid by data type'." Of course I didn't find anything useful. DAMN! I nearly screamed out, why isn't there a listing of commands sorted by valid data type. The thought rolled around in my head as the painfully obvious solution sat the staring blankly back at me blinking with wonder at my inability to fathom it.

Eventually it dawned on that among countless other situations in the world of data where the bidirectionality of a particular notion leads to folly, the reason I had crafted (or copied I don't specifically recall each and every second before the blog was created, or most of them still after for that matter) REPLACE(REPLACE(REPLACE(Tab.Col, '$', ''), ',', '') was to provide compatibility between a string and a decimal representation of a dollar amount, but specifically to make a string compatible with a decimal, and not a decimal compatible with a string. As you probably figured that inner replacement nest needs replacing, and since DB2 doesn't have support for regular expressions (a gripe tangential to my error down an avenue I glanced at longingly) , I was going to have to edit the code rather than write a scriplet to do it for me. The end result was something akin to this:

SUBSTR(CAST(Tab.Col AS CHAR(8)), 1, 7) REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTR(CAST(Tab.Col AS CHAR(8)), 1, 7), '0', 'A'), '1', 'B'), '2', 'C'), '3', 'D'), '4', 'E'), '5', 'F'), '6', 'G'), '7', 'H'), '8', 'I'), '9', 'J')

The lesson I have learned from this, is this: If I find myself so lacking in the brain power to solve a problem not present with every instance of a code snippet that I am contemplating resigning myself to stupidity and taking up drooling on myself, I should check to see if the command/keywords I am using are appropriate to the data type (often one of the first lessons in a book on SQL), because there are things that just aren't done like adding J to 8 and getting i^2.