This discussion is archived
1 Reply Latest reply: Oct 4, 2012 9:20 AM by rp0428 RSS

Help with a query.. (multiplication / division)

771751 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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;
    /

Legend

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