This discussion is archived
7 Replies Latest reply: Oct 5, 2012 3:45 PM by 771751 RSS

Help with performing a query (select)

771751 Newbie
Currently Being Moderated
Hi All, i have this table and scripts:
CREATE TABLE TMP_KARDEX
  (
    "ID_PRODUCTO"  NUMBER NOT NULL ENABLE,
    "CTD_INV_INI"  NUMBER(12,6) NOT NULL ENABLE,
    "CTD_ENTRADA"  NUMBER(12,6) NOT NULL ENABLE,
    "CTD_SALIDA"   NUMBER(12,6) NOT NULL ENABLE,
    "CTD_INV_FIN"  NUMBER(12,6) NOT NULL ENABLE,
    "MTO_COST_INI" NUMBER(12,6) NOT NULL ENABLE,
    "MTO_COST_FIN" NUMBER(12,6) NOT NULL ENABLE,
    "FCH_FECHA_HORA" TIMESTAMP (6) NOT NULL ENABLE,
    "NUM_TRANSAC" VARCHAR2(16 BYTE)
  );
  --INSERTING
Insert into TMP_KARDEX (ID_PRODUCTO,CTD_INV_INI,CTD_ENTRADA,CTD_SALIDA,CTD_INV_FIN,MTO_COST_INI,MTO_COST_FIN,FCH_FECHA_HORA,NUM_TRANSAC) values ('126','499,9996','0,0004','0','500','2,767874','2,767874',to_timestamp('09/08/12 00:00:00,000000000','DD/MM/RR HH24:MI:SS,FF'),'2');
Insert into TMP_KARDEX (ID_PRODUCTO,CTD_INV_INI,CTD_ENTRADA,CTD_SALIDA,CTD_INV_FIN,MTO_COST_INI,MTO_COST_FIN,FCH_FECHA_HORA,NUM_TRANSAC) values ('127','497','3','0','500','9,814437','9,814437',to_timestamp('09/08/12 00:00:00,000000000','DD/MM/RR HH24:MI:SS,FF'),'2');
Insert into TMP_KARDEX (ID_PRODUCTO,CTD_INV_INI,CTD_ENTRADA,CTD_SALIDA,CTD_INV_FIN,MTO_COST_INI,MTO_COST_FIN,FCH_FECHA_HORA,NUM_TRANSAC) values ('297','638,323421','0','138,323421','500','32,60314','16,72448',to_timestamp('09/08/12 00:00:00,000000000','DD/MM/RR HH24:MI:SS,FF'),'2');
Insert into TMP_KARDEX (ID_PRODUCTO,CTD_INV_INI,CTD_ENTRADA,CTD_SALIDA,CTD_INV_FIN,MTO_COST_INI,MTO_COST_FIN,FCH_FECHA_HORA,NUM_TRANSAC) values ('307','500','0','1','499','85,87','85,87',to_timestamp('09/08/12 00:00:00,000000000','DD/MM/RR HH24:MI:SS,FF'),'1');
Insert into TMP_KARDEX (ID_PRODUCTO,CTD_INV_INI,CTD_ENTRADA,CTD_SALIDA,CTD_INV_FIN,MTO_COST_INI,MTO_COST_FIN,FCH_FECHA_HORA,NUM_TRANSAC) values ('126','500','0','0,0004','499,9996','2,767874','2,767874',to_timestamp('09/08/12 00:00:00,000000000','DD/MM/RR HH24:MI:SS,FF'),'1');
Insert into TMP_KARDEX (ID_PRODUCTO,CTD_INV_INI,CTD_ENTRADA,CTD_SALIDA,CTD_INV_FIN,MTO_COST_INI,MTO_COST_FIN,FCH_FECHA_HORA,NUM_TRANSAC) values ('127','500','0','3','497','9,814437','9,814437',to_timestamp('09/08/12 00:00:00,000000000','DD/MM/RR HH24:MI:SS,FF'),'1');
Insert into TMP_KARDEX (ID_PRODUCTO,CTD_INV_INI,CTD_ENTRADA,CTD_SALIDA,CTD_INV_FIN,MTO_COST_INI,MTO_COST_FIN,FCH_FECHA_HORA,NUM_TRANSAC) values ('297','500','138,323421','0','638,323421','16,72448','32,60314',to_timestamp('09/08/12 00:00:00,000000000','DD/MM/RR HH24:MI:SS,FF'),'1');
Insert into TMP_KARDEX (ID_PRODUCTO,CTD_INV_INI,CTD_ENTRADA,CTD_SALIDA,CTD_INV_FIN,MTO_COST_INI,MTO_COST_FIN,FCH_FECHA_HORA,NUM_TRANSAC) values ('307','499','1','0','500','85,87','85,87',to_timestamp('09/08/12 00:00:00,000000000','DD/MM/RR HH24:MI:SS,FF'),'2');
commit;
--FUNCTIONS GET FIRST CTD_INI FROM A PRODUCT
SELECT * FROM TMP_KARDEX;
create or replace
function fn_inv_Cant_Ini_Producto
(
intIdProducto number,
fchFechaInicial DATE,
fchFechaFinal DATE
)
return number
is
  datCantidad_Inicial number(8,2);
