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

Checking using nvl2 with multiple group by

I have a table like ------------------------ S.No Name Amount Imp_Num 1 A 10 12345 2 B 20 3 A 30 4 C 40 4555 5 B 50 -------------------------- and I want something like…
Pooja smithi
  • 53
  • 5
3
votes
1 answer

Hibernate + nvl()= Oracle 01465

Oracle error when attempting to execute a native SQL query that uses a null parameter value. I've boiled it down to this test case: String SQL_SELECT = "select * from act t1 where t1.G074=:G074 AND t1.G542=:G542 AND …
asu
  • 51
  • 1
  • 8
3
votes
2 answers

pig latin relaxed equals == with null?

in a relation X with only 1 row X.A=null X.B= "blahblah" now I want to do: Y = FILTER X BY A != B ; I intended to say that since A is null and B is not, the condition should be true. but the actual result is that Y is empty, and the condition…
teddy teddy
  • 3,025
  • 6
  • 31
  • 48
3
votes
2 answers

NVL Functionality

select NVL(B.SEQID, A.LVL5_ID) LVL6_ID, NVL(B.NAME, ' ') LVL6_NAME, A.LVL5_ID FROM LVL5 A, ABC B WHERE A.LVL5_ID = B.LVL7 The above query is what defines a view in my system. Here I would like to know why the NVL was used. The thing is that SEQID…
MozenRath
  • 9,652
  • 13
  • 61
  • 104
2
votes
2 answers

Is there any way to set 'varchar' in number type?

When I write: SELECT last_name, NVL(commission_pct, 0) FROM Hr.employees; it works fine. When commission percentage is null, then it is set to 0. But I want to set it to 'NO COMMISSION' instead of 0.
Emdadul Islam
  • 23
  • 5
2
votes
3 answers

oracle query help using NVL similar function to set default

How do I rewrite this oracle query, in the case I have no rows returned and want to hardcode a default value of '0' for a count and the sysdate information? My query now will give me this if there is no data: 1* SELECT count(*) as MYCNT, timestamp…
cjd143SD
  • 859
  • 7
  • 17
  • 28
2
votes
3 answers

NVL vs NVL2 in PL/SQL used in varchar processing

I want to use NVL2 function in string processing, e.g. some_variable := nvl2 (other_variable, '.' || other_variable, ''); For this I receive error PLS-00201: identifier 'NVL2' must be declared Suprisingly, works: some_variable := nvl…
Jan Kohout
  • 93
  • 1
  • 7
2
votes
2 answers

NVL function in Oracle

I want to select all the data from the table employee and make an inner join with an other table for example: SELECT * FROM EMPLOYEE INNER JOIN Deparment ON Employee.Id_Department = Deparment.Deparment_Id AND NVL('Mathematics',…
Danny
  • 47
  • 11
2
votes
1 answer

nvl is not a recognized built-in function name

I am executing below query on SQL Server but getting error: SELECT DISTINCT t1.p_id "Id", (TO_CHAR("sysdate", 'YYYY') + least(SIGN(("sysdate" - to_date('01-Aug-' | | TO_CHAR("sysdate", 'YYYY'), 'DD-Mon-RRRR'))), 0)) "Year" FROM t1, t7, …
Aruna Raghunam
  • 903
  • 7
  • 22
  • 43
2
votes
1 answer

HiveQL join query - NVL not working in where clause

I have a HiveQL query which looks like below: create table JOINED as select TABLEA.* from TABLEA join TABLEB on TABLEA.key=TABLEB.key where nvl(TABLEA.attr, 0)=nvl(TABLEB.attr, 0); But this query doesn't pick those rows where TABLEA.key=TABLEB.key…
Anusha
  • 21
  • 3
2
votes
1 answer

Workaround for the missing NVL() in JPA 1.0

is there a workaround for the missing NVL() function in JPA 1.0 (Eclipselink 1.1.x)? Thank you and best regards
MRalwasser
  • 15,605
  • 15
  • 101
  • 147
2
votes
2 answers

NVL with CAST in Oracle sql?

I want to do something like: select cast(nvl(col1, col2) as Integer) col_name from table1; cast(col1 as Integer) col_name works (returns as Integer) nvl(col1, col2) col_name works (returns as Double) When I try to do both, I get it in the form…
Tiberiu Dorian Moşescu
  • 398
  • 8
  • 20
2
votes
1 answer

JPA coalesce / NVL, bind parameters and database index usage

There is a common approach in Oracle to use predicate NVL(some_column, ' ') = ' ' when we want to use function-based index on nullable column like this: CREATE INDEX idx_some_index ON some_table (NVL(some_column, ' ')); The predicate built in…
svaor
  • 2,205
  • 2
  • 19
  • 41
2
votes
3 answers

Not sure how to use Decode, NVL, and/or isNull (or something else?) in this situation

I have a table of orders for particular products, and a table of products that are on sale. (It's not ideal database structure, but that's out of my control.) What I want to do is outer join the order table to the sale table via product number, but…
RSW
  • 21
  • 1
  • 3
2
votes
1 answer

NVL function in Oracle 11g

I do not have data in one of the tables in the following query; my result set is empty. I'm trying to use NVL() FUNCTION, however; it still gives empty set. Can someone help me to understand this behaviour. My expectation was to display '0',…
aajmee
  • 25
  • 1
  • 3
1
2
3
…
9 10