4 Replies Latest reply: May 4, 2013 6:55 AM by 1007064 RSS

    Invalid Number (Very strange)

    1007064
      Hy guys,

      I hope somebody could help me =/

      I'm getting an error ORA-01722 executing this query above:

      The error occurs in the fields Material and Entrega, the field d.progresso is a number type. I've tried everything than i could, and the error persists. Somebody is seeing anything i couldn't see? Plese help me.

      select SUBSTR(OP.codigo||' - '||OP.descricao ,1,40) OP
      ,OP.status
      ,substr(case when i.substratofornec='Gráfica' then 'Gráfica' else
      cast(round(nvl(avg(case when d.tipo='Req. Material' then d.progresso else 0 end ),0) ) as varchar2(5)) || '%' end,1,8) Material
      ,substr(cast(round( nvl( avg(case when d.tipo='Entrega' then d.progresso else 0 end ) ,0) ) as varchar2(5))|| '%' ,1,8) Entrega
      ,( select min(e.dataentrega) from VORD_ORDEMPROD_ENT E
      where (e.id_ordemprod = OP.id_ordemprod)
      and ((e.qtdentrega-e.qtdrecebida)>0)
      and rownum <=1
      ) ProximaEntrega
      ,op.projeto
      from vord_ordemprod OP
      left join vprd_ordemprod_statusdet D on (d.id_ordemprod = op.id_ordemprod)
      left join ord_ordemprod_infocompl I on I.id_ordemprod = OP.id_ordemprod and i.isdeleted = 0
      where OP.status not in ('Finalizada', 'Cancelada')
      group by OP.id_ordemprod
      ,OP.codigo
      ,OP.descricao
      ,OP.status
      ,i.substratofornec
      ,op.projeto
      order by 5
        • 1. Re: Invalid Number (Very strange)
          1006154
          Hi~

          If the string records exists in the d.progresso column, then ORA-932 error was occured instead of ORA-1722.
          so the reason of ORA-1722 is not d.progresso column but d.tipo (maybe).
          so, change the query using to_char(d.tipo) like below.
          select SUBSTR(OP.codigo||' - '||OP.descricao ,1,40) OP
                ,OP.status
                ,substr(case when i.substratofornec='Grafica' then 'Grafica' else 
                        cast(round( nvl( avg(case when to_char(d.tipo)='Req. Material' then d.progresso else 0 end ) ,0) ) as varchar2(5)) || '%' end,1,8) Material
                ,substr(cast(round( nvl( avg(case when to_char(d.tipo)='Entrega'       then d.progresso else 0 end ) ,0) ) as varchar2(5)) || '%' ,1,8) Entrega
                ,( select min(e.dataentrega) 
                   from VORD_ORDEMPROD_ENT E 
                   where (e.id_ordemprod = OP.id_ordemprod) 
                     and ((e.qtdentrega-e.qtdrecebida)>0) 
                     and rownum <=1 
                 ) ProximaEntrega
                 ,op.projeto
          from vord_ordemprod OP
               left join vprd_ordemprod_statusdet D on (d.id_ordemprod = op.id_ordemprod)
               left join ord_ordemprod_infocompl  I on I.id_ordemprod = OP.id_ordemprod and i.isdeleted = 0
          where OP.status not in ('Finalizada', 'Cancelada')
          group by OP.id_ordemprod
                  ,OP.codigo
                  ,OP.descricao
                  ,OP.status
                  ,i.substratofornec
                  ,op.projeto
          order by 5
          Edited by: seankim on 2013. 5. 3 오후 9:26

          Edited by: seankim on 2013. 5. 3 오후 9:26
          • 2. Re: Invalid Number (Very strange)
            Etbin
            Seems you're missing the second parameter of the <tt>round</tt> function (tahiti gives 503 Service Temporarily Unavailable at the moment) but http://psoug.org/reference/date_func.html#dfrd can be trusted too
            select substr(op.codigo||' - '||op.descricao ,1,40) op,
                   op.status,
                   substr(case when i.substratofornec = 'Gráfica' 
                               then 'Gráfica'
                               else cast(round(nvl(avg(case when d.tipo = 'Req. Material'
                                                            then d.progresso
                                                            else 0
                                                       end
                                                      ),
                                                   0
                                                  ),
                                               0 /* <=== required */
                                              )
                                         as varchar2(5)
                                        ) || '%'
                          end,
                          1,
                          8
                         ) Material,
                   substr(cast(round(nvl(avg(case when d.tipo = 'Entrega'
                                                  then d.progresso
                                                  else 0
                                                  end
                                            ),
                                         0
                                        ),
                                     0 /* <=== required */
                                    )
                               as varchar2(5)
                              ) || '%',
                          1,
                          8
                         ) entrega,
                   (select min(e.dataentrega)
                      from vord_ordemprod_ent e 
                     where (e.id_ordemprod = op.id_ordemprod) 
                       and ((e.qtdentrega-e.qtdrecebida) > 0) 
                       and rownum <= 1 
                   ) proximaentrega,
                   op.projeto
              from vord_ordemprod op
                   left join
                   vprd_ordemprod_statusdet d
                on (d.id_ordemprod = op.id_ordemprod)
                   left join
                   ord_ordemprod_infocompl i
                on (i.id_ordemprod = op.id_ordemprod
               and  i.isdeleted = 0
                   )
             where op.status not in ('Finalizada','Cancelada')
             group by op.id_ordemprod,
                      op.codigo,
                      op.descricao,
                      op.status,
                      i.substratofornec,
                      op.projeto
             order by 5
            Regards

            Etbin

            Edited by: Etbin on 4.5.2013 9:03
            Sorry, forget it the second parameter of round function is (at least for 11g) optional http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions155.htm#SQLRF00698
            • 3. Re: Invalid Number (Very strange)
              1007064
              Thanks for your help but it doesn't works. The problem continues.
              • 4. Re: Invalid Number (Very strange)
                1007064
                Hi! Thanks for your help but it doesn't works.
                The problem continues.

                I've tried the two suggestions.