12 Replies Latest reply: Jan 21, 2011 1:04 PM by MaximDemenko RSS

    Can someone  see why im getting error in this query ?

    708631
      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
          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
            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
              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
                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
                  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
                    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
                      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 ?
                        Charles Hooper
                        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
                          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
                            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 ?
                              Charles Hooper
                              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
                                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