Traduccion de Query para HANA

Consultas SAP
1

Buena Tarde,

Compañeros, tengo este querry me gustaria poderlo traducir a hana studio la verdad soy nuevo.

declare @fecha AS DATETIME
declare @fecha1 AS DATETIME
declare @tipo AS nVarChar
set @fecha = /* T.DocDate / ‘[%0]’
set @fecha = / T.DocDate / ‘[%1]’
set @tipo = / t1.groupname */ ‘[%3]’
SELECT T0.[CardCode],T0.[CardName],T1.GroupName,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 1 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode]),0)‘Enero’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 2 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode] ),0)‘Febrero’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 3 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode]),0)‘Marzo’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 4 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode]),0)‘Abril’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 5 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode]),0)‘Mayo’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 6 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode]),0)‘Junio’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 7 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode]),0)‘Julio’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 8 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode]),0)‘Agosto’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 9 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode]= T0.[CardCode]),0)‘Septiembre’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 10 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode]),0)‘Octubre’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 11 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode] = T0.[CardCode]),0)‘Noviembre’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND MONTH(T.DocDate) = 12 AND YEAR(T.DocDate)=YEAR(@fecha) AND T.[CardCode]= T0.[CardCode]),0)‘Diciembre’,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.[DocDate] >=[%0] AND T.[DocDate] <=[%1] AND YEAR(@fecha)=2020 AND T.[CardCode] = T0.[CardCode] ),0)‘Acumulado 2020’
FROM OCRD T0 INNER JOIN OINV T ON T0.[CardCode] = T.[CardCode] INNER JOIN ORIN T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN OCRG T1 ON T0.[GroupCode] = T1.[GroupCode]
GROUP BY T0.[CardCode],T0.[CardName], T2.[CardCode],T1.GroupName ORDER BY T0.[CardCode],T0.[CardName]

Gracias
Saludos,

2

Me imagino que quieres hacer un SP no?.

Si ese es el caso, te dejo un ejemplo de como debers estructurar un SP para SAP Hana Studio:

-- Crear el nombre del SP
CREATE PROCEDURE Definir_Nombre_del_SP (

	-- Definir las variables
	IN FechaInicial DATE,
    IN FechaFinal DATE,
    IN Ruta VARCHAR(6)
	
)
LANGUAGE SQLSCRIPT
AS
BEGIN
    -- Declarar una variable para almacenar el resultado
    lt_result = 	
	--Hacer el SELECT
	SELECT
		*
	FROM OINV T0
	-- Condicionar usando las variables
	WHERE T0."DocDate" BETWEEN :FechaInicial AND :FechaFinal
	AND T2."Memo" = :Ruta; 
	-- *Nota: Las variables en Hana se declaran anteponiendo dos puntos (:). No olvidar poner al final punto y coma (;), ya que con eso finalizamos la consulta que guardamos en la variable.  
	
    -- Hacemos un SELECT a la variable que guardo la búsqueda
    SELECT * FROM :lt_result;
END;