About the nvl gottcha. Oracle's nvl surprised me because it will cast types - but only in one direction. If your column is a varchar, you can use nvl to get numbers or characters out: nvl(char_col, 'junk') and nvl(char_col, 33) both work. But if the column is a number, nvl(num_col, 'junk') does not work; you can only pull out numbers (e.g. nvl(num_col, 0)). In Oracle I replaced the NVL with a decode testing for NULL; casting using to_char did not throw an error but order of operations meant that I did not get what I wanted:
select nvl(to_char(sum(user_amount), '999,999,999.00'), 'None') as pending_gifts
from gifts where gift_status in ('in progress', 'receivable', 'received')
group by daf_id;
Gives me ".00" rather than "None"
select decode(sum(user_amount),0, 'None', to_char(sum(user_amount), '999,999,999.00')) as pending_gifts
from gifts where gift_status in ('in progress', 'receivable', 'received')
group by daf_id;
Gives me "None" when there are no pending gifts.
