This discussion is archived
12 Replies Latest reply: Jan 21, 2011 11:04 AM by MaximDemenko RSS

Can someone  see why im getting error in this query ?

708631 Newbie
Currently Being Moderated
I had 2 queries , instead of using left join i put them together. Now i get error , can someone just take a look to see if syntax wrong somewhere ?
select * from
(
select i.ips,
a.ips,
a.question_type,
sum(a.score) score,
p.project_name,
p.project_segment,p.location,p.project_exec_model,
p.project_exec_model||' - '||p.project_config pmodel,
one.score schedule,two.score cost,three.score execution,four.score commercial,
nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0) as total,
(select sum(prev_score) prev from XT_RISK_PAST2 where ips = i.ips) prev_score,
(select max(createdt) from tbl_risk_answer where (ips,sample_num) in
(select ips,max(sample_num) from VW_RISK_SCORE group by ips) and ips=i.ips) last_dt
from 
(select v.project_id,v.ips,v.sample_num,v.question_id,v.header_desc,v.section_area,v.score,
decode(bi_recurse(q.active_question,1,2),2,'OTR','-')||decode(bi_recurse(q.active_question,1,1),1,'ITO','-') question_type
from VW_RISK_SCORE v left join tbl_risk_question q on v.question_id=q.question_id
where (v.project_id,v.sample_num) in
(select project_id,max(sample_num) sample_num from VW_RISK_SCORE group by project_id)
) a,

