MYSQL can handle a GREATEST() function which SQL does not have, and I try to change the following line of code:
SELECT GREATEST(Efectivo,Cheques,Depositos,Tarjetas,Vales)
INTO M FROM movcaja WHERE sys_pk=MovCaja;
He managed to change it to this:
SELECT @M = (SELECT MAX(M) FROM (VALUES (Efectivo),(Cheques),(Depositos),(Tarjetas),(Vales)) AS T(M)) ,
FROM movcaja WHERE sys_pk= @MovCaja;
Marking the error of:
## Msg 156, Level 15, State 1, Procedure GetMetodoPago, Line 18
## Incorrect syntax near the keyword 'FROM'.
All to solve the following function:
CREATE FUNCTION GetMetodoPago() RETURNS varchar(50)
BEGIN
DECLARE @E decimal(18,8);
DECLARE @C decimal(18,8);
DECLARE @D decimal(18,8);
DECLARE @T decimal(18,8);
DECLARE @V decimal(18,8);
DECLARE @M decimal(18,8);
DECLARE @R VARCHAR(50);
DECLARE @MovCaja INTEGER;
DECLARE @PkVta INT;
SELECT @MovCaja = COALESCE(imovcaja,0) FROM venta WHERE sys_pk= @PkVta;
if (@MovCaja=0)
BEGIN
SELECT TOP 1 @MovCaja = COALESCE(imovcaja,0) FROM venta WHERE AplicadoA= @PkVta;
END;
SELECT @M = (SELECT MAX(M) FROM (VALUES (Efectivo),(Cheques),(Depositos),(Tarjetas),(Vales)) AS T(M)) , FROM movcaja WHERE sys_pk= @MovCaja;
SELECT @E = Efectivo, @C = Cheques, @D = Depositos, @T = Tarjetas, @V = Vales FROM movcaja WHERE sys_pk= @MovCaja;
IF @M=@E BEGIN SET @R = '01';END;
IF @M=@C BEGIN SET @R = '02';END;
IF @M=@D BEGIN SET @R = '03';END;
IF @M=@T BEGIN SET @R = '04';END;
IF @M=@V BEGIN SET @R = '08';END;
ELSE SET @R = '99';
RETURN @R;
END;