calcular stock dentro de un select con funciones de usuario

[artu01]

Gente:
Estoy migrando de dbf a sql y hasta ahora voy avanzando pero ahora tengo que mostrar el stock de artículos, mi tabla de artículos esta diseñada
asi:
[img]

[/img]

la tabla detguiat es una tabla transaccional que contiene los movimientos y esta relacionada con la tabla artículos para mostrar el stock:

Code: Select all  Expand view

   select detguiat
   SET RELATION TO detguiat->articu INTO articulo additive

   REDEFINE XBROWSE oBrwDet                                             ;        
      DATASOURCE detguiat                                               ;
      COLUMNS "articu", "Articulo->DesG","Articulo->UM","Articulo->Cap","Cantid"               ;
            ,"salant('articulo')+ingmes('articulo')-salmes('articulo')-cantid" ;
            ,"PRECIO"                                                   ;
      FIELDSIZES 65,195,30,30,40,80,40                 ;
      HEADERS "CODIGO"                                 ;
             ,"DESCRIPCIÓN"                            ;
             ,"U.M."                                   ;
             ,"CAP"                                    ;
             ,"CANT."                                  ;
             ,"STOCK"                                  ;
             ,"PRECIO"
 

y estas son las definiciones de las funciones que invoco en la definición del xbrowse

Code: Select all  Expand view

    FUNCTION INGANO(cAlias, nMes) // devuelve la suma todo los ingresos hasta el mes que le indique en el parametro
      MEMVAR mMes
      LOCAL nSuma := 0, i
      LOCAL cMes
      DEFAULT nMes   := mMes
      nMes -= 1
      FOR i = 0 to nMes
          cMes := 'CONING'+STRZERO(i,2)
          nSuma += (cAlias)->&cMes
      NEXT
    Return(nSuma)
/* --------------------------------------------------------- */
    FUNCTION SALANO(cAlias, nMes) //devuelve la suma de todas las salidas hasta el mes que le indique en el parametro
      MEMVAR mMes
      LOCAL nSuma := 0, i
      LOCAL cMes
      DEFAULT nMes   := mMes
      nMes -= 1
      FOR i = 0 to nMes
          cMes := 'CONSAL'+StrZero(i,2)
          nSuma += (cAlias)->&cMes
      NEXT
    RETURN(nSuma)
/* --------------------------------------------------------- */
    FUNCTION INGMES(cAlias, nMes) //devuelve los ingresos de un mes
      MEMVAR mMes
      LOCAL cMes, nIng
      DEFAULT nMes   := mMes

      cMes := 'CONING'+ STRZERO(nMes,2)
      nIng := (cAlias)->&cMes
    RETURN( nIng )
/* --------------------------------------------------------- */
    FUNCTION SALMES(cAlias, nMes) //devuelve las salidas de un mes
     MEMVAR mMes
     LOCAL cMes, nSal  
     DEFAULT nMes   := mMes

     cMes := 'CONSAL' +  STRZERO(nMes,2)
     nSal := (cAlias)->&cMes
   RETURN( nSal )
/* --------------------------------------------------------- */
    FUNCTION SALANT(cAlias, nMes)  //devuelve las salidas de un mes
      MEMVAR mMes
      LOCAL cMes, nIng, nSal
      DEFAULT nMes   := mMes

      nIng :=INGANO(cAlias,nMes)
      nSal :=SALANO(cAlias,nMes)
    RETURN ( nIng - nSal )
 

La pregunta del TRILLON como hacer esto mismo con sentencias sql? que me recomiendan usar VISTAS? No he usado vistas, tendrán un ejemplo

Code: Select all  Expand view

cSql:="SELECT d.cod, a.descripcion, a.um, a.cap, d.cantid, [FUNCION DE USUARIO QUE CALCULE STOCK], a.precio from #ppru as d left join articulo as a on d.cod=a.cod "
 

[armando.lagunas]

es un ejemplo con mysql, para que te oriente un poco

http://forums.fivetechsupport.com/viewtopic.php?f=6&t=32245

[artu01]

Hola Armando gracias por responder
quiero convertir esta función en fivewin en una función sql

Code: Select all  Expand view

FUNCTION INGANO(cAlias, nMes) // devuelve la suma todo los ingresos hasta el mes que le indique en el parametro
      MEMVAR mMes
      LOCAL nSuma := 0, i
      LOCAL cMes
      DEFAULT nMes   := mMes
      nMes -= 1
      FOR i = 0 to nMes
          cMes := 'CONING'+STRZERO(i,2)
          nSuma += (cAlias)->&cMes
      NEXT
    Return(nSuma)
 

