Results 1 to 12 of 12

Thread: Oracle error

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Oracle error

    Hi,

    I have a query

    select a.col1, a.col2, b.col1,
    (select count(*) from table c) as count
    from table a, table b
    where a.col = b.col and
    count > 0

    that returns an error message "count: invalid identifier"

    What is wrong with this query and what is the work around?

    Thanks.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    (select count(*) from table c) as count

    as count should be as "count" .or. count_tablec ... Count by itself is a reserve word

    HTH
    Gregg

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    I've tried that and I am still getting the same error.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I missed this part ...

    where a.col = b.col and
    count > 0

    where a.col = b.col having count(*) > 0

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Stop trying to use SQL keywords in your SQL statement(s).
    > from table a, table b
    -- is NOT valid
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jul 2004
    Posts
    268
    Tried that also. Got another error "not a single-group group function".

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We are NOT mind readers.
    >Tried that also
    Tried what?????????????????
    Please cut & paste BOTH the actual SQL & Oracle's actual COMPLETE response.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You will have to use a grouping ...

    group by col1,col2 etc ...

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select a.col1, a.col2, b.col1,count(*) cnt
    from table a, table b
    where a.col = b.col and
    group by a.col1,a.col2,b.col1;


    Checking for count(*) being greater then zero is not necessary. You will not see the combination if there isn't at least one.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Post

    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Tiger would try this:
    Code:
    SELECT e.ename, e.job, d.dname, 
      (SELECT COUNT(*) FROM SALGRADE) AS count_salgrade
    FROM EMP e, DEPT d
    WHERE e.deptno = d.deptno
    GROUP BY e.ename, e.job, d.dname
    HAVING COUNT(*) > 0;

  12. #12
    Join Date
    Jul 2004
    Posts
    268
    Thanks a lot! It worked.

Posting Permissions

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