1 Reply Latest reply: Oct 4, 2012 11:20 AM by rp0428 RSS

    Help with a query.. (multiplication / division)

    771751
      Hi All, i have this data example
      CREATE TABLE "TB_CONVERSION_UNIDAD_TMP"
        ("ID_PRODUCTO"    NUMBER,
          "FLG_OPERADOR"   CHAR(1 BYTE) NOT NULL ENABLE,
          "NUM_FACTOR"     NUMBER(10,6) NOT NULL ENABLE);
          
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values (null,'M','0,0001');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values (null,'D','1');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values (null,'M','1');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values (null,'M','2');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values ('79','M','1');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values ('17','M','100');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values ('16','M','10');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values ('16','M','1');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values ('78','D','48');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values ('18','D','100');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values (null,'M','1');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values (null,'M','11');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values (null,'M','1111');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values ('126','D','1111,19');
      Insert into TB_CONVERSION_UNIDAD_TMP (ID_PRODUCTO,FLG_OPERADOR,NUM_FACTOR) values ('40','D','2');
      /
      COMMIT;
      /
      I want get the num_factor columns where's the condition i send is id_producto then validate if the column FLG_OPERADOR is 'M' or 'D' for the multiplication or division with the another parameter i send (val).

      so far i got this:
      set serveroutput on;
      DECLARE
      intFactor number(8,2);
      intID_Producto NUMBER(10):=126;
      strFlg_Operador CHAR(1);
      Cant number(8,2);
      Val number(8,2):=100;
      BEGIN
      select num_factor into intFactor from TB_CONVERSION_UNIDAD_TMP where id_producto=intid_producto and rownum=1;
      select flg_operador into strFlg_Operador from TB_CONVERSION_UNIDAD_TMP where id_producto=intid_producto and rownum=1;
      if (strFlg_operador='M') then
          Cant:=Val*intFactor;
      else
          Cant:=Val/intFactor;
      end if;
      dbms_output.put_line('Factor :' || to_char(intFactor) || ' Operador ' || strFlg_Operador || ' Total : ' || to_char(Cant));
      END;
      Thanks for the help.
        • 1. Re: Help with a query.. (multiplication / division)
          rp0428
          Wrong forum!

          This is the sql developer forum and is not for sql or pl/sql questions.

          Please mark this question ANSWERED and if the below doesn't answer your question repost the question in the sql and pl/sql forum.
          >
          I want get the num_factor columns where's the condition i send is id_producto then validate if the column FLG_OPERADOR is 'M' or 'D' for the multiplication or division with the another parameter i send (val).
          >
          You've got a good start. Now you just need to BULK COLLECT the data and loop thru it. By the way you don't need two separate queries.
          set serveroutput on;
          DECLARE
          intFactor number(8,2);
          intID_Producto NUMBER(10):=126;
          strFlg_Operador CHAR(1);
          Cant number(8,2);
          Val number(8,2):=100;
          type tbl_factor is table of number(8,2);
          type tbl_flg is table of char(1);
          t_factor tbl_factor;
          t_flg tbl_flg;
          intID1 NUMBER(10):=40;
          BEGIN
          select num_factor into intFactor from TB_CONVERSION_UNIDAD_TMP where id_producto=intid_producto and rownum=1;
          select flg_operador into strFlg_Operador from TB_CONVERSION_UNIDAD_TMP where id_producto=intid_producto and rownum=1;
          if (strFlg_operador='M') then
              Cant:=Val*intFactor;
          else
              Cant:=Val/intFactor;
          end if;
          dbms_output.put_line('Factor :' || to_char(intFactor) || ' Operador ' || strFlg_Operador || ' Total : ' || to_char(Cant));
          select num_factor, flg_operador bulk collect into t_factor, t_flg
           from TB_CONVERSION_UNIDAD_TMP where id_producto=intid1;
           for i in 1..t_factor.count loop
             if (t_flg(i)='M') then
                 Cant:=Val*t_factor(i);
             else
                 Cant:=Val/t_factor(i);
             end if;
            dbms_output.put_line('Factor :' || to_char(t_factor(i)) || 
            ' Operador ' || t_flg(i) || ' Total : ' || to_char(Cant));
           end loop;
          
          
          END;
          /