Lo primero que se me vino a la cabeza fue VISTA pero en realidad lo que necesito es FUNCIONES en sql
estoy intentando asi y el compilador mssql me marca mensaje de error

Code: Select all  Expand view

CREATE FUNCTION IngAno(@nMes int, @cCod varchar(7))
RETURNS int
AS
BEGIN
    DECLARE @nsuma int;
    set @nsuma = CASE @nMes
    When 1 then select coning00 as nsuming from ARTICULO where numero = @cCod
    When 2 then select (coning00+coning01) as nsuming from ARTICULO where numero = @cCod
    When 3 then select (coning00+coning01+coning02) as nsuming from ARTICULO where numero = @cCod
    RETURN @nsuma)
END
GO
 

tampoco me funciona asi:

Code: Select all  Expand view

CREATE FUNCTION IngAno(@nMes int, @cCod varchar(7))
RETURNS int
AS
BEGIN
    DECLARE @nsuma int;
    RETURN (
    CASE @nMes
    When 1 then SET @nsuma = select TOTBRUTO from CABGUIA where numero = @cCod
    When 2 then SET @nsuma = select (TOTBRUTO+TOTBRUTO) from CABGUIA where numero = @cCod
    When 3 then SET @nsuma = select (TOTBRUTO+TOTBRUTO+TOTBRUTO) from CABGUIA where numero = @cCod
    RETURN @nsuma

END
GO
 

Espero que me puedan dar una mano los gurus en sql, gracias

[jacgsoft]

¿Tus tablas estan migradas a Sql-server?

[artu01]

Así es

[armando.lagunas]

deja que el mismo motor sql te realice el trabajo, en vez de función ocupa una Procedure y que el dato devuelto lo tratas como un select común y corriente.

ejemplo personal para que te oriente un poco

Code: Select all  Expand view

USE [VIKING_SYSTEM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CGE_StockResumen_Fruta]
  @Fruta varchar(5)

AS
BEGIN

SELECT  P.PT_Fruta         AS Codigo,
      SUM(C.ET_PesoCaja)   AS Kilos
           
FROM  CGE_PalletTerminados AS P

      INNER JOIN CGE_CajasTerminadas AS C
                           ON  C.ET_NroPallet=P.PT_Numero_PK
                          AND C.ET_Reproceso=0

WHERE     P.PT_Estado NOT IN ('D','B','R','a')
      AND P.PT_Hold = 0  
      AND P.PT_Fruta = @Fruta

GROUP BY
      P.PT_Fruta  
;
END

 

me suma los kilos asignados a un Numero de Pallet de una fruta especifica que la paso como parámetro "@Fruta"

Code: Select all  Expand view

oDbf := oConnect:Query("EXECUTE CGE_StockResumen_Fruta '"+SubStr(cFruta,1,5)+"' ")
 

oDbf recibe los 2 datos resultantes de la ejecución del procedimiento, en mi caso el código de la fruta y la suma total de sus kilos

[artu01]

Gracias armando lo resolvi con función
tengo una duda existe macro sustitucion en sql , como era en vfp coningXX por los valores 01,02,03, 04,05, 06 ...12, que son los meses del año

Code: Select all  Expand view

CREATE FUNCTION CalcIngAno
(
    @Codigo int, @nMes int
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @IngA int
 
    -- Restar las fechas para ebtener las edad
    SELECT
        @IngA =
      CASE
      When @nMes = 1 then (coning00)
      When @nMes = 2 Then (coning00+coning01)
      When @nMes = 3 tHEN (coning00+(coning01+coning02)
      When @nMes = 4 tHEN (coning00+(coning01+coning02+coning03)
      When @nMes = 5 tHEN (coning00+coning01+coning02+coning03+coning04)
      When @nMes = 6 tHEN (coning00+coning01+coning02+coning03+coning04+coning05)
      When @nMes = 7 tHEN (coning00+coning01+coning02+coning03+coning04+coning05+coning06)
      When @nMes = 8 tHEN (coning00+coning01+coning02+coning03+coning04+coning05+coning06+coning07)
      When @nMes = 9 tHEN (coning00+coning01+coning02+coning03+coning04+coning05+coning06+coning07+coning08)
      When @nMes = 10 tHEN (coning00+coning01+coning02+coning03+coning04+coning05+coning06+coning07+coning08+coning09)
      When @nMes = 11 tHEN (coning00+coning01+coning02+coning03+coning04+coning05+coning06+coning07+coning08+coning09+coning10)
      else (coning00+coning01+coning02+coning03+coning04+coning05+coning06+coning07+coning08+coning09+coning10+coning11)
      end

    FROM
        Articulo P
    WHERE
        P.Codigo = @Codigo;
 
    -- Retornar el valor escalar
    RETURN @IngA
 
END
GO