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

Stored procedure using NVL() on input parameter - why?

Recently, I have come to analyze a procedure in which they have used below scenario. I want to know what is the usefulness of this ? A procedure (cwrkid, date) select statement WHERE CWRK.cwrkid = NVL(in_cwrk_id,CWRK.cwrkid) and in_cwrk_id is…
zerosleep
  • 11
  • 4
1
vote
2 answers

Multiple NVL() alternative - first not null parameter

Currently I have something like that: NVL(COL1, NVL(COL2, NVL(COL3, NVL(COL4, NVL(COL5, COL6))))) Is in Oracle 11gR2 any function that returns first NOT NULL parameter ?
WBAR
  • 4,924
  • 7
  • 47
  • 81
1
vote
2 answers

Oracle NVL wild card search

I have a search page where for null values I use NVL for null conditions. But for one field I have to do a wildcard search. Is nvl possible with wildcard search eg. NVL(null,%name%)?
user3193026
  • 39
  • 6
1
vote
1 answer

cognos - Calculation if query results missing

I am relatively new to Cognos tools (4months on/off experience). My problem is that i had 3 pages ( page1, page2 and summary page). Page 1 and page 2 are list objects with a total calculation of the total Cost. In the query explorer i have added…
user3017849
  • 93
  • 1
  • 11
1
vote
1 answer

Oracle not evaluating nvl as expected

This query returns rows where colA is null: select * from viewA where colA is null; But this query does not - select * from viewA where nvl(colA, 'X') = 'X'; Any idea why the second query is not returning any results?
Marcus Leon
  • 55,199
  • 118
  • 297
  • 429
1
vote
1 answer

Oracle: Is it possible to accurately join null fields from two different tables?

I have four fields -- itemnum, storeloc, binnum, and quantity. I am trying to select data, where the first three fields match, but binnum is sometimes null. When I perform my sum on quantity, the calculation is not correct because of the NULL…
Sun
  • 2,595
  • 1
  • 26
  • 43
1
vote
3 answers

js function optimization

I found my self quite often parsing values from the so called Name Value Lists in JavaScript. I used a self made function which did the job pretty well, however I decided to experiment with the prototype property. It seems to work, however I find…
Talaveriux
  • 83
  • 1
  • 5
1
vote
3 answers

how to select optional parameter in SQL

I am trying to pass mandatory and optional parameter in sql query using bind variable. The Condition like , If I entered(in WHERE CLAUSE) mandatory parameter(with some value) and optional parameter that could be EMPTY(it means it should be blank )…
user1252398
  • 1,069
  • 7
  • 22
  • 29
0
votes
1 answer

Error in execution case when nvl in oracle

SELECT cast(ID as number) AS ID, cast(MARKETER_ID as NUMBER) AS MARKETER_ID, CAST(NEW_MARKETING_BANKS_ID AS NUMBER) AS NEW_MARKETING_BANKS_ID_ASSIGNE, To_char(To_date(REQUEST_DATE,'YYYY/MM/DD','NLS_CALENDAR=''Gregorian''')) as …
JO OMIDI
  • 3
  • 3
0
votes
2 answers

Select substring in Oracle SQL up to a specific character

Let's consider the following example. Say I have a table column called N.Note and it contains the string: Veg: Lettuce 200 ¶ Fruit: Oranges 200 ¶ Dairy: Milk 300 ¶ This string is not constant and always changes positions. I would like select…
python
  • 1
0
votes
0 answers

How to replace a particular column element with 0 if NULL is found in another column in the same tuple?

My database schema is as follows: Students(sid, firstname, lastname, status, gpa, email) Courses(dept_code, course#, title) Course_credit(course#, credits) Classes(classid, dept_code, course#, sect#, year, semester, limit,…
Podri12
  • 1
  • 1
0
votes
1 answer

Oracle - Replace null values for pivot columns with join from another table

I've a pivot table output and now I want to check for the values from the pivot columns and replace the values if null from another column from another…
ItsMeGokul
  • 403
  • 1
  • 5
  • 16
0
votes
1 answer

Why put NVL around WHEN in SQL?

I was reading a query and came upon the following code: nvl(case when month>start then null else 0 end, 1) and it seemed to me there was no reason for the nvl. Why not do this: case when month>start then 1 else 0 end ?
Bruce
  • 301
  • 2
  • 13
0
votes
1 answer

Decode or NVL, I am trying to avoid duplicates

I am trying to avoid duplicate records. I want sql to pull anyone who has a ('AU','RE', 'RW') record but I only need one to show in my results. Can you use Decode or Nvl for something like this? select distinct r1.sfrstcr_rsts_code,…
Tanque
  • 1
  • 1
0
votes
2 answers

oracle Sql nvl or case which one is faster?

I want to talk about a scenario that we can use both nvl or case in the select statement. In PL-SQL using nvl will be easier that is true. But when it comes to query (in select statement) will using nvl make it slower? I have a package that have…
Atakan Günay
  • 27
  • 1
  • 3
1 2 3
…
9 10