ACN Validation with SQL (Australian Company Number)

Carrying on from earlier, here's what you need to use to find invalid ACNs in your database.

Exciting stuff.

select replace(acn, ' ', ''), Name
from Copmany C
where acn <> ''
and (acn not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or right(10-
(( CAST( SUBSTRING( replace(acn, ' ', ''), 1, 1 ) AS integer ) * 8 )
+ ( CAST( SUBSTRING( replace(acn, ' ', ''), 2, 1 ) AS integer ) * 7 )
+ ( CAST( SUBSTRING( replace(acn, ' ', ''), 3, 1 ) AS integer ) * 6 )
+ ( CAST( SUBSTRING( replace(acn, ' ', ''), 4, 1 ) AS integer ) * 5 )
+ ( CAST( SUBSTRING( replace(acn, ' ', ''), 5, 1 ) AS integer ) * 4 )
+ ( CAST( SUBSTRING( replace(acn, ' ', ''), 6, 1 ) AS integer ) * 3 )
+ ( CAST( SUBSTRING( replace(acn, ' ', ''), 7, 1 ) AS integer ) * 2 )
+ ( CAST( SUBSTRING( replace(acn, ' ', ''), 8, 1 ) AS integer ) * 1 ) ) % 10, 1)
<> substring(replace(acn, ' ', ''),9,1))

ABN validation with SQL (Australian Business Number)

I'm sure we've all wanted to use Sql to validate every ABN number you've got in your database?

I know I have.

Next time, ACN validation.

select abn, Name
from Company C
where abn <> ''
and ( ( ( CAST( SUBSTRING( abn, 1, 1 ) AS integer ) - 1 ) * 10 )
+ ( CAST( SUBSTRING( abn, 2, 1 ) AS integer ) * 1 )
+ ( CAST( SUBSTRING( abn, 3, 1 ) AS integer ) * 3 )
+ ( CAST( SUBSTRING( abn, 4, 1 ) AS integer ) * 5 )
+ ( CAST( SUBSTRING( abn, 5, 1 ) AS integer ) * 7 )
+ ( CAST( SUBSTRING( abn, 6, 1 ) AS integer ) * 9 )
+ ( CAST( SUBSTRING( abn, 7, 1 ) AS integer ) * 11 )
+ ( CAST( SUBSTRING( abn, 8, 1 ) AS integer ) * 13 )
+ ( CAST( SUBSTRING( abn, 9, 1 ) AS integer ) * 15 )
+ ( CAST( SUBSTRING( abn, 10, 1 ) AS integer ) * 17 )
+ ( CAST( SUBSTRING( abn, 11, 1 ) AS integer ) * 19 ) ) % 89 <> 0

Renovations and engineers

Yesterday the structural engineer didn't turn up, so I spent the whole day working in the backyard on the laptop. Very pleasant autumn day.

Today the structural engineer did arrive on the day we'd agreed -- I'd remembered the wrong day. He was very efficient and organised and arrived at 10am precisely, clearly any mistakes dealing with him are going to be mine :-)

He takes a quick look at what I want to do (take out a wall), and says I'll need a such-and-such sort of beam and probably 3 supports and a new concrete pad, and would I be doing it myself?

Ha, ha, ha!

The materials are cheap, probably around AUD 500, but the know-how is priceless.

There's an analogy between business and my little bit of rennovation. Don't waste your time attempting something you've never done before and will never need to do again, especially when consequences of doing it wrong are large.

Such as upstairs moving downstairs.

Marketing + Advertising = Godzilla Part 2

Back to that meeting with the Advertising + Marketing dudes.

The main reason this meeting was happening was to nut out the nitty-gritty detail of a webservice interface between Them and Us.

It was a reasonably easy security thing:

1. Our site produces an iFrame with src=w.Them.com?Token=ABCDEF&ReplyTo=www.Us.com/Webservice.asmx
2. Their site calls w.Us.Com/WebService.asmx/GetStuff?Token=ABCDEF to retrieve some Stuff to integrate with Other Stuff they need to display.
3. They generate the page and serve it up inside the iFrame.

