Results 1 to 3 of 3

Thread: Not In Clause

  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Lightbulb Unanswered: Not In Clause

    Hi,

    My Following select st. is running from last one day. I think the problem is NOT IN clause.
    Please suggest the alternative select clause.



    SELECT resreq.proj, resreq.ver, substr(resreq.act, -5, 5) act, resreq.rn, null,
    null, resreq.rl, resreq.dr, resreq.ra
    FROM resreq
    WHERE resreq.ver IN ('0', '99')
    AND NOT (resreq.proj, resreq.ver, substr(resreq.act, -5, 5), resreq.rn)
    IN (SELECT actres.proj, actres.ver, substr(actres.act, -5, 5) act,
    actres.rn
    FROM actres, resreq
    WHERE actres.proj = resreq.proj
    AND actres.ver = resreq.ver
    AND substr(actres.act, -5, 5) = substr(resreq.act, -5, 5)
    AND actres.rn = resreq.rn
    AND actres.ver IN ('0', '99'))




    Best Regards,
    Prashant Dalal
    prashantdalal@hotmail.com
    http://prashantdalal.port5.com

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

    Re: Not In Clause

    Try NOT EXISTS, with the subquery corellated (joined) to the main query.

  3. #3
    Join Date
    Dec 2003
    Posts
    74
    DOES THIS WORK?

    SELECT
    resreq.proj,
    resreq.ver,
    substr(resreq.act, -5, 5) act,
    resreq.rn,
    null,
    null,
    resreq.rl,
    resreq.dr,
    resreq.ra
    FROM
    resreq
    WHERE
    resreq.ver IN ('0', '99')
    AND (resreq.proj, resreq.ver, substr(resreq.act, -5, 5), resreq.rn)
    NOT IN (SELECT
    actres.proj,
    actres.ver,
    substr(actres.act, -5, 5) act,
    actres.rn
    FROM
    actres,
    resreq
    WHERE
    actres.proj = resreq.proj
    AND actres.ver = resreq.ver
    AND substr(actres.act, -5, 5) = substr(resreq.act, -5, 5)
    AND actres.rn = resreq.rn
    AND actres.ver IN ('0', '99'))

Posting Permissions

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