As an aside, more recent Oracles support ansi "coalesce",
so it is probably more appropriate to only use nvl() for older, non-ansi
Oracle versions, but if this is only for illustration then that is not a
big deal (unless you don't want people writing to you saying coalesce
does support more than 2 arguments.
In my case, I'd rather rework the "nvl()" function to work on other
engines besides Oracle as an alias to "coalesce()", seems less ambiguous.
Thanks again.
On 4/2/2010 5:41 PM, Michael Bayer 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())
--
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.
