Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: ORA - 00907 error

    Iam using a VB application to open an oracle database, and I keep getting the error, ORA-00907 missing right paranthesis. The query does run on plsql though.

    What could be wrong?

    These are the queries Iam using.

    sqlQuery = sqlQuery + "Select
    sqlQuery = sqlQuery + " sum(sev1) as Noofsev1,
    sqlQuery = sqlQuery + " sum(sev2) as Noofsev2,
    sqlQuery = sqlQuery + " sum(sev3) as Noofsev3
    sqlQuery = sqlQuery + "from
    sqlQuery = sqlQuery + "(
    sqlQuery = sqlQuery + "select count(*) as sev1,
    sqlQuery = sqlQuery + " 0 as sev2,
    0 as sev3

    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "from t_task t,
    sqlQuery = sqlQuery + " t_defect d,
    t_qareview q,
    sqlQuery = sqlQuery + " t_project p
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "where d.at_projectid=p.at_projectid
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "and ((at_branchcode=47 and at_worklocation like '1%') or (at_branchcode=60 and at_worklocation like '1010101') or
    sqlQuery = sqlQuery + " (at_projwon in ('1002541','1002561','1002600','1002601','1003009' ,'1003090')))
    sqlQuery = sqlQuery + "and d.at_projectid=t.at_projectid
    sqlQuery = sqlQuery + "and d.at_phaseid=t.at_phaseid
    sqlQuery = sqlQuery + "and d.at_itemid=t.at_itemid
    sqlQuery = sqlQuery + "and d.at_itemversionid=t.at_itemversionid
    sqlQuery = sqlQuery + "and d.at_referenceid=t.at_reviewreportid -- for review
    sqlQuery = sqlQuery + "and t.at_tasktype in (1,2) -- for IQA --> tasktype=1, EQA --> tasktype=2
    sqlQuery = sqlQuery + "and d.at_referencetype='Q'
    sqlQuery = sqlQuery + "and d.at_projectid=q.at_projectid
    sqlQuery = sqlQuery + "and d.at_referenceid=q.at_qareviewid
    sqlQuery = sqlQuery + "and q.at_vnvtypecode in (2) -- for doc : 1 for code : 2 for testing : not in 1,2
    sqlQuery = sqlQuery + "and d.at_defectseveritycode in (1)
    sqlQuery = sqlQuery + "and d.at_logdate between to_date('01-sep-2003','dd-mon-yyyy')
    and to_date('30-sep-2003','dd-mon-yyyy')
    sqlQuery = sqlQuery + "
    UNION ALL
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "select 0 as sev1,
    sqlQuery = sqlQuery + " count(*) as sev2,
    0 as sev3
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "from t_task t,
    sqlQuery = sqlQuery + " t_defect d,
    sqlQuery = sqlQuery + " t_qareview q,
    t_project p
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "where d.at_projectid=p.at_projectid
    sqlQuery = sqlQuery + "--and at_projwon in ('2009978')
    sqlQuery = sqlQuery + "and ((at_branchcode=47 and at_worklocation like '1%') or (at_branchcode=60 and at_worklocation like '1010101') or
    sqlQuery = sqlQuery + " (at_projwon in ('1002541','1002561','1002600','1002601','1003009' ,'1003090')))
    sqlQuery = sqlQuery + "and d.at_projectid=t.at_projectid
    sqlQuery = sqlQuery + "and d.at_phaseid=t.at_phaseid
    sqlQuery = sqlQuery + "and d.at_itemid=t.at_itemid
    sqlQuery = sqlQuery + "and d.at_itemversionid=t.at_itemversionid
    sqlQuery = sqlQuery + "and d.at_referenceid=t.at_reviewreportid -- for review
    sqlQuery = sqlQuery + "and t.at_tasktype in (1,2) -- for IQA --> tasktype=1, EQA --> tasktype=2
    sqlQuery = sqlQuery + "and d.at_referencetype='Q'
    sqlQuery = sqlQuery + "and d.at_projectid=q.at_projectid
    sqlQuery = sqlQuery + "and d.at_referenceid=q.at_qareviewid
    sqlQuery = sqlQuery + "and q.at_vnvtypecode in (2) -- for doc : 1 for code : 2 for testing : not in 1,2
    sqlQuery = sqlQuery + "and d.at_defectseveritycode in (2)
    sqlQuery = sqlQuery + "and d.at_logdate between to_date('01-sep-2003','dd-mon-yyyy')
    and to_date('30-sep-2003','dd-mon-yyyy')
    sqlQuery = sqlQuery + "
    UNION ALL
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "select 0 as sev1,
    sqlQuery = sqlQuery + " 0 as sev2,
    count(*) as sev3
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "from t_task t,
    sqlQuery = sqlQuery + " t_defect d,
    sqlQuery = sqlQuery + " t_qareview q,
    t_project p
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "where d.at_projectid=p.at_projectid
    sqlQuery = sqlQuery + --and at_projwon in ('2009978')
    sqlQuery = sqlQuery + and ((at_branchcode=47 and at_worklocation like '1%') or (at_branchcode=60 and at_worklocation like '1010101') or
    sqlQuery = sqlQuery + " (at_projwon in ('1002541','1002561','1002600','1002601','1003009' ,'1003090')))
    sqlQuery = sqlQuery + "and d.at_projectid=t.at_projectid
    sqlQuery = sqlQuery + "and d.at_phaseid=t.at_phaseid
    sqlQuery = sqlQuery + "and d.at_itemid=t.at_itemid
    sqlQuery = sqlQuery + "and d.at_itemversionid=t.at_itemversionid
    sqlQuery = sqlQuery + "and d.at_referenceid=t.at_reviewreportid -- for review
    sqlQuery = sqlQuery + "and t.at_tasktype in (1,2) -- for IQA --> tasktype=1, EQA --> tasktype=2
    sqlQuery = sqlQuery + "and d.at_referencetype='Q'
    sqlQuery = sqlQuery + "and d.at_projectid=q.at_projectid
    sqlQuery = sqlQuery + "and d.at_referenceid=q.at_qareviewid
    sqlQuery = sqlQuery + "and q.at_vnvtypecode in (2) -- for doc : 1 for code : 2 for testing : not in 1,2
    sqlQuery = sqlQuery + "and d.at_defectseveritycode in (3)
    sqlQuery = sqlQuery + "and d.at_logdate between to_date('01-sep-2003','dd-mon-yyyy')
    sqlQuery = sqlQuery + " and to_date('30-sep-2003','dd-mon-yyyy')
    )

  2. #2
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    Quote Originally Posted by smile6
    Iam using a VB application to open an oracle database, and I keep getting the error, ORA-00907 missing right paranthesis. The query does run on plsql though.

    What could be wrong?

    These are the queries Iam using.

    sqlQuery = sqlQuery + "Select
    sqlQuery = sqlQuery + " sum(sev1) as Noofsev1,
    sqlQuery = sqlQuery + " sum(sev2) as Noofsev2,
    sqlQuery = sqlQuery + " sum(sev3) as Noofsev3
    sqlQuery = sqlQuery + "from
    sqlQuery = sqlQuery + "(
    sqlQuery = sqlQuery + "select count(*) as sev1,
    sqlQuery = sqlQuery + " 0 as sev2,
    0 as sev3

    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "from t_task t,
    sqlQuery = sqlQuery + " t_defect d,
    t_qareview q,
    sqlQuery = sqlQuery + " t_project p
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "where d.at_projectid=p.at_projectid
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "and ((at_branchcode=47 and at_worklocation like '1%') or (at_branchcode=60 and at_worklocation like '1010101') or
    sqlQuery = sqlQuery + " (at_projwon in ('1002541','1002561','1002600','1002601','1003009' ,'1003090')))
    sqlQuery = sqlQuery + "and d.at_projectid=t.at_projectid
    sqlQuery = sqlQuery + "and d.at_phaseid=t.at_phaseid
    sqlQuery = sqlQuery + "and d.at_itemid=t.at_itemid
    sqlQuery = sqlQuery + "and d.at_itemversionid=t.at_itemversionid
    sqlQuery = sqlQuery + "and d.at_referenceid=t.at_reviewreportid -- for review
    sqlQuery = sqlQuery + "and t.at_tasktype in (1,2) -- for IQA --> tasktype=1, EQA --> tasktype=2
    sqlQuery = sqlQuery + "and d.at_referencetype='Q'
    sqlQuery = sqlQuery + "and d.at_projectid=q.at_projectid
    sqlQuery = sqlQuery + "and d.at_referenceid=q.at_qareviewid
    sqlQuery = sqlQuery + "and q.at_vnvtypecode in (2) -- for doc : 1 for code : 2 for testing : not in 1,2
    sqlQuery = sqlQuery + "and d.at_defectseveritycode in (1)
    sqlQuery = sqlQuery + "and d.at_logdate between to_date('01-sep-2003','dd-mon-yyyy')
    and to_date('30-sep-2003','dd-mon-yyyy')
    sqlQuery = sqlQuery + "
    UNION ALL
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "select 0 as sev1,
    sqlQuery = sqlQuery + " count(*) as sev2,
    0 as sev3
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "from t_task t,
    sqlQuery = sqlQuery + " t_defect d,
    sqlQuery = sqlQuery + " t_qareview q,
    t_project p
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "where d.at_projectid=p.at_projectid
    sqlQuery = sqlQuery + "--and at_projwon in ('2009978')
    sqlQuery = sqlQuery + "and ((at_branchcode=47 and at_worklocation like '1%') or (at_branchcode=60 and at_worklocation like '1010101') or
    sqlQuery = sqlQuery + " (at_projwon in ('1002541','1002561','1002600','1002601','1003009' ,'1003090')))
    sqlQuery = sqlQuery + "and d.at_projectid=t.at_projectid
    sqlQuery = sqlQuery + "and d.at_phaseid=t.at_phaseid
    sqlQuery = sqlQuery + "and d.at_itemid=t.at_itemid
    sqlQuery = sqlQuery + "and d.at_itemversionid=t.at_itemversionid
    sqlQuery = sqlQuery + "and d.at_referenceid=t.at_reviewreportid -- for review
    sqlQuery = sqlQuery + "and t.at_tasktype in (1,2) -- for IQA --> tasktype=1, EQA --> tasktype=2
    sqlQuery = sqlQuery + "and d.at_referencetype='Q'
    sqlQuery = sqlQuery + "and d.at_projectid=q.at_projectid
    sqlQuery = sqlQuery + "and d.at_referenceid=q.at_qareviewid
    sqlQuery = sqlQuery + "and q.at_vnvtypecode in (2) -- for doc : 1 for code : 2 for testing : not in 1,2
    sqlQuery = sqlQuery + "and d.at_defectseveritycode in (2)
    sqlQuery = sqlQuery + "and d.at_logdate between to_date('01-sep-2003','dd-mon-yyyy')
    and to_date('30-sep-2003','dd-mon-yyyy')
    sqlQuery = sqlQuery + "
    UNION ALL
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "select 0 as sev1,
    sqlQuery = sqlQuery + " 0 as sev2,
    count(*) as sev3
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "from t_task t,
    sqlQuery = sqlQuery + " t_defect d,
    sqlQuery = sqlQuery + " t_qareview q,
    t_project p
    sqlQuery = sqlQuery + "
    sqlQuery = sqlQuery + "where d.at_projectid=p.at_projectid
    sqlQuery = sqlQuery + --and at_projwon in ('2009978')
    sqlQuery = sqlQuery + and ((at_branchcode=47 and at_worklocation like '1%') or (at_branchcode=60 and at_worklocation like '1010101') or
    sqlQuery = sqlQuery + " (at_projwon in ('1002541','1002561','1002600','1002601','1003009' ,'1003090')))
    sqlQuery = sqlQuery + "and d.at_projectid=t.at_projectid
    sqlQuery = sqlQuery + "and d.at_phaseid=t.at_phaseid
    sqlQuery = sqlQuery + "and d.at_itemid=t.at_itemid
    sqlQuery = sqlQuery + "and d.at_itemversionid=t.at_itemversionid
    sqlQuery = sqlQuery + "and d.at_referenceid=t.at_reviewreportid -- for review
    sqlQuery = sqlQuery + "and t.at_tasktype in (1,2) -- for IQA --> tasktype=1, EQA --> tasktype=2
    sqlQuery = sqlQuery + "and d.at_referencetype='Q'
    sqlQuery = sqlQuery + "and d.at_projectid=q.at_projectid
    sqlQuery = sqlQuery + "and d.at_referenceid=q.at_qareviewid
    sqlQuery = sqlQuery + "and q.at_vnvtypecode in (2) -- for doc : 1 for code : 2 for testing : not in 1,2
    sqlQuery = sqlQuery + "and d.at_defectseveritycode in (3)
    sqlQuery = sqlQuery + "and d.at_logdate between to_date('01-sep-2003','dd-mon-yyyy')
    sqlQuery = sqlQuery + " and to_date('30-sep-2003','dd-mon-yyyy')
    )

    Try replacing single quotes with 2 singles quotes (not double quotes)
    Thanks and Regards,

    Praveen Pulikunnu

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello!

    I think it's beter if you write your code in the following form:

    sqlQuery = sqlQuery + "Select
    sqlQuery = sqlQuery + " sum(sev1) as Noofsev1,
    sqlQuery = sqlQuery + " sum(sev2) as Noofsev2,
    sqlQuery = sqlQuery + " sum(sev3) as Noofsev3
    ====>
    PHP Code:
    sqlQuery sqlQuery "Select "
    sqlQuery sqlQuery " sum(sev1) as Noofsev1, "
    sqlQuery sqlQuery " sum(sev2) as Noofsev2, "
    sqlQuery sqlQuery " sum(sev3) as Noofsev3 " 
    or better:
    PHP Code:
    sql "select "
    sql1 " sum(sev1) as Noofsev1, "
    sql2 " sum(sev2) as Noofsev2, "
    sql3 " sum(sev3) as Noofsev3 " 

    sqlQuery sqlQuery sql
    sqlQuery 
    sqlQuery sql1
    sqlQuery 
    sqlQuery sql2
    sqlQuery 
    sqlQuery sql3 
    of course you can make an iteration with counter = i++
    ==> sqlQuery = sqlQuery & i

    Good luck!
    Last edited by julla27; 05-12-04 at 07:56.
    Regards,
    Julia

  4. #4
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    sorry - no iteration!
    Now I've seen the whole code!
    Regards,
    Julia

Posting Permissions

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