label() is quoting "isnull" because "isnull" is listed as a reserved word. literal_column(isnull) is not, because literal_column does exactly what you say.
So id try first not using the word "isnull" for the label. After that, its possible that SQlite isnt going to let you allow ordering by a label, its not standard SQL. what you're really looking for here is "order by <colname> NULLS FIRST". see if sqlite supports that. the next thing to try is "order by CASE WHEN colname IS NULL then "AAAAA" else colname" - that can also be textual or you can use the case() construct. On May 22, 2008, at 8:43 PM, andrew cooke wrote: > > > Hi, > > I have a rather complex query, which may be incorrect (I end up > needing to use a literal column, which suggests I am doing something > wrong). Anyway, it does work OK in MySQL, but when I switch to > SQLite, I see the following error (see below, formatted for easier > reading). > > Is this my fault, or is it a bug? Maybe "as" is not intended for use > here (which I need to use the literal column in the other by later). > If so, how would I do this? > > Thanks, > Andrew > > PS Incidentally, the reason I need "isnull" is to force the ordering > of NULL to come after the ordered numeric values. See > http://www.shawnolson.net/a/730/mysql-sort-order-with-null.html If > there's a better approach to solving that issue then I could avoid > this. > > (OperationalError) near "isnull": syntax error > u'SELECT rules.classification_id, > rules.upper_bound_inclusive IS NULL AS "isnull" > FROM ( > SELECT standards.standard_id AS standard_id > FROM standards > WHERE standards.metric_id = ? > AND (standards.end_date_inclusive > ? > OR standards.end_date_inclusive IS NULL) > ORDER BY standards.end_date_inclusive DESC > LIMIT 1 OFFSET 0 > ) AS anon_1 > JOIN rules ON anon_1.standard_id = rules.standard_id > WHERE rules.upper_bound_inclusive >= ? > OR rules.upper_bound_inclusive IS NULL > ORDER BY isnull ASC, rules.upper_bound_inclusive ASC > LIMIT 1 OFFSET 0' [...params here...] > > Note the quotes around isnull near the start. > > The Python code I used was: > > # parameters needed on call > p_metric = bindparam('metric') > p_date = bindparam('date') > p_value = bindparam('value') > # tables references > stds = self.__context.t.standard > rules = self.__context.t.rule > # incrementally construct the query > # first, we want the latest standard for the metric > std_id = select([stds.c.standard_id], stds.c.metric_id == > p_metric) > # with a date valid until after the measurement > std_id = std_id.where(or_(stds.c.end_date_inclusive > > p_date, > stds.c.end_date_inclusive == > None)) > # and we want the first of those > std_id = > std_id.order_by(stds.c.end_date_inclusive.desc()).limit(1) > # now we want the rules associated with that standard > # (the alias below seems to be a mysql oddity? without it > we > # get an error) > cls_id = select( > [rules.c.classification_id, > > (rules.c.upper_bound_inclusive==None).label(isnull)], > from_obj=std_id.alias().join(rules)) > # but only rules that have an upper bound above the value > cls_id = cls_id.where(or_(rules.c.upper_bound_inclusive >= > p_value, > rules.c.upper_bound_inclusive == > None)) > # and again, sort those so that we get the lowest upper > bound > cls_id = cls_id.order_by(literal_column(isnull).asc(), > > rules.c.upper_bound_inclusive.asc()) > cls_id = cls_id.limit(1) > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
