Had a look at this, but it looks the method with select case etc. is faster, maybe some 20%. This is even without setting up the lookup table.
Also I am not what the benefit is of the order by in your sql. Doing this: update xxx set band = (select band from convert_age ca where xxx.band = ca.age) seems slightly faster and looks simpler. RBS On 11/22/12, Simon Slavin <slav...@bigfraud.org> wrote: > > On 22 Nov 2012, at 10:47am, Bart Smissaert <bart.smissa...@gmail.com> > wrote: > >> Ignore this e-mail, it was a simple mistake from my side and nil to do >> with SQLite. > > It takes a big man to admit a mistake. Thanks for saving us the time of > figuring out your problem. > > By the way ... your original question talks about a problem which can be > solved far more quickly using SQL and a second table. Put a table into your > database which does the conversion for you: > > lowerLimit descriptionText > 0 0 - 10 > 11 11 - 20 > ... > 91 91 - 100 > 101 over 100 > > And create an index on lowerLimit. Then look up the description you want > using > > SELECT descriptionText FROM ageDescriptions WHERE lowerLimit <= [myvar] > ORDER BY lowerLimit DESC LIMIT 1 > > You will always get get one row which has the description you want. You can > do the lookup either before you do your INSERT (to put the description into > the database) or the modern way would be to do the lookup only when you > actually need to know the age band, probably just before you show the data > on the display. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