(BTW this is firewalled so theoretically no-one else can access Our webservice)

It was Their responsibility to write the spec document for this interface. I could see Mr Sideways Notepad was really struggling with the whole Token and webservice concept ("But we'll just display the Stuff in the iFrame"), but the Bright Young Coder was nodding enthusiastically and even asked some useful questions.

Meeting ends with a little frustration on my part, but Bright Young Coder and I - I thought, had an understanding. Mr Sideways Notepad was bipolar -- glum because he'd finished his latte, but happy because he'd drawn some really beautiful boxes and arrows with his 2B pencil.

I learned something that day. Bring crayons and butcher's paper for Marketing and Advertising to play with in the corner, while the real work can happen uninterrupted.

C++ STL, crap, but the best crap we've got*

[* I did a 10 year stint of C++, so I figure I'm vaguely qualified to have an opinion.]

The STL, you either hate it a lot or a little.

Sure there are some folks who think it's an adorable little beast, write books about it, attend conferences, think it's elegant, take it out to dinner and so forth, but to them I say "There are 3 types of people, those who like maths and those who don't".

It's efficient. Too efficient for its own good, frankly. Clearly designed by an academic who didn't think easily readable and easily maintainable were necessary design criteria.

As I get older and crankier I want to spend less time fighting the language syntax, dealing with esoteric class libraries and less time waiting for the compiler to finish doing its languid lap around the pool (GCC you are hopelessly slow, may Microsoft or Intel buy Linux and replace you with a decent compiler).

So there are 2 issues. The STL is uglier than those scrawny things were on Friends, and slower to compile than a book index. Templates you see. Fancy, cunning, clever, smart, ingenious, nifty but achingly slow.

I wondered on a job a few years ago whether the amount of time spent waiting for heavily STL and templatised code to compile and link (6-8 hours multiplied by 3 years) was actually less efficient than if we'd been using a non-template based solution.

Another thing to despise about the STL is the wacky "have at you DOS Liberal scumbags" decision to remove the ".h" from the filenames of headers. 20 years of .h/.hpp etc extensions on all files, and then suddenly "Ha! Things are getting too easy! Let's make this unwanted and inexplicable change just to make you thing we're clever."

It's a shame the shaggy-bearded, pot-smoking, tree-hugging hippy C++ community approved the STL as part of the C++ standard.

Marketing + Advertising = Godzilla

The website I'm involved with during the day recently had an enhancement go live. Looks very pretty, and is doing its thing nicely. But beneath the slick oh-so-cool exterior lies the incesteous unholy offspring of the twins Marketing and Advertising.

One day, in a flash of latte inspired, uh, inspiration M + A decide they know how to manage, if that's the word, a software development project. The warning bells should have gone off from the very first meeting I attended, there were 3 other companies involved. Or 4. Or maybe 2. Anyway, the chief guy from one of these places did something I've never seen before in an IT meeting:

He used a A4 spiral bound notebook with NO LINES, SIDEWAYS with a 2B PENCIL.

His head didn't spin 720. But I bet it can.

The "What do you do" conversation

"What do you do at work?" they ask.

"Computer stuff," I say. [Do you really want to know?]

"What sort of stuff?" they persist.

"Well, it varies." Pause. [You can still get out alive if you leave now. No? Well I warned you.] "My current role is varied. I get to do programming, QA, spec writing, BA, ad-hoc reports, play with being DBA, and generally random stuff.

"But I really want to be a rock and roll star."

Just another blog

So here I am, then.

Lots of space. So unnecessary for me to say anything, really. 12 kazillion blogs, so little time.

Actually, not enough space. There's too much clutter on my screen. Do I really need 35 applications in the taskbar?

Probably.

I imagine it's traditional to say I'm going to update this a lot and be profound or somesuch crud, but I won't. If I've anything interesting to say, I'll say it later.