(select distinct ips from VW_RISK_SCORE) i,
(select ips, sum(score) score from VW_RISK_SCORE where section_area=1 group by ips) one,
(select ips, sum(score) score from VW_RISK_SCORE where section_area=2 group by ips) two,
(select ips, sum(score) score from VW_RISK_SCORE where section_area=3 group by ips) three,
(select ips, sum(score) score from VW_RISK_SCORE where section_area=4 group by ips) four,
tbl_risk_project p
where i.ips=one.ips(+) and i.ips=two.ips(+) and i.ips=three.ips(+) and i.ips=four.ips(+) and ito on scores.ips=ito.ips
and i.ips=p.ips and  a.question_type='-ITO' group by  i.ips,a.ips, a.question_type, p.project_name, p.project_segment, p.location, p.project_exec_model, p.project_exec_model||' - '||p.project_config, one.score, two.score, three.score, four.score, nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0), (select sum(prev_score) prev from XT_RISK_PAST2 where ips = i.ips), (select max(createdt) from tbl_risk_answer where (ips,sample_num) in
(select ips,max(sample_num) from VW_RISK_SCORE group by ips) and ips=i.ips)
) scores 
and here is error I get.

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 12
ORA-00920: invalid relational operator
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
Error at Line: 30 Column: 4
  • 1. Re: Can someone  see why im getting error in this query ?
    Hoek Guru
    Currently Being Moderated
    To begin with:

    and ito on scores.ips = ito.ips
    will give you an error.
  • 2. Re: Can someone  see why im getting error in this query ?
    708631 Newbie
    Currently Being Moderated
    ok, removed that. but still get error.


    ORA-00604: error occurred at recursive SQL level 1
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 12
    ORA-22818: subquery expressions not allowed here
    00604. 00000 - "error occurred at recursive SQL level %s"
    *Cause:    An error occurred while processing a recursive SQL statement
    (a statement applying to internal dictionary tables).
    *Action:   If the situation described in the next error on the stack
    can be corrected, do so; otherwise contact Oracle Support.
    Error at Line: 30 Column: 347
  • 3. Re: Can someone  see why im getting error in this query ?
    Hoek Guru
    Currently Being Moderated
    Probably the queries in your GROUP BY.
    Move the queries out of the GROUP BY.
    See:
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8363433072800
    for examples.
  • 4. Re: Can someone  see why im getting error in this query ?
    708631 Newbie
    Currently Being Moderated
    Hmmm. im not sure where to move to. can you give me an example of where I would move them to ?
  • 5. Re: Can someone  see why im getting error in this query ?
    SomeoneElse Guru
    Currently Being Moderated
    Try wrapping an outer query around them like this:
    select col1
          ,col2
          ,inline_query
          ,count(*)
    from
    (
       select col1
             ,col2
             ,(select...) inline_query
       from   ....
    )
    group by col1
            ,col2
            ,inline_query;
  • 6. Re: Can someone  see why im getting error in this query ?
    Hoek Guru
    Currently Being Moderated
    You would move them to the from-clause, just like one, two, three and four.

    Something like:
    untested for obvious reasons
    select *
      from (select i.ips,
                   a.ips,
                   a.question_type,
                   sum(a.score) score,
                   p.project_name,
                   p.project_segment,
                   p.location,
                   p.project_exec_model,
                   p.project_exec_model || ' - ' || p.project_config pmodel,
                   one.score schedule,
                   two.score cost,
                   three.score execution,
                   four.score commercial,
                   nvl(one.score, 0) + nvl(two.score, 0) + nvl(three.score, 0) +
                   nvl(four.score, 0) as total,
                   (select sum(prev_score) prev
                      from xt_risk_past2
                     where ips = i.ips) prev_score,
                   (select max(createdt)
                      from tbl_risk_answer
                     where (ips, sample_num) in
                           (select ips, max(sample_num)
                              from vw_risk_score
                             group by ips)
                       and ips = i.ips) last_dt
              from (select v.project_id,
                           v.ips,
                           v.sample_num,
                           v.question_id,
                           v.header_desc,
                           v.section_area,
                           v.score,
                           decode(bi_recurse(q.active_question, 1, 2),
                                  2,
                                  'OTR',
                                  '-') ||
                           decode(bi_recurse(q.active_question, 1, 1),
                                  1,
                                  'ITO',
                                  '-') question_type
                      from vw_risk_score v
                      left join tbl_risk_question q
                        on v.question_id = q.question_id
                     where (v.project_id, v.sample_num) in
                           (select project_id, max(sample_num) sample_num
                              from vw_risk_score
                             group by project_id)) a,
                   
                   (select distinct ips from vw_risk_score) i,
                   (select ips, sum(score) score
                      from vw_risk_score
                     where section_area = 1
                     group by ips) one,
                   (select ips, sum(score) score
                      from vw_risk_score
                     where section_area = 2
                     group by ips) two,
                   (select ips, sum(score) score
                      from vw_risk_score
                     where section_area = 3
                     group by ips) three,
                   (select ips, sum(score) score
                      from vw_risk_score
                     where section_area = 4
                     group by ips) four,
                   tbl_risk_project p
                   -- moved part I
                   (select ips,
                           sum(prev_score) prev
                      from xt_risk_past2
                     where ips = i.ips) five --or whatever
                   -- moved part II
                  (select ips, 
                     max(createdt) maxcreatedt
                    from tbl_risk_answer
                   where (ips, sample_num) in  (select ips, max(sample_num)
                                                  from vw_risk_score
                                              group by ips)
                   group by ips) six -- or whatever               
                   --
             where i.ips = one.ips(+)
               and i.ips = two.ips(+)
               and i.ips = three.ips(+)
               and i.ips = four.ips(+)
               --
               and i.ips = five.ips -- outerjoin if needed 
               and i.ips = five.ips -- outerjoin if needed
               --
               and ito on scores.ips = ito.ips
               and i.ips = p.ips
               and a.question_type = '-ITO'
             group by i.ips,
                      a.ips,
                      a.question_type,
                      p.project_name,
                      p.project_segment,
                      p.location,
                      p.project_exec_model,
                      p.project_exec_model || ' - ' || p.project_config,
                      one.score,
                      two.score,
                      three.score,
                      four.score,
                      nvl(one.score, 0) + nvl(two.score, 0) +
                      nvl(three.score, 0) + nvl(four.score, 0),
                      five.prev,
                      six.maxcreatedt
           ) scores
    I wonder how all this is going to perform by the way....all those scalar subqueries and outer joins are expensive....

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1594885400346999596

    Read up on Subquery Factoring/WITH-clause, and try to rewrite parts of your query.
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4423923392083
  • 7. Re: Can someone  see why im getting error in this query ?
    708631 Newbie
    Currently Being Moderated
    Thanks Hoek,
    I tried yours, i still get error.

    ORA-00604: error occurred at recursive SQL level 1
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 12
    ORA-00907: missing right parenthesis
    00604. 00000 - "error occurred at recursive SQL level %s"
    *Cause:    An error occurred while processing a recursive SQL statement
    (a statement applying to internal dictionary tables).
    *Action:   If the situation described in the next error on the stack
    can be corrected, do so; otherwise contact Oracle Support.
    Error at Line: 70 Column: 40
  • 8. Re: Can someone  see why im getting error in this query ?
    CharlesHooper Expert
    Currently Being Moderated
    Jay wrote:
    I had 2 queries , instead of using left join i put them together. Now i get error , can someone just take a look to see if syntax wrong somewhere ?
    (snip)
    Reformatting your SQL statement:
    select * from
    (
      select
        i.ips,
        a.ips,
        a.question_type,
        sum(a.score) score,
        p.project_name,
        p.project_segment,p.location,
        p.project_exec_model,
        p.project_exec_model||' - '||p.project_config pmodel,
        one.score schedule,
        two.score cost,
        three.score execution,
        four.score commercial,
        nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0) as total,
        (select
          sum(prev_score) prev
        from
          XT_RISK_PAST2
        where
          ips = i.ips) prev_score,
        (select
          max(createdt)
        from
          tbl_risk_answer
        where
          (ips,sample_num) in
            (select
              ips,max(sample_num)
            from
              VW_RISK_SCORE
            group by
              ips)
          and ips=i.ips) last_dt
      from 
        (select
          v.project_id,
          v.ips,
          v.sample_num,
          v.question_id,
          v.header_desc,
          v.section_area,
          v.score,
          decode(bi_recurse(q.active_question,1,2),2,'OTR','-')||decode(bi_recurse(q.active_question,1,1),1,'ITO','-') question_type
            /* above, might return VARCHAR2 that is too long? */
        from
          VW_RISK_SCORE v left join tbl_risk_question q
            on v.question_id=q.question_id
       where
         (v.project_id,v.sample_num) in
           (select
           project_id,
           max(sample_num) sample_num
         from
           VW_RISK_SCORE
         group by
           project_id)) a,
      (select distinct
        ips
      from
        VW_RISK_SCORE) i,
      (select
        ips, 
        sum(score) score
      from
        VW_RISK_SCORE
      where
        section_area=1
      group by
        ips) one,
      (select
        ips,
        sum(score) score
      from
        VW_RISK_SCORE
      where
        section_area=2
      group by
        ips) two,
      (select
        ips,
        sum(score) score
      from
        VW_RISK_SCORE
      where
        section_area=3
      group by
        ips) three,
      (select
        ips,
        sum(score) score
      from
        VW_RISK_SCORE
      where
        section_area=4
      group by
        ips) four,
      tbl_risk_project p
    where
      i.ips=one.ips(+)
      and i.ips=two.ips(+)
      and i.ips=three.ips(+)
      and i.ips=four.ips(+)
      and scores.ips=ito.ips
      and i.ips=p.ips
      and  a.question_type='-ITO'
    group by
      i.ips,
      a.ips,
      a.question_type,  /* here, might return VARCHAR2 that is too long? */
      p.project_name,
      p.project_segment,
      p.location,
      p.project_exec_model,
      p.project_exec_model||' - '||p.project_config,
      one.score,
      two.score,
      three.score,
      four.score,
      nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0),
      (select
        sum(prev_score) prev
      from
        XT_RISK_PAST2
      where
        ips = i.ips),
      (select
        max(createdt)
      from
        tbl_risk_answer
      where
        (ips,sample_num) in
          (select
            ips,
            max(sample_num)
          from
            VW_RISK_SCORE
          group by
            ips)
        and ips=i.ips)
    ) scores
    I see a couple of possibilities for problems:
    * Combining ANSI outer joins with Oracle specific outer joins in the same SQL statement
    * Combining ANSI joins with scalar subqueries (SELECTs listed in column positions)
    * Grouping on a.question_type, which could be up to 12,000 bytes long based on the definition of BI_RECURSE
    * Including SELECT statements in GROUP BY clauses

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 9. Re: Can someone  see why im getting error in this query ?
    708631 Newbie
    Currently Being Moderated
    Charles hooper, thank you. get an error also.



    ORA-00604: error occurred at recursive SQL level 1
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 12
    ORA-00904: "ITO"."IPS": invalid identifier
    00604. 00000 - "error occurred at recursive SQL level %s"
    *Cause:    An error occurred while processing a recursive SQL statement
    (a statement applying to internal dictionary tables).
    *Action:   If the situation described in the next error on the stack
    can be corrected, do so; otherwise contact Oracle Support.
    Error at Line: 109 Column: 19
  • 10. Re: Can someone  see why im getting error in this query ?
    SomeoneElse Guru
    Currently Being Moderated
    If you really want help with this, you'll need to post CREATE TABLE statements so we can test statements.

    We don't have your tables.
  • 11. Re: Can someone  see why im getting error in this query ?
    CharlesHooper Expert
    Currently Being Moderated
    Jay wrote:
    Charles hooper, thank you. get an error also.

    ORA-00604: error occurred at recursive SQL level 1
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 12
    ORA-00904: "ITO"."IPS": invalid identifier
    00604. 00000 - "error occurred at recursive SQL level %s"
    *Cause:    An error occurred while processing a recursive SQL statement
    (a statement applying to internal dictionary tables).
    *Action:   If the situation described in the next error on the stack
    can be corrected, do so; otherwise contact Oracle Support.
    Error at Line: 109 Column: 19
    Just to clarify, I made no changes to your SQL statement other than to add a couple of comments and add a little whitespace to help see where the potential problem areas might be located.

    In my previous reply I stated the following:
    ----
    I see a couple of possibilities for problems:
    * Combining ANSI outer joins with Oracle specific outer joins in the same SQL statement
    * Combining ANSI joins with scalar subqueries (SELECTs listed in column positions)
    * Grouping on a.question_type, which could be up to 12,000 bytes long based on the definition of BI_RECURSE
    * Including SELECT statements in GROUP BY clauses
    ----

    While it may work, I have never seen a SELECT statement used directly in a GROUP BY clause. Depending on the definition of the BI_RECURSE function, you could be asking Oracle to group on a result that is potentially 12,000 bytes longs (I do not recall the exact number, but I think that the limit is roughly 3500 bytes with an 8KB block size database for older Oracle releases and about twice that for more recent releases). You should avoid mixing ANSI inner and outer joins with Oracle specific inner and outer join syntax in the same SQL statement. Because ANSI joins (except full outer joins after Oracle Datbase 11.1) are transformed into Oracle specific join syntax by the optimizer, there is a risk of hitting a transformation bug. Scalar subqueries might be one of the items that triggers bugs when used in conjunction with ANSI join syntax (but I might be remembering incorrectly).

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 12. Re: Can someone  see why im getting error in this query ?
    MaximDemenko Pro
    Currently Being Moderated
    It may be helpful, if you could explain what the identifier "ITO"."IPS" stands for. I couldn't see any alias in your query with the name "ITO", so it could be packaged function, or function in another schema or something else, what i can't think of at the moment. Your error is raised obviously in the pl sql unit, so you could look up the source at the given line/column if you know, in which pl sql unit exception is raised. If i were you, i would probably trace this statement and look for the cursor in question ( you can easily locat the place in trace, where the error occured and from that position move up in the cursor hierarchy). But first, i would indeed try to resolve "ITO"."IPS" identifier ( if you dont know, what it stands for - you can try to find it using dbms_utility.name_resolve procedure ).

    Best regards

    Maxim

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points