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
2
votes
2 answers

NULL handling with subselect in Hibernate Criteria API

I'm constructing a Hibernate Criterion, using a subselect as follows DetachedCriteria subselect = DetachedCriteria.forClass(NhmCode.class, "sub"); // the subselect selecting the maximum 'validFrom' subselect.add(Restrictions.le("validFrom", new…
Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
2
votes
3 answers

SQL substituting NULL using NVL, having problems checking substituted value in the where clause correctly

I am running a query to display messages when the from date is before (<=) the current date and to_date is after (>) the current date. If the to_date is NULL, then i give it a value using the NVL method and make the check n the where clause, and if…
user1971376
  • 93
  • 1
  • 4
  • 10
1
vote
1 answer

How Can I compare a Column in a Self Join with itself if it is Not Null?

I'm doing an update to a temp table in Oracle. My thinking was that I could use NVL to set a value ( essentially making the null value on either side equal ) What happens in the update is that if a number of comparisons hold true then a column is…
dee
  • 609
  • 7
  • 16
  • 24
1
vote
1 answer

NVL error in query Oracle

After updating the oracle the query below starts returning strange results, it should returns column with some values but returns null, after some twiching with the query, found that if I change t.event_id = d.event_id with t.event_id = 125162 query…
Georgi Filipov
  • 332
  • 2
  • 18
1
vote
0 answers

JPAQueryFactory NVL function

I am using this JPAQueryFactory .select to get the data queryFactory.select( Projections.bean(detailDTO.class, qEmpcate.id, qEmcate.date.as("completeDate"), qEmpcate.status, …
Peter Potoma
  • 41
  • 7
1
vote
2 answers

combine nvl into single statement

There are two different tables Table 1: select nvl(a.column1,'dummy_employee') from table1 a; Table 2: select nvl(b.column1,a.column1) from table1 a left join table2 b on a.key_col=b.key_col Logic: If column1 from table1 is null then, return…
Eric
  • 47
  • 4
1
vote
2 answers

Null values in where clause at in statement Oracle Sql

I know I can't simply say NULL values equals to some integer etc. But I wonder what is the output of NULL = 5 or something like that. I don't think it is false either because of the code below. select * from hr.employees where NOT ( …
süleyman
  • 93
  • 10
1
vote
2 answers

Why does NVL work over COALESCE in Oracle?

I have the following column: ... some_column NUMBER(1, 0) DEFAULT NULL NULL, ... which is used to hold a nullable Integer value. Now I have a row with that column filled. I'm performing a patch using Spring's JdbcTemplate, meaning that I want to…
Hasan Can Saral
  • 2,950
  • 5
  • 43
  • 78
1
vote
2 answers

Oracle NVL returning inconsistence result when used with/without CASE statement

Can anybody help me to understand why oracle db is returning inconsistence result when used with/without CASE statement. SELECT NVL(CASE WHEN '' IS NULL THEN NULL ELSE NULL END ,TO_DATE('19010101', 'YYYYMMDD')) a, NVL(CASE WHEN '' IS NOT NULL…
Rahil Husain
  • 570
  • 3
  • 14
1
vote
1 answer

NVL between subqueries in HIVEQL and Talend

Is it possible to call nvl when there are subqueries between them? Code is below. NVL( ( SELECT MIN(DATE_TIME)AS EFFECTIVE_DATE FROM TEMP.DM_ZEXPIDA_FICANT Z WHERE…
thecardcaptor
  • 131
  • 7
1
vote
1 answer

Can you rewrite the NVL function?

I'm using an NVL function in my WHERE clause like this. ... AND NVL(FIRST_DATE, SECOND_DATE) BETWEEN (SYSDATE - 7) and SYSDATE What I'm doing is this: If FIRST_DATE is null then use the SECOND_DATE for comparison. The problem with this is that it…
Victor O
  • 91
  • 6
1
vote
1 answer

Oracle PL/SQL speed of NVL/LENGTH/TRIM calls versus IS NOT NULL AND != ' '

I try to find the best way to check if a CHAR/VARCHAR2 variable contains characters (NULL or spaces should be considered the same, as "no-value"): I know there are several solutions, but it appears that (NVL(LENGTH(TRIM(v)),0) > 0) is faster than (v…
Seb
  • 59
  • 9
1
vote
1 answer

Update statement is slow with sum and nvl function

I have a procedure , in which a table's columns is being filled using sum and nvl functions on other tables' column. These update queries are slow and which is making overall Proc slow.One of such update query is below: UPDATE t_final wp SET…
Khushi
  • 325
  • 1
  • 11
  • 32
1
vote
2 answers

Oracle SQL to T-SQL conversion with NVL2 function to Case Statement

Currently doing an Oracle to T-SQL statement conversion. Here is what I'm trying to convert: SELECT USC.USER_ID ,NVL2 (MAX(STREET_1), MAX(STREET_1) || CHR (10), '') || NVL2 (MAX(STREET_2), MAX(STREET_2) || CHR (10), '') || NVL2 (MAX(STREET_3),…
PJ.SQL
  • 343
  • 2
  • 10
1
vote
1 answer

how to set NVL on SQL with TO_CHAR(MAX)

I trying to set a nvl() with the value OPEN on -> select to_char(max(CLOSE_DATE),'dd.mm.yyyy hh24:mi:ss') If it is closed is getting the date ('dd.mm.yyyy hh24:mi:ss') otherwise it should display OPEN Any ideas, where I can put…
Stef
  • 139
  • 12
1 2
3
…
9 10