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
1
vote
1 answer

Why does the NVL2 function don't accept LONG values in its second and third parameters?

The official Oracle database documentation says that the NVL2 function doesn't allow values of type LONG in its second and third parameters though it doesn't explain why. Why do we have this restriction? Is there any specific reason for this…
n7rider
  • 477
  • 3
  • 12
1
vote
2 answers

Use NVL with string in Django?

On a Django app, I'm using cx_Oracle to display a roster of sports players: def get_players(self, sport_code='', term=0): """ :rtype: object """ con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL) cursor = con.cursor() query = 'select…
Kevin Celebi
  • 25
  • 7
1
vote
4 answers

Oracle NOT IN does not work will nullable fields

I have to compare two tables with identical structure (int not null,int not null, varchar2). In both tables field3 is a nullable. I have the next SQL: Select t1.field1, t1.field2, t1.field3) From table1 t1 Where (field1,field2,field3) …
Carlos
  • 456
  • 2
  • 7
  • 21
1
vote
0 answers

oracle nvl, nvl2 with stored function not working as expected

Calling NVL and NVL2 function with a stored function. Function gets called even when value is not to be returned from that function. Please see following example. create or replace function f1 return varchar2 is begin ---set…
oravib
  • 11
  • 1
1
vote
1 answer

NVL (EXPR1,0) vs NVL(EXPR1,-1)

MERGE INTO A USING ( SELECT B_1,B_2,B_3,SUM(B_4) FROM B GROUP BY B_1,B_2,B_3) srt ON ( A.A_1 = B.B_1 AND NVL (A.A_2, 0) = NVL (B.B_2, 0) AND NVL (A.A_3, ' ') = NVL (B.B_3, ' ') ) WHEN MATCHED THEN …
Rohan K
  • 177
  • 1
  • 3
  • 21
1
vote
1 answer

CASE Statement and NVL provides different output in Oracle11G

I have a string having only digits and a space. I am using CASE and NVL function to get the output. If string is NULL then use the same string value else use 1. I am able to different behavior for SQL and PLSQL. Can anyone provide some explanation…
hemalp108
  • 1,209
  • 1
  • 15
  • 23
1
vote
2 answers

Using nvl in a listagg SQL Oracle

Having problems using an nvl call within a listagg. I am new to SQL so I am still iffy on the syntax.. listagg(nvl(asgu.name, ', '),'In waiting') within group (order by asgu.name) as asgu_name, case when asgu.name is not null then…
Jacked_Nerd
  • 209
  • 3
  • 12
1
vote
4 answers

Why NVL() doesn't work in the following outer join(+)?

I'm trying to outer join the two tables, and when there is a null value displayed in the column of "Full Name", replace it with 'No one'. The outer join worked fine, the problem is, the null value is still null, not 'No one'. The following is my…
Wilheim
  • 123
  • 3
  • 13
1
vote
1 answer

Is this check(nvl(column_name in ('x1','x2'), (another_column.value='something_else')) possible in oracle sql?

I've got this line of SQL code here, which doesn't work. I guess it's because I'm writing the wrong syntax. constraint ck_ceg check(nvl(ceg in ('%rt','%yrt'),'vezérigazgató')) This check would have to check if 'ceg' wether has 'rt' or 'yrt' at the…
Narc0t1CYM
  • 499
  • 6
  • 25
1
vote
2 answers

How to replace empty string with null value in oracle using nvl function

INSERT INTO books(book_id, title, author_last_name, author_first_name, classify) VALUES (1000, 'The Picture of Dorian Gray', 'Wilde', 'Oscar', 'fiction'); INSERT INTO books(book_id, title, author_last_name, author_first_name, classify) VALUES (1001,…
dhruv kadia
  • 45
  • 1
  • 3
  • 10
1
vote
2 answers

Oracle sql 11g - Can an nvl2 function include a select statement?

Not even sure nvl2 is what I should be using, but it seems to make sense to me. If I could get the select to work. The data below is not the actual, but as close as I can get with imagined objects: I tried to post this with a full case of two…
user761758
  • 565
  • 2
  • 6
  • 18
1
vote
0 answers

compute nvl(max(c.EmployeeId),0) in slick using scala play?

I am using slick in play framework. I need to know how to write the following line in slick: compute nvl(max(s.version),0)
h ketab
  • 107
  • 1
  • 2
  • 7
1
vote
2 answers

Oracle SQL Comparing via DECODE

I try to get something in Oracle, if the commission is greater than 0.2 I would like to get 'GOOD', otherwise 'BAD'. And also if the commission is null I want to get 0. I know that is with NVL, but something get wrong with syntax. Can you help…
razi3lCast
  • 115
  • 2
  • 14
1
vote
4 answers

Trigger not executing correctly in Oracle PL/SQL

I have a trigger which executes on AFTER UPDATE. It doesn't work as i want it to. How would i check if a value has changed on a field which is of nullable type? I have the following fields which are of nullable type: FRM_DATE DATE FRM_TIME …
KI1
  • 907
  • 1
  • 13
  • 31
1
vote
2 answers

Putting NVL in an INDEX does not seem to work (Oracle 11.2.0.4.0)

I am trying to understand why NVL works fine in a direct SELECT, but not in an INDEX. Here is shown how it works perfectly before creating the INDEX (columnn foo is varchar2): SELECT id,foo FROM bar WHERE foo IS NULL; 1001 1002 SELECT id,…
Grön Oktober
  • 11
  • 2
1 2 3
…
9 10