Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Unanswered: ORA-00906: missing left parenthesis

    New to dBforums...

    I have been working on the following statement for more than 8 hours. I was able to discover many problems that have been subsequently fixed, however I am stumped by this one...

    SELECT POMAST.PA_MAST_NO, MIN(POPPEV.PI_EVT_DATE), POPPEV.PI_EVT_DISP, POMAST.PA_REF_STAT, POMAST.PA_PRBNAM, POPPEV.PI_EVENT

    FROM TIBURON.POMAST POMAST, TIBURON.POPPPR POPPPR, TIBURON.POPPEV POPPEV

    WHERE POMAST.PA_MAST_NO=POPPPR.PB_MAST_NO AND POPPPR.PB_MAST_NO=POPPEV.PI_MAST_NO AND POPPPR.PB_INVL='DEF' AND (POPPEV.PI_EVENT='CIN' OR POPPEV.PI_EVENT='DINAPP') AND

    NOT EXISTS =
    SELECT *
    FROM "POPPEV.PI_EVENT"
    WHERE" POPPEV.PI_MAST_NO" = "POPPEV.PI_MAST_NO" AND "POPPEV.PI_EVENT" = 'CIN'

    When attempting to execute I get "ORA-00906: missing left parenthesis" and Failed to retrieve data from database.

    I'm not sure what I am doing wrong. Thanks in advance for any and all help...

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT pomast.pa_mast_no,
           MIN(poppev.pi_evt_date),
           poppev.pi_evt_disp,
           pomast.pa_ref_stat,
           pomast.pa_prbnam,
           poppev.pi_event
    FROM   tiburon.pomast pomast,
           tiburon.popppr popppr,
           tiburon.poppev poppev
    WHERE  pomast.pa_mast_no = popppr.pb_mast_no
           AND popppr.pb_mast_no = poppev.pi_mast_no
           AND popppr.pb_invl = 'DEF'
           AND ( poppev.pi_event = 'CIN'
                  OR poppev.pi_event = 'DINAPP' )
           AND NOT EXISTS (SELECT *
                           FROM   "POPPEV.PI_EVENT"
                           WHERE  "POPPEV.PI_MAST_NO" = "POPPEV.PI_MAST_NO"
                                  AND "POPPEV.PI_EVENT" = 'CIN')
    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.

  3. #3
    Join Date
    Apr 2010
    Posts
    7
    Thank you so very much, this has definitely helped. I tested the query, it worked, then modified the script (a little) to add a few more conditions. Thanks again...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    table tiburon.popppr contributes NO data to the SELECT clause & should be eliminated out of FROM clause

    It should be subordinated into WHERE clause.
    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.

  5. #5
    Join Date
    Apr 2010
    Posts
    7
    Okay, I have made that change, also. Thank you, again.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    please post changed code so others can learn from what you did.
    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.

  7. #7
    Join Date
    Apr 2010
    Posts
    7
    Sure, the script includes:
    1) POPPPR.PB_NAM in SELECT statement
    2) Changed "*" to "POPPEV.PI_EVENT"
    3) Included a GROUP BY statement

    SELECT POMAST.PA_MAST_NO, MIN(POPPEV.PI_EVT_DATE),
    POPPEV.PI_EVT_DISP, POMAST.PA_REF_STAT, POPPPR.PB_NAM, POMAST.PA_PRBNAM, POPPEV.PI_EVENT

    FROM TIBURON.POMAST POMAST, TIBURON.POPPPR POPPPR,
    TIBURON.POPPEV POPPEV

    WHERE POMAST.PA_MAST_NO=POPPPR.PB_MAST_NO
    AND POPPPR.PB_MAST_NO=POPPEV.PI_MAST_NO AND POPPPR.PB_INVL='DEF' AND (POPPEV.PI_EVENT='CIN'
    OR POPPEV.PI_EVENT='DINAPP')

    AND NOT EXISTS
    (SELECT POPPEV.PI_EVENT
    FROM TIBURON.POPPEV POPPEV
    WHERE POPPPR.PB_MAST_NO = POPPEV.PI_MAST_NO
    AND POPPEV.PI_EVENT = 'CIN')

    GROUP BY POMAST.PA_MAST_NO, POPPEV.PI_EVT_DISP, POMAST.PA_REF_STAT, POPPPR.PB_NAM, POMAST.PA_PRBNAM, POPPEV.PI_EVENT

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT pomast.pa_mast_no,
           MIN(poppev.pi_evt_date),
           poppev.pi_evt_disp,
           pomast.pa_ref_stat,
           popppr.pb_nam,
           pomast.pa_prbnam,
           poppev.pi_event
    FROM   tiburon.pomast pomast,
           tiburon.popppr popppr,
           tiburon.poppev poppev
    WHERE  pomast.pa_mast_no = popppr.pb_mast_no
           AND popppr.pb_mast_no = poppev.pi_mast_no
           AND popppr.pb_invl = 'DEF'
           AND ( poppev.pi_event = 'CIN'
                  OR poppev.pi_event = 'DINAPP' )
           AND NOT EXISTS (SELECT poppev.pi_event
                           FROM   tiburon.poppev poppev
                           WHERE  popppr.pb_mast_no = poppev.pi_mast_no
                                  AND poppev.pi_event = 'CIN')
    GROUP  BY pomast.pa_mast_no,
              poppev.pi_evt_disp,
              pomast.pa_ref_stat,
              popppr.pb_nam,
              pomast.pa_prbnam,
              poppev.pi_event
    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.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Tiburon? What does it mean (if anything)? Somewhere, back in my head, it rings "shark", but I can't tell for sure nor can I remember what makes me think so.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Tiburon is Shark in spanish. Also a corporate raider, apparently.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes!

    Thank you, #24601

Posting Permissions

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