Just to be inconsistent, with one argument, databases handle this
differently:
postgres:
# select coalesce(0);
coalesce
----------
0
(1 row)
Oracle 10g:
select coalesce(0) from dual
*
ERROR at line 1:
ORA-00938: not enough arguments for function
On Apr 2, 7:08 pm, Ian Kelly <ian.g.ke...@gmail.com> wrote:
> On Fri, Apr 2, 2010 at 3:41 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
>
>
> > Kent wrote:
> > > Along the same lines, is there something we can do about nvl()
> > > (oracle) versus coalesce() (ansi)?
>
> > > They aren't exactly the same, unfortunately (nvl takes exactly 2
> > > arguments, no more), so maybe there is nothing 'official' you can do,
> > > but can you help me work it out for my project?
>
> > > I assume it is very similar to what you helped me out with
> > > above...something like this:
>
> > > ============================================================
> > > from sqlalchemy.sql.expression import ColumnElement
> > > from sqlalchemy.ext.compiler import compiles
> > > from sqlalchemy.types import DateTime, Date
>
> > > class current_date(ColumnElement):
> > > type = Date()
>
> > > @compiles(current_date)
> > > def _compiler_dispatch(element, compiler, **kw):
> > > if compiler.dialect.name == 'oracle':
> > > if compiler.dialect.use_ansi:
> > > return "trunc(current_date)"
> > > else:
> > > return "trunc(sysdate)"
> > > else:
> > > # assume postgres
> > > return "current_date"
> > > ============================================================
>
> > > But the main difference is it takes arguments.
>
> > > Is there a clever way to return the appropriate function, something
> > > like this:
>
> > > def ....
> > > if compiler.dialect.name == 'oracle':
> > > return func.nvl
> > > else:
> > > # assume postgres
> > > return func.coalesce
>
> > I will add this to the docs:
>
> > from sqlalchemy import *
> > from sqlalchemy.ext.compiler import compiles
>
> > from sqlalchemy.sql.expression import FunctionElement
>
> > class coalesce(FunctionElement):
> > name = 'coalesce'
>
> > @compiles(coalesce)
> > def compile(element, compiler, **kw):
> > return "coalesce(%s)" % compiler.process(element.clauses)
>
> > @compiles(coalesce, 'oracle')
> > def compile(element, compiler, **kw):
> > if len(element.clauses) > 2:
> > raise TypeError("coalesce only supports two arguments on Oracle")
> > return "nvl(%s)" % compiler.process(element.clauses)
>
> > print coalesce(5, 6)
>
> > from sqlalchemy.dialects import oracle
> > print coalesce(5, 6).compile(dialect=oracle.dialect())
>
> Might this work as a more complete solution for Oracle?
>
> @compiles(coalesce, 'oracle')
> def compile(element, compiler, **kw):
> sql = "nvl(%s)"
> for i in xrange(len(element.clauses) - 2):
> sql %= "%s, nvl(%%s)" % compiler.process(element.clauses[i:i+1])
> return sql % compiler.process(element.clauses[-2:])
>
> Ian
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
