Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38

    Angry Unanswered: * from nested select

    The problem lies in the query here-below

    SELECT * FROM (SELECT * FROM ACTEUR WHERE ROWNUM < 20)
    WHERE ACTCODE IN
    (SELECT ACTCODE FROM (SELECT * FROM ACTEUR WHERE ROWNUM < 10))

    This returns 0 lines.


    However, when I substitute count(*), or ACTCODE, for the first *, I get the correct information.

    Why can't I select *?
    Cordialement

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: * from nested select

    Originally posted by Crassus
    The problem lies in the query here-below

    SELECT * FROM (SELECT * FROM ACTEUR WHERE ROWNUM < 20)
    WHERE ACTCODE IN
    (SELECT ACTCODE FROM (SELECT * FROM ACTEUR WHERE ROWNUM < 10))

    This returns 0 lines.


    However, when I substitute count(*), or ACTCODE, for the first *, I get the correct information.

    Why can't I select *?
    I don't understand your query (what it's for), but it works for me (substituting a table I have):

    SQL> SELECT * FROM (SELECT * FROM emp WHERE ROWNUM < 20)
    2 WHERE empno IN
    3* (SELECT empno FROM (SELECT * FROM emp WHERE ROWNUM < 10));

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
    7369 SMITH CLERK 7902 17-DEC-1980 800 20
    7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30
    7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30
    7566 JONES MANAGER 7839 02-APR-1981 2975 20
    7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30
    7698 BLAKE MANAGER 7839 01-MAY-1981 2850 30
    7782 CLARK MANAGER 7839 09-JUN-1981 2450 10
    7788 SCOTT ANALYST 7566 19-APR-2087 3000 20
    7839 KING PRESIDENT 17-NOV-1981 5000 10

    9 rows selected.

    It appears to be a very long-winded version of:

    SELECT * FROM ACTEUR WHERE ROWNUM < 10;

  3. #3
    Join Date
    May 2003
    Posts
    87

    Re: * from nested select

    Did you try to debug the query ? i.e. try executing the subqueries individually and see how the data looks -- that might answer your question.

    Originally posted by Crassus
    The problem lies in the query here-below

    SELECT * FROM (SELECT * FROM ACTEUR WHERE ROWNUM < 20)
    WHERE ACTCODE IN
    (SELECT ACTCODE FROM (SELECT * FROM ACTEUR WHERE ROWNUM < 10))

    This returns 0 lines.


    However, when I substitute count(*), or ACTCODE, for the first *, I get the correct information.

    Why can't I select *?

  4. #4
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38
    Thanks, Tony

    Quite long-winded as you have said, for it's generated by some PLSQL functions.

    The condensed version is

    SELECT * FROM (RECSET1)
    WHERE [list of cols] IN
    (SELECT [list of cols] FROM (RECSET2))


    However, it should work. What could be the problem here? I'm not an expert DBA, but I'd say that there's a problem in the tuning, optimisation, or in the parametrage, of the data-base.

    Do you have an idea of whence such a problemmay stem?
    Cordialement

  5. #5
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38

    Re: * from nested select

    Originally posted by dbmadcap
    Did you try to debug the query ? i.e. try executing the subqueries individually and see how the data looks -- that might answer your question.
    Indeed, I did, dbmadcap. The fact that I return the correct COUNT is telling.

    If I'm not mad, I'd say, also, that this query works randomly. All points to a sick DB.
    Cordialement

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •