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
0
votes
3 answers

Left join and concatenated column displaying blanks instead of (null)

I'm doing a left outer join of two tables where the manager_id of one table can be joined to values in another table's employee_id. The query I wrote runs, but I am trying to use nvl() to replace the null values with the string 'NO_MANAGER'. …
kmancusi
  • 591
  • 3
  • 20
0
votes
2 answers

How to use Oracle's decode function in where clause

I have a query with few filter criteria, one of them is get to the query as integer parameter. I want to use this filter only if this integer is > 0 I can't use NVL as it will never be null. How can I use DECODE in such case? SELECT (columns list) …
Yehuda N
  • 75
  • 1
  • 2
  • 9
0
votes
1 answer

In SQL , how do I JOIN a column that is usually null so that the data is still retrieved?

I want to incorporate a new column into a working SQL query. However, it causes the whole query return nothing at all(because the column is mostly null in the database) . Here's my pared-down code so far : SELECT DISTINCT …
Caffeinated
  • 11,982
  • 40
  • 122
  • 216
0
votes
3 answers

Using NVL function in SQL, please help!

I'm needing to select first and last name in a table which is working fine in the following SQL, but the part that isn't working is the NVL function. The file should show all pilots at a company that fly helicopters and IF they don't have a licence…
Mila
  • 21
  • 5
0
votes
0 answers

How to update a record with prompt.(If null don't update)

I'm creating a script where I need a prompt to come up asking to update the record if I input something, if I leave it null I want to not update. Anyways I figure it has something to do with CASE,or NVL. The first part asks for a number. After I…
user4864508
0
votes
2 answers

Oracle replacing null using NVL check for empty rows

I am running a query to retrieve an integer but want to put a check for nulls. If null I want the query to return 0. How can I do that? I tried this below but it's not working select NVL(A_COUNT, 0) from MYTABLE where VEH_YEAR = '2003'; If A_COUNT…
yalkris
  • 2,596
  • 5
  • 31
  • 51
0
votes
3 answers

display Count of one column from another table even when the count is zero

I have two tables A and B. In Table A (Oracle sql), an unique column(not primary key) code may have some records in table B. Example: Code "A" has 3 entries, Code "B" has 2 entries and code "C" has 0 entries in table B. I want the query to…
user3615185
  • 55
  • 1
  • 8
0
votes
2 answers

Having trouble with my NVL function

So I followed the directions that I was given to me in my live lecture word for word and also researched this in my textbook and I've done everything (So I think) properly but I keep getting a error Which I have looked up to see what it means and it…
Nicolle Chase
  • 21
  • 2
  • 7
0
votes
3 answers

SQL Command not properly ended ( oracle 11g for windows)

I m using this query to get a result of the difference between the start time and end time of an activity. Where the end time is null i wanted to put the minimum value as 500. Please advice and HELP!! select * from table where (end_time -…
user4533129
0
votes
1 answer

Invalid number error - NVL function

I just found out an error (Invalid Number) in this line of SQL code: WHERE NVL((submit_time - req_time) * 24 ,12121) != 12121 Can someone explain what may be the problem?
Sathish Kothandam
  • 1,530
  • 3
  • 16
  • 34
0
votes
1 answer

Oracle Subselect in NVL (Group By required)

I'm facing a problem in Oracle. I had a SQL where some values were fixed. Now I started replacing them with values from a parameter-table. Some of these fixed values where in a NVL(). Simply said my statement is like this. SELECT…
Stix
  • 455
  • 5
  • 16
0
votes
1 answer

What is the purpose of NVL having to evaluate both arguments?

I found out in this question - Oracle Differences between NVL and Coalesce - that NVL always evaluates both arguments. NVL( string1, relace_with) What is the point of evaluating both, if the first argument is not null ? Won't we always return back…
Caffeinated
  • 11,982
  • 40
  • 122
  • 216
0
votes
2 answers

How to Return a Value using CASE, DECODE, and/or NVL

I'm having a bit of trouble with this. I need a formula that will give me an actual result regardless of whether or not the values are NULL and/or 0. See the following; SELECT [...columns...], (NVL(SUM(table1.qty_rtnd), 0) +…
aCarella
  • 2,369
  • 11
  • 52
  • 85
0
votes
1 answer

Oracle: How to incorporate NVL to this FULL Join?

How can I use NVL to replace the null values with zeros in the output of this code? I need the zero's so I can perform addition on these two columns horse_wins + jockey_wins At the moment they always sum to null if just one of the values is null.…
COOLBEANS
  • 729
  • 3
  • 13
  • 31
0
votes
0 answers

Check if value is already in a query field to change the value of another

I'll clarify this: I have a data result with the twist that the two PK's (A and B) are the same, and field C doesn't. Example: A B C D > 14 20 1 null > 14 20 2 1 > 15 20 2 0 As you can see, D field has a null…
user3467255
  • 1
1 2 3
…
9 10