Thread: nvl() function
I am trying to find the equivalent in Postgresql to the Oracle sql function nvl(). With nvl() you give two parameters. The first may be a field/column or variable. If the value is not null then it is returned by the function. For example the with query below if the :ClientParameter is passed then only those rows which have a clientdesc matching the parameter are returned. If the :ClientParameter is null then those rows which have clientdesc = clientdesc are returned (all rows): select clientid, clientdescfrom clientswhere ( clientdesc = nvl( :ClientParameter,clientdesc ) ) I have looked thru all the documentation I could find but nowhere were any built-in SQL functions delineated. Does anyone know where the documentation can be found? Thanks, Steve
On Tue, 16 Oct 2001, Steven Dahlin wrote: > I am trying to find the equivalent in Postgresql to the Oracle sql function > nvl(). With nvl() you give two parameters. The first may be a field/column > or variable. If the value is not null then it is returned by the function. > For example the with query below if the :ClientParameter is passed then only > those rows which have a clientdesc matching the parameter are returned. If > the :ClientParameter is null then those rows which have clientdesc = > clientdesc are returned (all rows): > > select clientid, > clientdesc > from clients > where ( clientdesc = nvl( :ClientParameter, clientdesc ) ) > > I have looked thru all the documentation I could find but nowhere were any > built-in SQL functions delineated. Does anyone know where the documentation > can be found? COALESCE is the SQL standard name for this. You'll find details in the documentation, in the Conditional Expressions section (4.10). Copy at: http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-conditional.html -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant