7 Replies Latest reply: Oct 5, 2012 5:45 PM by 771751 RSS

    Help with performing a query (select)

    771751
      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
          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
            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
              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
                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
                  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
                    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
                      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.