Useless Blogging

December 16, 2007

Totally Random Crystal/SQL Trick

Filed under: SQL — steegness @ 8:22 pm

I wanted to put this up so I didn’t forget it. This is a trick to turn a field into a toggle, allowing for a default date range or a user-specified one. It’s made for Crystal, but would work for anything Oracle-based, I think, with some tweaking.

whateverdate between (decode(‘{?Timespan}’, ‘DEFAULT’, (Last_Day(Add_Months(SYSDATE, -2)) + 1), ‘{?StartDate}’)) and (decode(‘{?Timespan}’, ‘DEFAULT’, (Last_Day(Add_Months(SYSDATE, -1)) + 1)

Thank you for your time.

May 26, 2007

Things I Never Knew About SQL

Filed under: Programming,SQL — steegness @ 7:46 am

(Speaking Oracle, specifically)
When trying to SELECT INTO a variable, if the query returns no rows, an exception is thrown. I’d have thought that the variable would just be NULL, but noooooooooo…

You can nest BEGIN..END blocks to your heart’s content, but you can’t have more than one autonomous block within a given BEGIN..END block. (not that I’ve found, at least).

Exceptions don’t always tell you that they’ve been raised. Sometimes you have to figure it out by noticing that a NULL is where your return value should be.

That is all for now. Carry on, people, carry on.