I didn't really look to hard, but you are nesting the nvl()'s I assume?
If so, yes, that is a thought I had (I am just nesting them in user land
instead of in sqla land) but that's really great if that works.
if you don't mind, add your comment to :
http://www.sqlalchemy.org/trac/ticket/1760
On 4/2/2010 7:08 PM, Ian Kelly wrote:
On Fri, Apr 2, 2010 at 3:41 PM, Michael Bayer
<mike...@zzzcomputing.com <mailto: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 <http://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 <http://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.
--
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.
