Forum OpenACS Q&A: What is PG equivelent of NVL

Collapse
Posted by Bob OConnor on

I get this server error:

...-conn8674-] Error: Ns_PgExec: result status: 7 message: 
ERROR:  Function 'nvl(varchar, varchar)' does not exist
Unable to identify a function that satisfies the given 
argument types You may need to add explicit typecasts
...-conn8674-] Error: nsd.tcl: Database operation "1row" failed...

So I search thru www (openacs 3.2.2) and find all these files that use the nvl function which it appears that postgress doesn't support.
* Is there a workaround?
* How do I report this to the SDM? It appears to me to be ONE "bug" in many files....
Here's the list:

grep -r nvl *

admin/bookmarks/edit-bookmark.tcl:  "select nvl(local_title,
admin/bookmarks/one-user.tcl:  nvl(local_title,
admin/comments/persistent-edit-2.tcl: "select ... nvl(page_title, 
url_stub)
admin/conversion/sequence-update.tcl: "select nvl(max($column_name)

And more without detail shown:

admin/ecommerce/orders/
admin/links/delete-2.tcl
admin/links/delete.tcl
admin/links/edit.tcl
education/class/one.tcl
events/admin/spam/spam.tcl
homepage/index.tcl
homepage/mkdir-2.tcl
homepage/publish-2.tcl
homepage/publish-2.tcl
intranet/customers/view.tcl
survsimp/admin/one.tcl
wp/bulk-image-upload-2.tcl

I found this while in the admin pages trying to delete a "bad" link that someone posted.
/admin/links/one-page.tcl?page_id=16

Thank you.
-Bob

Collapse
Posted by Roberto Mello on
nvl is an Oracle function that will return the first non-null value. The equivalent in PG is coalesre. You hit a bug... please submit this at the SDM (just mention nvls in several files in the x,y,z modules).

Some of the nvl hits you found are from modules that are not ported at all, like the boomarks module.

Collapse
Posted by Michael A. Cleverly on
The Oracle to Postgres Porting Guide has a section on using coalesce instead of NVL.
Collapse
Posted by Michael A. Cleverly on
Poking around the Postgres docs, apparently version 6.5 had a built-in ifnull function. See: http://www.postgresql.org/users-lounge/docs/6.5/user/functions.htm while 7.0 has a nullif function. See: http://www.postgresql.org/users-lounge/docs/7.0/user/functions.htm. Odd, that the name changed between versions...
Collapse
Posted by Bob OConnor on

Thank you all; For those lurking the equivelent is
coalesce( ) (it was 'mispeld' above!).

Also, I posted this to the SDM.

Further...there is no category in the SDM for --applies to 'Many Modules'.