Traduccion de Query para HANA

[sergio9]

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,

[Sergio3098]

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;

[sergio9]

hola @Sergio3098 ,

muchas gracias por tu ayuda me funciono perfectamente, ya lo estoy trabajando en crystal report para una mejor visualización al usuario.

[Sergio3098]

Excelente.
No olvides tomar en cuenta los punto y coma “;” ya que SAP Hana Studio es malísimo para decirte en que esta mal tu query, y cuando los omites, SHS no te dice precisamente que te hacen falta.

Saludos.