Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Oracle 8i Select statement

    I am working with Oracle 8i and having the following requirement:

    Table – Dept

    Table – Emp

    First I need to search whether Dept table having any data with Dept No 10 or not.

    1. Select COUNT(Deptno) from Dept where Deptno=10

    If record found then search the EMP table to know that Deptno 10 should contain JOB type only as ‘CLERK’ else through one err msg(which will perform using JAVA code)

    2. IF count(Deptno)>0 then perform step 3 else do not do enything

    3. Select * from
    Emp E,Dept D
    where E.deptno = D.deptno
    and E.deptno = 10
    and E.job=’CLERK’

    Now my requirement is, I would like to perform the entire task using ONE select statement.

    Is it possible? If yes, please let me know the solution.


    Thanks,
    JD

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Select count(*) from
    Emp E,Dept D
    where E.deptno = D.deptno
    and E.deptno = 10
    and E.job != ’CLERK’
    and rownum = 1

    If that returns 1 then there is a dept 10 and it does contain someone who is not a clerk.

Posting Permissions

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