begin
    select ctd_inv_ini into  datCantidad_Inicial  from (
     select ctd_inv_ini 
      from TMP_KARDEX where id_producto=intIdProducto 
      and FCH_FECHA_HORA between fchFechaInicial and fchFechaFinal
        order by num_transac) 
  where rownum=1;
return (datCantidad_Inicial);
end;
--FUNCTIONS GET LAST CTD_FIN FROM A PRODUCT (SAME THING
create or replace
function fn_inv_Cant_Fin_Producto
(
intIdProducto number,
fchFechaInicial DATE,
fchFechaFinal DATE
)
return number
is
  datCantidad_Final number;
begin
  select ctd_inv_fin into datCantidad_Final from (
  select ctd_inv_fin 
  from TMP_KARDEX where id_producto=intIdProducto 
  and FCH_FECHA_HORA between fchFechaInicial and fchFechaFinal
  order by num_transac asc)
  where rownum=1;
return (datCantidad_Final);
end;
/

--there's my select statement (126 is my id_product hardcoded)
SELECT 
   (fn_inv_Cant_Inicial_Producto(126,SYSDATE-60,SYSDATE)+ SUM(K.CTD_ENTRADA))-(fn_inv_Cant_Final_Producto(126,SYSDATE-60,SYSDATE)-SUM(K.CTD_SALIDA)) AS "Cantidad",
    SUM(K.MTO_COST_FIN) AS "Costo_Extendido",
    ROUND(SUM(K.MTO_COST_FIN) / ((fn_inv_Cant_Inicial_Producto(126,SYSDATE-60,SYSDATE)+ SUM(K.CTD_ENTRADA))-(fn_inv_Cant_Final_Producto(126,SYSDATE-60,SYSDATE)-SUM(K.CTD_SALIDA))),6) AS "C_Prom"
  FROM TMP_KARDEX K 
I just want to know how i can perform my select statement for not running the function multiple times..

Edited by: xDeviates on 05-oct-2012 8:04
  • 1. Re: Help with performing a query (select)
    AlanWms Journeyer
    Currently Being Moderated
    This should probably work, untested:
    with data as (
    select 
        fn_inv_Cant_Final_Producto(126,SYSDATE-60,SYSDATE) final,
        fn_inv_Cant_Inicial_Producto(126,SYSDATE-60,SYSDATE) inicial
    from dual
    )
    SELECT 
       (d.inicial+ SUM(K.CTD_ENTRADA))-(d.final-SUM(K.CTD_SALIDA)) AS "Cantidad",
        SUM(K.MTO_COST_FIN) AS "Costo_Extendido",
        ROUND(SUM(K.MTO_COST_FIN) / ((d.inicial+ SUM(K.CTD_ENTRADA))-(d.final-SUM(K.CTD_SALIDA))),6) AS "C_Prom"
      FROM TMP_KARDEX K,
              DATA D
    There may be typos, and you need to fix the hardcode 126's.

    I also think your functions can be replaced by pure SQL.
  • 2. Re: Help with performing a query (select)
    771751 Newbie
    Currently Being Moderated
    How can i fix the hardcod please?,
    if i change 126 by K.ID_PRODUCTO give me an error: invalid identifier.

    Thanks for you help.
  • 3. Re: Help with performing a query (select)
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    xDeviates wrote:
    Hi All, i have this table and scripts:
    CREATE TABLE TMP_KARDEX
    (
    "ID_PRODUCTO"  NUMBER NOT NULL ENABLE,
    "CTD_INV_INI"  NUMBER(12,6) NOT NULL ENABLE,
    "CTD_ENTRADA"  NUMBER(12,6) NOT NULL ENABLE,
    "CTD_SALIDA"   NUMBER(12,6) NOT NULL ENABLE,
    "CTD_INV_FIN"  NUMBER(12,6) NOT NULL ENABLE,
    "MTO_COST_INI" NUMBER(12,6) NOT NULL ENABLE,
    "MTO_COST_FIN" NUMBER(12,6) NOT NULL ENABLE,
    "FCH_FECHA_HORA" TIMESTAMP (6) NOT NULL ENABLE,
    "NUM_TRANSAC" VARCHAR2(16 BYTE)
    );
    --INSERTING
    Insert into TMP_KARDEX (ID_PRODUCTO,CTD_INV_INI,CTD_ENTRADA,CTD_SALIDA,CTD_INV_FIN,MTO_COST_INI,MTO_COST_FIN,FCH_FECHA_HORA,NUM_TRANSAC) values ('126','499,9996','0,0004','0','500','2,767874','2,767874',to_timestamp('09/08/12 00:00:00,000000000','DD/MM/RR HH24:MI:SS,FF'),'2');
    Thanks for posting the CREATE TABLE and INSERT statements. Remember why you go to all that trouble: so the people who want to help you can re-create the problem and test their ideas. If you post statements that don't work, it's not as helpful.
    None of the INSERT statements you posted work on my system, because you're trying to INSERT VARCHAR2 values such as '2,767874' into NUMBER columns. If you really must use ',' as a decimal separator in INSERT statements, then use TO_NUMBER to convert the strings to NUMBERs.
    create or replace
    function fn_inv_Cant_Ini_Producto
    ...
    create or replace
    function fn_inv_Cant_Fin_Producto
    ...
    Again, please be careful to post code that people can use. The functions called in your query have slightly different names.
    --there's my select statement (126 is my id_product hardcoded)
    SELECT 
    (fn_inv_Cant_Inicial_Producto(126,SYSDATE-60,SYSDATE)+ SUM(K.CTD_ENTRADA))-(fn_inv_Cant_Final_Producto(126,SYSDATE-60,SYSDATE)-SUM(K.CTD_SALIDA)) AS "Cantidad",
    SUM(K.MTO_COST_FIN) AS "Costo_Extendido",
    ROUND(SUM(K.MTO_COST_FIN) / ((fn_inv_Cant_Inicial_Producto(126,SYSDATE-60,SYSDATE)+ SUM(K.CTD_ENTRADA))-(fn_inv_Cant_Final_Producto(126,SYSDATE-60,SYSDATE)-SUM(K.CTD_SALIDA))),6) AS "C_Prom"
    FROM TMP_KARDEX K 
    I just want to know how i can perform my select statement for not running the function multiple times..
    You can call the functions once in a sub-query, and use the values returned as often as you need to in the main query, like this:
    WITH     got_fs         AS
    (
         SELECT     fn_inv_Cant_Inicial_Producto ( 126
                                      , SYSDATE - 60
                                  , SYSDATE
                                  )          AS inicial
         ,     fn_inv_Cant_Final_Producto ( 126
                                      , SYSDATE - 60
                                , SYSDATE
                                )          AS final
         FROM    dual
    )
    SELECT    ( f.inicial
           + SUM (k.ctd_entrada)
           ) - (f.final - SUM (k.ctd_salida))
                              AS "Cantidad"
    ,           SUM (k.mto_cost_fin)      AS "Costo_Extendido"
    ,       ROUND ( SUM (k.mto_cost_fin) 
                / ( f.Inicial
                + SUM (k.ctd_entrada)
                - (f.final - SUM (k.ctd_salida))
                )
              , 6
              )          AS "C_Prom"
    FROM          tmp_kardex k
    CROSS JOIN  got_fs     f
    ;
    You could also get rid of the functions altogether, and simply compute the values once in a sub-query. Calling user-defined functions from SQL statements tends to be slow, but in this case you're only calling each one once, so the difference won't be significant.
  • 4. Re: Help with performing a query (select)
    771751 Newbie
    Currently Being Moderated
    Thanks for the tips,

    But when i change the hardcode value ID_PRODUCTO=126
    WITH     got_fs         AS
    (
         SELECT     fn_inv_Cant_Inicial_Producto ( ID_PRODUCTO
                                      , SYSDATE - 60
                                  , SYSDATE
                                  )          AS inicial
         ,     fn_inv_Cant_Final_Producto ( ID_PRODUCTO
                                      , SYSDATE - 60
                                , SYSDATE
                                )          AS final
         FROM    dual
    )
    SELECT    ( f.inicial
           + SUM (k.ctd_entrada)
           ) - (f.final - SUM (k.ctd_salida))
                              AS "Cantidad"
    ,           SUM (k.mto_cost_fin)      AS "Costo_Extendido"
    ,       ROUND ( SUM (k.mto_cost_fin) 
                / ( f.Inicial
                + SUM (k.ctd_entrada)
                - (f.final - SUM (k.ctd_salida))
                )
              , 6
              )          AS "C_Prom"
    FROM          tmp_kardex k
    CROSS JOIN  got_fs     f
    ;
    give me an error: ID_PRODUCTO invalid identifier.

    Thanks for the help!
  • 5. Re: Help with performing a query (select)
    Frank Kulash Guru
    Currently Being Moderated
    Hi
    xDeviates wrote:
    But when i change the hardcode value ID_PRODUCTO=126 ...
    give me an error: ID_PRODUCTO invalid identifier.
    Why do you want to change the hard-coded value?

    Is the query you posted producing the results you want? If not, post the results you want from the given sample data, and explain how you get them.
  • 6. Re: Help with performing a query (select)
    771751 Newbie
    Currently Being Moderated
    Tha result its OK, but the value 126 is value hard-code from the column ID_PRODUCTO, if i change that for the real column give me an error..

    so far i got this:
    select 
       ROUND((fn_inv_Cant_Inicial_Producto(K.ID_PRODUCTO,SYSDATE-60,SYSDATE)+ SUM(K.CTD_ENTRADA))-(fn_inv_Cant_Final_Producto(K.ID_PRODUCTO,SYSDATE-60,SYSDATE)-SUM(K.CTD_SALIDA)),4) AS "Cantidad",
        SUM(K.MTO_COST_FIN) AS "Costo_Extendido",
        ROUND(SUM(K.MTO_COST_FIN) / ((fn_inv_Cant_Inicial_Producto(K.ID_PRODUCTO,SYSDATE-60,SYSDATE)+ SUM(K.CTD_ENTRADA))-(fn_inv_Cant_Final_Producto(K.ID_PRODUCTO,SYSDATE-60,SYSDATE)-SUM(K.CTD_SALIDA))),6) AS "C_Prom"
      FROM tmp_kardex K 
      GROUP BY K.ID_PRODUCTO;
  • 7. Re: Help with performing a query (select)
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    xDeviates wrote:
    Tha result its OK, but the value 126 is value hard-code from the column ID_PRODUCTO, if i change that for the real column give me an error..
    Sorry, I still don't undersatand what the problem is.
    Do you want to derive the value 126 from the table? Why 126, and not 127 or 307?

    If you want the lowest value of id_producto from the table:
    WITH     got_fs         AS
    (
         SELECT     fn_inv_Cant_Inicial_Producto ( MIN (id_producto)
                                      , SYSDATE - 60
                                  , SYSDATE
                                  )          AS inicial
         ,     fn_inv_Cant_Final_Producto ( MIN (id_producto)
                                      , SYSDATE - 60
                                , SYSDATE
                                )          AS final
         FROM    tmp_kardex
    )
    SELECT    ( MIN (f.inicial)
           + SUM (k.ctd_entrada)
           ) - (MIN (f.final) - SUM (k.ctd_salida))
                              AS "Cantidad"
    ,           SUM (k.mto_cost_fin)      AS "Costo_Extendido"
    ,       ROUND ( SUM (k.mto_cost_fin) 
                / ( MIN (f.Inicial)
                + SUM (k.ctd_entrada)
                - (MIN (f.final) - SUM (k.ctd_salida))
                )
              , 6
              )          AS "C_Prom"
    FROM          tmp_kardex k
    CROSS JOIN  got_fs     f
    ;
    so far i got this:
    select 
    ROUND((fn_inv_Cant_Inicial_Producto(K.ID_PRODUCTO,SYSDATE-60,SYSDATE)+ SUM(K.CTD_ENTRADA))-(fn_inv_Cant_Final_Producto(K.ID_PRODUCTO,SYSDATE-60,SYSDATE)-SUM(K.CTD_SALIDA)),4) AS "Cantidad",
    SUM(K.MTO_COST_FIN) AS "Costo_Extendido",
    ROUND(SUM(K.MTO_COST_FIN) / ((fn_inv_Cant_Inicial_Producto(K.ID_PRODUCTO,SYSDATE-60,SYSDATE)+ SUM(K.CTD_ENTRADA))-(fn_inv_Cant_Final_Producto(K.ID_PRODUCTO,SYSDATE-60,SYSDATE)-SUM(K.CTD_SALIDA))),6) AS "C_Prom"
    FROM tmp_kardex K 
    GROUP BY K.ID_PRODUCTO;
    If you add a GROUP BY clause, you'll risk getting multiplle rows of output. The current results have only 1 row, and you said that was correct.

Legend

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