Home » SQL & PL/SQL » SQL & PL/SQL » wrong data of query result
wrong data of query result [message #305316] Mon, 10 March 2008 07:03 Go to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
I've a query that brings employee information who has a number 7369 ,and when it doesn't find it, it returns the information for the employee who has a number 7499.
the problem is that it brings both employee
this is my query:

SELECT  EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM scott.EMP
where DECODE( EMPNO,7369,ENAME, decode(EMPNO,7499,ENAME)
) is not null


any idea?
Regards
Re: wrong data of query result [message #305319 is a reply to message #305316] Mon, 10 March 2008 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The where clause is computed for each row of the source.
So one row is 7369 and Oracle displays it, and another row is 7499 and Oracle displays it.

Depending of what is your actual problem there are many ways to do it.
For instance:
select * 
from ( select * from emp where empno = 7369
       union all
       select * from emp where empno = 7499
       order by decode(empno,7369,1,2)
      )
where rownum = 1
/

Regards
Michel

[Updated on: Mon, 10 March 2008 07:20]

Report message to a moderator

Re: wrong data of query result [message #305324 is a reply to message #305319] Mon, 10 March 2008 07:42 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
Thanks for your reply, it was very useful.
but if there are other ways to do that without using union because i could have four or five employee.

Regards
Re: wrong data of query result [message #305326 is a reply to message #305324] Mon, 10 March 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said it depends on your actual need.
I put an example with union to show a general case but if you only need to access 1 table you can just use "IN (empno list)".

Regards
Michel
Re: wrong data of query result [message #305523 is a reply to message #305326] Tue, 11 March 2008 03:20 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
Thanks for your reply.
My real problem is that I want the query to return the employees who work in the accounting department and if there isn't any one the query should return the employees in the research departmentand if there isn't any one the query should return the employees in the sales department.

any ideas?
Regards
Re: wrong data of query result [message #305526 is a reply to message #305316] Tue, 11 March 2008 03:28 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
post a test case with desire output.

regards,
Re: wrong data of query result [message #305527 is a reply to message #305523] Tue, 11 March 2008 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the same way: get all records, order them in the inner query and restrict (to the first department found) in the outer one.

Regards
Michel
Re: wrong data of query result [message #305537 is a reply to message #305526] Tue, 11 March 2008 04:07 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
this is the query i tried to write:

SELECT  * FROM emp
where
 DECODE( DECODE (DEPTNO,10,ENAME,NULL), NULL
,decode(DEPTNO,20,ENAME,DECODE (DEPTNO,30,ENAME,NULL)
),ENAME)
IS not NULL


but it gives me all employees work in the three department.

I want the results to be:
if it founds employees in the accounting department it returns them:
deptno    empno
10         7934
10         7839


and if it didn't find any employee it brings the employees in the research department:
deptno    empno
20         7902
20         7876


and if it didn't find any employee it brings the employees in the sales department:
deptno    empno
30         7900
30         7698


Regards
Re: wrong data of query result [message #305546 is a reply to message #305537] Tue, 11 March 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand your problem, you don't understand the solution I posted.
There is an inner query. In this one you select and order all records.
In the outer part you restrict the result to only the part you want.

Regards
Michel
Re: wrong data of query result [message #305584 is a reply to message #305546] Tue, 11 March 2008 05:40 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
I've done what you told me but i didn't know how to restrict the records when the results in more than one records.

SELECT  empno,ename ,deptno
FROM (select empno,ename ,deptno
from emp where DEPTNO in (10,20,30))


Regards
Re: wrong data of query result [message #305598 is a reply to message #305584] Tue, 11 March 2008 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, this is the first step, now you have to order or give a rank from your requirements in the inner query and then restrict to the first rank in the outer part.

Regards
Michel
Re: wrong data of query result [message #305610 is a reply to message #305316] Tue, 11 March 2008 06:27 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
select deptno,empno from emp
where deptno=decode((select count(*) from emp where deptno=10),0,
decode((select count(*) from emp where deptno=20),0,30,20),10);



regards,

[Updated on: Tue, 11 March 2008 06:42]

Report message to a moderator

Re: wrong data of query result [message #305640 is a reply to message #305610] Tue, 11 March 2008 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you can find a query that makes only 1 FTS instead of 3 or N if you have N conditions.

Regards
Michel
Re: wrong data of query result [message #305931 is a reply to message #305640] Wed, 12 March 2008 06:20 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
Thanks for your help.
I've tried to use Rank() but i've the fowling result:

SELECT deptno, ename, empno,sal,
   DENSE_RANK() OVER (PARTITION BY sal ORDER BY deptno) as r
   FROM scott.emp
   WHERE  deptno in (10,20,30)
order by deptno


  DEPTNO ENAME          EMPNO       SAL         R
-------- ---------- --------- --------- ---------
      10 CLARK           7782         0         1
      10 MILLER          7934         0         1
      10 KING            7839      5000         1
      20 SMITH           7369         0         2
      20 SCOTT           7788         0         2
      20 FORD            7902         0         2
      20 JONES           7566         0         2
      20 ADAMS           7876         0         2
      30 ALLEN           7499         0         3
      30 WARD            7521         0         3
      30 MARTIN          7654         0         3
      30 BLAKE           7698         0         3
      30 TURNER          7844         0         3
      30 JAMES           7900         0         3


i didn't know how to get the rows i want.
Regards
Re: wrong data of query result [message #305933 is a reply to message #305316] Wed, 12 March 2008 06:24 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
do you have used my query as it is suitable in your case and also modified form of your first query?

select deptno,empno from emp
where deptno=decode((select count(*) from emp where deptno=10),0,
decode((select count(*) from emp where deptno=20),0,30,20),10);



regards,

[Updated on: Wed, 12 March 2008 06:25]

Report message to a moderator

Re: wrong data of query result [message #305936 is a reply to message #305931] Wed, 12 March 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you partition by sal? A priori, you don't need any partitioning.

You just want rank 1 (r = 1).
So use this query as an inner query and select only rank 1 in the outer part.

Regards
Michel
Re: wrong data of query result [message #305937 is a reply to message #305933] Wed, 12 March 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@mshrkshl

Your query does 3 FTS, hers only 1.
Doesn't this a good reason?

Regards
Michel

[Updated on: Wed, 12 March 2008 06:30]

Report message to a moderator

Re: wrong data of query result [message #305951 is a reply to message #305316] Wed, 12 March 2008 06:55 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Great Michel........
Really your solution is applicable for N level..........

Thanks..
Re: wrong data of query result [message #305956 is a reply to message #305316] Wed, 12 March 2008 07:24 Go to previous message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
Thanks mshrkshl for your help it's realy suitable for me, also Michel solution is realy good for N level.
I'm realy gratful for your help

Regards,

Previous Topic: Cursor problem
Next Topic: sql query
Goto Forum:
  


Current Time: Sun Dec 04 14:48:53 CST 2016

Total time taken to generate the page: 0.07618 seconds