RE: [sqlite] Age calculation on literal

[RB Smissaert]

Yes, that looks better and thanks for that.
Still get the same error though.
I will keep fiddling with it.

RBS


-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal

On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> Got this nearly worked out now, but somehow I can't get the nested case
> when
> syntax right:
>
> SELECT
> case
> when
> date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
> '2006-10-14')) || ' years') <= date('now')
> then
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
> (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +



You have an extra closing bracket in the line above.


(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
> end
> else
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)   * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14')))) -1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)  * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14')))
> end
> end
>
> It will give me an error (from my VB wrapper) syntax error near else.
> Any idea what is wrong here?


Try this instead, I find the extra indentation makes it easier to see what
you are doing.

SELECT
case when
    date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
    '2006-10-14')) || ' years') <= date('now')
then
    case when
        strftime('%d', 'now') > strftime('%d', '2006-10-14')
    then
        ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
        (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
    else
        (strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
        (strftime('%m', 'now') - strftime('%m', '2006-10-14'))
    end
else
    case when
        strftime('%d', 'now') > strftime('%d', '2006-10-14')
    then
        (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
        (strftime('%m', 'now') +
        (12 - strftime('%m', '2006-10-14')))) -1
    else
        (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
        (strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
    end
end

HTH
Dennis Cote



-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------