%s shortcut?

[Carsten Haese]

On Wed, 2007-09-26 at 02:55 +0000, james_027 wrote:
> hi i have something like this
> 
> cursor.execute("""
>             select c.name,
>                 (select from ap_invoice i where Month(i.date) = 1 and
> Year(i.date) = %s and i.customer_id = c.id and i.status != 'CAN') jan,
>                 (select from ap_invoice i where Month(i.date) = 2 and
> Year(i.date) = %s and i.customer_id = c.id and i.status != 'CAN') feb,
>                 (select from ap_invoice i where Month(i.date) = 3 and
> Year(i.date) = %s and i.customer_id = c.id and i.status != 'CAN') mar
>             from ap_customer c
>             order by %s""",
>             [year, order_by])

In addition to previously made suggestions of using parameter binding,
I'd like to point out the following:

1) Aren't you missing at least one expression between "select" and
"from" in your subqueries? I'd imagine you'd have to select
sum(i.amount) or something like that.

2) Why do you use subqueries at all? I think it would be much more
efficient to let the database return the months as rows instead of
columns, and rearrange the results into columns in Python logic:

cursor.execute("""
   select c.name, month(i.date), sum(i.amount)
     from ap_customer c, ap_invoice i
    where i.customer_id = c.id and i.status != 'CAN'
      and year(i.date) = %s group by 1,2 order by %s"""
   % [year, order_by])

3) Instead of doing a filter on year(i.date), you should try "i.date
between start_date and end_date" (where start_date and end_date are
supplied appropriately). That way, the database might be able to use an
index on the date column, which it might not otherwise be able to do.

Hope this helps,

-- 
Carsten Haese
http://informixdb.sourceforge.net