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 [email protected] 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 -~----------~----~----~----~------~----~------~--~---
