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

sql ora-01427 error single query returning multiple rows

Query: SELECT DISTINCT st_str_hry.id_str_rt, decode(co_strgp.nm_strgp,'HOLLAND & BARRETT','H&B','GNC UK','GNC')brandName FROM co_strgp, st_str_hry WHERE co_strgp.id_strgp=st_str_hry.id_strgp AND co_strgp.nm_strgp in(nvl(:brandName, …
saniya
  • 15
  • 5
0
votes
1 answer

Oracle - No rows returned/inserting a value

I'm trying to create a report which checks an SQL statement for the data returned, and based on result set populate a column with either Y/N. I've made an attempt at using the NVL functionality, but I think I'm missing the point of this function…
cstones88
  • 13
  • 1
  • 8
0
votes
1 answer

Need to query data from a table using nvl function

I have a table which have multiple columns. Say, the columns are C1, C2, C3 and C4. C1 contains daytime, C2 contains names, C3 is the numeric values and C4 have 2 values repeatedly say a and b. Now I need to query data in a way that for daytime,…
user2563162
  • 1
0
votes
2 answers

SQL, Displaying string instead of NULL/actual value

I'm working on an assignment and am having trouble with this question: *Display the department name and the name of all employees plus their manager status. Status should show message ‘is a Manager’ for those who are managers. For those who are not…
user2491621
  • 15
  • 3
0
votes
2 answers

Need query to return zero when count(*) doesn't match any records

SELECT COUNT(*),TRUNC(TEST_DATE_CREATE) FROM TEST_TABLE WHERE TEST_DATE_CREATE > (SYSDATE - 10) GROUP BY TRUNC(TEST_DATE_CREATE); I need the above query to return the count 0 in case of no records present for a particular date. This doesn't seem…
Chittprakash
  • 67
  • 11
0
votes
4 answers

Oracle query to display some template message if the data for a particular value is not present?

I want to display some template message for the value which are not present in the table. Example: select table_name from all_tables where table_name in('RAM','SHA,','EMPLOYEE','E_SCHOOL'); out of the given 4 tables above only EMPLOYEE table is…
Learner
  • 1,544
  • 8
  • 29
  • 55
0
votes
2 answers

Displaying records with a column value 0 when there is no record

I have following tables: Schema tbl_semester_empid {id, semester_name varchar(50), start_date} tbl_batch_empid {id, semester_id,batch_name, session_room} tbl_associate_empid {id, associate_name, batch_id, contact, joining_date, induction_result,…
Mahesha999
  • 22,693
  • 29
  • 116
  • 189
0
votes
2 answers

Are these snippets equivalents? (NVL vs Exception)

I have a doubt managing the situation of asigning values to variables based in sql statements, when no data is found. First, are these blocks equivalents? and if that is true, is better one of the two? declare nonsense number; begin select…
mjsr
  • 7,410
  • 18
  • 57
  • 83
0
votes
2 answers

How can I have a particular timestamp from a SQL query

I have a table in which timestamp is stored as varchar in the format 20120419170525. The column also has some null values. In a SELECT query, I want to apply NVL(my_timestamp, to_timestamp(SYSDATE)), if the value is null then it should display the…
pankaj_ar
  • 757
  • 2
  • 10
  • 33
-1
votes
1 answer

Set the same value to columns if column is null and another value if column is not null using just nvl, decode or coalesce in Oracle

I need write select script that checks if column is null then shows "is null" value, when column is not null then shows "is not null" value in output. But I should use only nvl, decode or coalesce functions. Using another functionalities is not…
This Immortal
  • 29
  • 4
-1
votes
1 answer

Join multiple Select Statements using an NVL Clause

Hi I appreciate all help in advance here, As I am reasonably new to SQL and the Join Types, looking for a little assistance here Query 1 (select ic.ITEM_ID, ic.ITEM_NAME from ITEM_CBO ic where COLOR_DESC = 'BRAND'); Query 2 (select ptt.ITEM_ID,…
Geoff Bird
  • 9
  • 1
  • 5
-1
votes
1 answer

nvl function with in clause

I have a problem with NVL function.I can use my in clause properyl but when i add nvl it gives error. My query like this: SELECT ci.date, SUM (cid.salary) amount, SUM (cid.slary_gross) gross_amount FROM students ci, orders cid …
erhan
  • 15
  • 5
-1
votes
3 answers

How to replace NVL function in a sql query

I need to replace an NVL function into a select for example NVL (MAX(EX.ID_EXAMPLE),0) How can I replace this into my query Thanks a lot
Csanchez
  • 373
  • 1
  • 16
-1
votes
2 answers

REPLACE WITH NVL

select to_number(replace(nvl('-100,000',0),'',','),'999,999,999.99')from dual; ... produces the output: 100000 Now I use the same expression in my procedure with table column: select…
aishwarya m
  • 19
  • 3
-1
votes
1 answer

Oracle nvl function not working as expected

I want to convert null values to ' '. But when I use this code I got null values as 'NULL': SELECT NVL(column_a, ' ') FROM table_a
phileoseda
  • 292
  • 1
  • 6
  • 29
1 2 3
…
9
10