Stack Overflow
Stack Overflow

Questions tagged [nvl]

`nvl` is a SQL function present in its vendor-specific implementation by Oracle. It takes two arguments, and returns the first argument if it's non-null, and otherwise returns the second. It's similar to the function `coalesce` in standard SQL.

Questions having this tag should probably also be tagged with and/or , as well as and the corresponding version tag, as explained here.

Syntax:

nvl(expr1, expr2)

Return value: expr1 if expr1 is not NULL; expr2 otherwise.

Example query:

SELECT patient, nvl(discharge_date, CURRENT_DATE) as last_seen FROM patients

Useful links:

Wikipedia on Null Coalescing Operator

Oracle documentation on nvl

139 questions
238
votes
8 answers

Oracle Differences between NVL and Coalesce

Are there non obvious differences between NVL and Coalesce in Oracle? The obvious differences are that coalesce will return the first non null item in its parameter list whereas nvl only takes two parameters and returns the first if it is not null,…
Tom Hubbard
  • 15,820
  • 14
  • 59
  • 86
28
votes
2 answers

Is there a function equivalent to the Oracle's NVL in MySQL?

I'm selecting the max of a column from a table. But there is one problem: if there are no rows in the table, it returns null. I want to use a function which will return a certain value if the result is null. For example with Oracle there is the NVL…
user833129
17
votes
3 answers

Using NVL for multiple columns - Oracle SQL

Good morning my beloved sql wizards and sorcerers, I am wanting to substitute on 3 columns of data across 3 tables. Currently I am using the NVL function, however that is restricted to two columns. See below for an example: SELECT ccc.case_id, …
Heisenberg
  • 267
  • 3
  • 6
  • 15
11
votes
3 answers

COALESCE in JPA namedQuery

I have the following namedQuery select new test.entity.Emp(COALESCE(k.projectId,'N') as projectId, k.projectName) from Emp o inner join o.projects k However I am getting error expecting RIGHT_ROUND_BRACKET, found '(' How to handle COALESCE in…
Jacob
  • 14,463
  • 65
  • 207
  • 320
11
votes
3 answers

Oracle NVL with empty string

I have this table where NULL is the NULL value, not the string NULL: MYCOL -------- NULL example Why does this query not return the NULL row? select * from example_so where nvl(mycol, '') = '';
kmkaplan
  • 18,655
  • 4
  • 51
  • 65
10
votes
2 answers

Oracle nvl2 not working in stored procedure PLS-00201: identifier 'NVL2' must be declared

As title, I'm writing some Stored Procedure on Oracle, first I checked the version SELECT * FROM v$version; with result Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE …
RRTW
  • 3,160
  • 1
  • 35
  • 54
8
votes
1 answer

Why are NVL functions called "NVL"?

Sometimes I see an nvl() function in code. In the case of Java, it looks like this: public static String nvl(String value, String alternateValue) { if (value == null) return alternateValue; return value; } I understand what this…
c0rp
  • 501
  • 1
  • 10
  • 31
8
votes
4 answers

Why does NVL always evaluate 2nd parameter

Does anyone know, why Oracle's NVL (and NVL2) function always evaluate the second parameter, even if the first parameter is not NULL? Simple test: CREATE FUNCTION nvl_test RETURN NUMBER AS BEGIN dbms_output.put_line('Called'); RETURN 1; END…
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
7
votes
3 answers

Select statement inside NVL

I'm trying to run the following query: select a.*, case when NVL (SELECT max(b.field1) FROM b where b.field2 = a.tbl_a_PK , 'TRUE') = 'TRUE' then 'has no data in b' else 'has data in b' end as…
Naama Zrihen
  • 217
  • 1
  • 3
  • 9
6
votes
1 answer

Coalesce equivalent in Hibernate Criteria query?

I want to write the following query as a Hibernate Criteria query: select to_char(nvl(ol.updated_datetime, ol.created_datetime), 'dd/mm/yyyy'), sum(discount_price) from order_line ol where nvl(ol.updated_datetime,…
JMM
  • 3,922
  • 6
  • 39
  • 46
5
votes
2 answers

Does anyone know how to reproduce an NVL() function in linq

So I need to do a query where I need a bunch of NVL's but I need to do these in linq (if it helps the db backend is BD2 and we are using subsonic) I searched the web for "NVL linq" and didn't really find anything useful so I am asking here, Thanks…
Kenn
  • 2,709
  • 2
  • 29
  • 60
4
votes
1 answer

Joins: How to Return default values for empty right side of left outer join

In my Oracle DB, I have a left outer join for a parent workorder to its child workorders. I then run a calculation performing a SUM() of some of the child values. I wrap the results from the child workorders in Nvl() to ensure they'll be calculated…
SeanKilleen
  • 8,809
  • 17
  • 80
  • 133
4
votes
5 answers

Oracle nvl need to insert null into a number field

This is a fallback script for a delete script i am running. Here is the query i am using to generate the insert statements. select 'insert into remark_element(ELEMENTID, REMARKID, …
rtd353
  • 105
  • 1
  • 9
4
votes
2 answers

Oracle NVL function not allows second parameter as datetime

select nvl(trunc(null),trunc(sysdate)) from dual; While executing above query i am getting following error ORA-00932: inconsistent datatypes: expected NUMBER got DATE look like when i take string or number instead of trunc(sysdate) it run fine.
Rashmin Javiya
  • 5,173
  • 3
  • 27
  • 49
3
votes
4 answers

Oracle nvl in where clause showing strange results?

I have a web form that allows users to search on and edit records from an Oracle table based on parameters passed in to a proc. Here's my data: CAE_SEC_ID SEC_CODE APPR_STATUS 1 ABC1 100 2 ABC2 100 3 ABC3 …
Ciarán Bruen
  • 5,221
  • 13
  • 59
  • 69
1
2 3
…
9 10