Home » SQL & PL/SQL » SQL & PL/SQL » null problem (10g, winxp)
null problem [message #343932] Thu, 28 August 2008 08:25 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I observed a problem while working on a table

SQL> select * from employees;


Quote:
EMPLOYEE_ID LAST_NAME MANAGER DEPARTMENT_ID
----------- -------------------- ---------- -------------
101 Kochhar 100 1
108 Greenberg 101
109 Faviet 108 2
110 Chen 108
111 Sciarra 108 3
112 Urman 108
113 Popp 108 4
200 Whalen 101
201 Patel 121 5

9 rows selected.



when I run this query it gives me wrong result

Quote:
SQL> select count(*), avg(department_id) from employee;

COUNT(*) AVG(DEPARTMENT_ID)
---------- ------------------
10 2.8




now when I try to run the query with an object it gives proper result

Quote:
SQL> desc ty1
Name Null? Type
----------------------------------------- -------- ----------------------------

A NUMBER
B NUMBER

SQL> select ty1(count(*), avg(department_id)) from employees;

TY1(COUNT(*),AVG(DEPARTMENT_ID))(A, B)
--------------------------------------------------------------------------------

TY1(9, 3)



I think this might be because of the null value in the department_id column but even this gives improper value

SQL> select count(nvl(employee_id, 0)), avg(nvl(department_id,0)) from employees
;

COUNT(NVL(EMPLOYEE_ID,0)) AVG(NVL(DEPARTMENT_ID,0))
------------------------- -------------------------
9 1.66666667


so does that mean that in tables having null values only objects can help? Is there any other way to work on these queries?
Re: null problem [message #343943 is a reply to message #343932] Thu, 28 August 2008 08:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I have observed a problem looking at your observation. To start with
Quote:

SQL> select * from employees;
SQL> select count(*), avg(department_id) from employee;


To me they are two different objects. So I am not sure what you are comparing between these two.

Quote:

select ty1(count(*), avg(department_id)) from employees;
select count(nvl(employee_id, 0)), avg(nvl(department_id,0)) from employees


Again these two complete different. I don't know whether you know or not. Avg function works only on non-null values. So if you see in your resultset out of 9 rows only 5 rows are populated with dept_id with values (1, 2, 3, 4 and 5) -> Which is 15. Divide it by number of non-null dept_id values yield 3. So your first query is returning avg(department_id) as 3 is correct.

In your second query since you use nvl around the department_id you have 9 non-null values. So it become 15 / 9 => 1.6666667.

I am not sure what is the problem ?

Regards

Raj
Re: null problem [message #343945 is a reply to message #343932] Thu, 28 August 2008 08:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't really believe this is the whole story - partly because the table name that you're using in your queries is sometimes EMPLOYEE and sometimes EMPLOYEES

Can you copy and paster the results (including the queries) of running the following in SQL*Plus

SELECT nvl(employee_id,-1) emp_id
      ,department_id
FROM   employees;

SELECT select count(*), avg(department_id) from employees;
Re: null problem [message #344081 is a reply to message #343932] Thu, 28 August 2008 13:48 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
durgadas.menon wrote on Thu, 28 August 2008 09:25

SQL> select ty1(count(*), avg(department_id)) from employees;



I agree with JRow for at least two reasons.

1. Is ty1 is a function? If yes, then your DESC of TY1 is faked.

2. Why would you want the average of a Department number?
Re: null problem [message #344083 is a reply to message #344081] Thu, 28 August 2008 13:59 Go to previous message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Yes..I agree that I had done a mistake

I was not getting the expected results as I was using two different tables employee and employees.

Quote:
1. Is ty1 is a function? If yes, then your DESC of TY1 is faked.


TY1 is an object and this has been mentioned before

sorry guys..I will be more careful with the posts...
Previous Topic: Handling an Error Occurred in Declaration Block of PL/SQL
Next Topic: Trigger in support of a Staging Table on underlying table
Goto Forum:
  


Current Time: Fri Dec 09 21:16:51 CST 2016

Total time taken to generate the page: 0.11889 seconds