Home » SQL & PL/SQL » SQL & PL/SQL » Sql doubt
Sql doubt [message #230930] Fri, 13 April 2007 07:21 Go to next message
abhioracle
Messages: 2
Registered: April 2007
Junior Member

Dear all,

See below sql

select 1 from x ;

how can i use nvl function with this sql if null comes


Re: Sql doubt [message #230931 is a reply to message #230930] Fri, 13 April 2007 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

Select count(*) from x;

or

select decode(count(*),0,0,1) from x;

returns 1 if there are some rows and 0 otherwise.

Regards
Michel
Re: Sql doubt [message #230933 is a reply to message #230931] Fri, 13 April 2007 07:28 Go to previous messageGo to next message
abhioracle
Messages: 2
Registered: April 2007
Junior Member
ok thank u
Re: Sql doubt [message #230969 is a reply to message #230930] Fri, 13 April 2007 10:38 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I have always disliked using count(*) to check to see if a table contains any data or a particular row. if the table contains a few million rows, or the indexes are lousy it will take a while to return. Simply use an exception block. It is Very quick


....
  begin
   select col1
   into x_col1
   from my_table
   where col2 = 2345;
  exception
   when no_data_found then
    x_col1 := null;
  end if;
...


or to see if any data is in the table, use something like

....
  begin
   select 1
   into x_col1
   from my_table
   where rownum = 1;
  exception
   when no_data_found then
    x_col1 := 0;
  end if;
...

[Updated on: Fri, 13 April 2007 10:39]

Report message to a moderator

Re: Sql doubt [message #230971 is a reply to message #230969] Fri, 13 April 2007 10:43 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good remark.
Finally, it depends on the final goal.

Regards
Michel
Previous Topic: purge recyclebin
Next Topic: procedure with defautl parameter values error!!!!!
Goto Forum:
  


Current Time: Mon Dec 05 12:54:27 CST 2016

Total time taken to generate the page: 0.11365 seconds