To compare previous values, it's not necessary to use temp tables and update
commands. Just use LEFT OUTER JOIN.
-- following view create values for previous year
CREATE temporary VIEW vPreValues ( toYear, toPreValue) AS SELECT (toYear+1),
toValue FROM TurnOver
-- After that you can compare within one SELECT
SELECT t1.toYear, t1.toValue t2.toPreValue, (IFNULL(t2.toPreValue,'
',(IFGT(t1.toValue,t2.toPreValue,'+','-')) )) +
FROM TurnOver T1 +
LEFT OUTER JOIN vPreValues t2 ON T1.ToYear = T2.ToYear
HINT: This example works only for fixed steps forward, missing years are not
compared.
t1.toYear t1.toValue t2.toPreVa (IFNULL(t2.toPr
---------- ---------- ---------- ---------------
2006 112
2007 105 112 -
2009 128
2010 120 128 -
2011 123 120 +
2012 86 123 -
2013 110 86 +
2014 68 110 -
Best Regards
Adrian
> -----Ursprüngliche Nachricht-----
> Von: [email protected] [mailto:[email protected]] Im Auftrag von Dr. Fritz
> Luettgens
> Gesendet: Dienstag, 8. Juli 2014 20:06
> An: RBASE-L Mailing List
> Betreff: [RBASE-L] - Display Feedback on Data with the IFF function
>
> Hi,
>
> year(col1) turnover(col2) info(col3)
> 2011 3
> 2012 6 +
> 2013 5 -
> 2014 7 +
>
> I want to add to the info column "+" or "-"
> with the UPDATE command using the
> IFF function (IFF('.v1<.v2','+','-'))
> but cannot get it working because I think I
> need the year as reference.
> Is there a different function I can use ?
> Thank´s
> fritz
>
--- RBASE-L
=======================3D=======================3
D=
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
=======================3D=======================3
D=
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
=======================3D=======================3
D=
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
=======================3D=======================3
D=
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
=======================3D=======================3
D=
