Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> LNNVL function
thiyagusham
post Mar 28 2012, 07:58 AM
Post #1


Advanced Member
***

Group: Members
Posts: 78
Joined: 16-March 12
From: Chennai from India
Member No.: 46,939



Hi to all ;
please review following table and clear my question ..


SQL> select * from tes1;

NAME SUB_MARK PRAC_MARK
---------- ---------- ----------
sam 120 70
sona 70 90
rose 90 90
sony 90
maya

SQL> select * from tes1
2 where lnnvl(sub_mark > prac_mark);

NAME SUB_MARK PRAC_MARK
---------- ---------- ----------
sam 120 70
sona 70 90
rose 90 90
sony 90
maya


As per lnnvl function it should display except first record (if condition evaluates true ,
function should return false value, )i.e. first record is true so should not disclose. Am I right ????. Please clarify ..


Go to the top of the page
 
+Quote Post
burleson
post Mar 28 2012, 09:58 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,333
Joined: 26-January 04
Member No.: 13



>> if condition evaluates true , function should return false value

Yes, that first row evaluates to "true" and gence, it should be false and NOT appear in your output:

http://www.dba-oracle.com/t_lnnvl.htm

I reproduced your condition and got different results:

CODE
create table tes1
(
myname varchar2(10),
sub_mark number,
prac_mark number);

insert into tes1 values ('sam', 120, 70);
insert into tes1 values ('sona', 70, 90);

select *
from tes1
where
   lnnvl(sub_mark > prac_mark);


For me, the first rows DOES NOT appear.




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
thiyagusham
post Mar 28 2012, 03:55 PM
Post #3


Advanced Member
***

Group: Members
Posts: 78
Joined: 16-March 12
From: Chennai from India
Member No.: 46,939



thanks for reply
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 30th July 2014 - 12:09 PM