Convertir query de Sql a Hana

ogallego · 14 Marzo, 2022 17:23

Buenas tardes,

Les escribo ya que requiero ayuda con el siguiente query, pues estamos migrando de Sql a Hana y en el momento en que me encontré este query para migrarlo no me funciona, según entiendo HANA no permite crear tablas temporales, la verdad aquí si quede 100% perdido, alguien podría ayudarme con la solución de como podría migrar este query de SQL a HANA, de antemano agradezco la colaboración de cada uno de ustedes, aquí dejo el query

/*SELECT FROM [dbo].[OWHS] TT1*/
     declare  @BodegaI as varchar (8) 
     /* WHERE */
     set @BodegaI =  /* TT1.WhsCode */'[%0]'  
 
/*SELECT FROM [dbo].[OWHS] TT1*/
     declare  @BodegaF as varchar (8) 
     /* WHERE */
     set @BodegaF =  /* TT1.WhsCode */'[%1]'  

/*SELECT FROM [dbo].[OITB] TT2*/
     declare  @GrupoI as varchar (20) 
     /* WHERE */
     set @GrupoI =  /* TT2.[ItmsGrpNam] */'[%2]'  

/*SELECT FROM [dbo].[OITB] TT2*/
     declare  @GrupoF as varchar (20) 
     /* WHERE */
     set @GrupoF =  /* TT2.[ItmsGrpNam] */'[%3]' 


CREATE TABLE [dbo].[#Template_Entrada_Detalle1](
        [Num_line] [int] IDENTITY (1,1) NOT NULL,
        [DocNum] [int] NOT NULL,
        [LineNum] [int] NOT NULL,
        [ItemCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [Dscription] [nvarchar] (100) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [Quantity] [numeric](19, 6) NOT NULL,
        [Price] [numeric](19, 6) NOT NULL,
        [Currency] [nvarchar] (3) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [WhsCode] [nvarchar] (8) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [AcctCode] [nvarchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [OcrCode] [nvarchar] (8) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [TaxStatus] [nvarchar] (3) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [DeferrTax] [nvarchar] (3) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [LineTotal] [numeric](19, 6) NOT NULL
) 


CREATE TABLE [dbo].[#Template_Entrada_Detalle2](
        [DocNum] [int] NOT NULL,
        [LineNum] [int] NOT NULL,
        [ItemCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [Dscription] [nvarchar] (100) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [Quantity] [numeric](19, 6) NOT NULL,
        [Price] [numeric](19, 6) NOT NULL,
        [Currency] [nvarchar] (3) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [WhsCode] [nvarchar] (8) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [AcctCode] [nvarchar] (15) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [OcrCode] [nvarchar] (8) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [TaxStatus] [nvarchar] (3) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [DeferrTax] [nvarchar] (3) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
        [LineTotal] [numeric](19, 6) NOT NULL
) 


insert into #Template_Entrada_Detalle1

SELECT 1 'DocNum', 0 'LineNum', T0.[Articulo] 'ItemCode', T0.Descripcion 'Dscription', SUM(T0.Diferencia_Cantidad) 'Quantity', 
       SUM(T0.Diferencia_Costo) 'Price', 
       '$' 'Currency', T0.Bodega 'WhsCode',   '73959503' 'AcctCode', '601099' 'OcrCode',
       'tNO' 'TaxStatus', 'tNO' 'DeferrTax',  SUM(T0.Diferencia_Costo) 'LineTotal'

FROM

(


SELECT T0.Bodega, T0.Articulo, T0.Descripcion, T0.Lote, T0.Costo_Unitario, T0.Diferencia_Cantidad, T0.Diferencia_Costo

FROM

-- Tomado del Informe de Diferencias---

(

SELECT T0.Bodega 'Bodega', T0.Articulo 'Articulo', T0.Descripcion 'Descripcion', ISNULL(T0.Lote,'') 'Lote', 
       T2.AvgPrice 'Costo_Unitario', 
       SUM(T0.Existencia) 'Existencia_Cantidad', SUM(T0.Existencia * T2.AvgPrice) 'Existencia_Costo',
       SUM(T0.Fisico) 'Físico_Cantidad', SUM(T0.Fisico * T2.AvgPrice) 'Físico_Costo',
       SUM(T0.Fisico) - SUM(T0.Existencia) 'Diferencia_Cantidad', (SUM(T0.Fisico) - SUM(T0.Existencia)) * T2.AvgPrice 'Diferencia_Costo'

FROM

(

SELECT T0.[WhsCode] 'Bodega', T0.[ItemCode] 'Articulo', T2.[ItemName] 'Descripcion', ISNULL(T1.[BatchNum],'') 'Lote', 
       CASE 
         WHEN ISNULL(T1.[BatchNum],'') = '' THEN T0.OnHand
         ELSE T1.Quantity
       END 'Existencia', 0 'Fisico' 
FROM [dbo].[OITW] T0 LEFT JOIN [dbo].[OIBT]  T1 ON T1.WhsCode = T0.WhsCode AND T1.ItemCode = T0.ItemCode 
                     INNER JOIN [dbo].[OITM]  T2 ON T0.ItemCode = T2.ItemCode
                     INNER JOIN OITB T3 ON T3.ItmsGrpCod = T2.ItmsGrpCod
WHERE ( T0.[WhsCode]  >= @BodegaI AND T0.WhsCode <= @BodegaF ) AND
      ( T3.ItmsGrpNam >= @GrupoI AND T3.ItmsGrpNam <= @GrupoF ) 


UNION ALL



SELECT T0.[U_CodAlm] 'Bodega', T0.[U_CodArt] 'Articulo', T1.ItemName 'Descripcion', ISNULL(T0.[U_NroLote],'') 'Lote', 
       0 'Existencia', SUM(T0.[U_Cantidad]) 'Fisico' 
FROM [dbo].[@HBT_FISICO]  T0 LEFT JOIN OITM T1 ON T1.ItemCode = T0.U_CodArt 
                             INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod 
WHERE ( T0.[U_CodAlm]  >= @BodegaI AND T0.[U_CodAlm] <= @BodegaF ) AND
      ( T2.ItmsGrpNam >= @GrupoI AND T2.ItmsGrpNam <= @GrupoF ) 
GROUP BY T0.[U_CodAlm], T0.[U_CodArt], T1.ItemName, ISNULL(T0.[U_NroLote],'') 

 
) T0

INNER JOIN OITW T2 ON T2.WhsCode = T0.Bodega AND T2.ItemCode = T0.Articulo
GROUP BY T0.Bodega, T0.Articulo, T0.Descripcion, ISNULL(T0.Lote,''), T2.AvgPrice

HAVING (SUM(T0.Existencia) <> 0) OR (ISNULL(SUM(T0.Fisico),0) <> 0) 

) T0

-- Fin Informe de Diferencias-----------------------------------------------------------------------------------------------------------------------

WHERE T0.Diferencia_Cantidad > 0

) T0

GROUP BY T0.Articulo, T0.Descripcion, T0.Bodega

insert into #Template_Entrada_Detalle2
SELECT T0.DocNum 'DocNum', T0.Num_Line - 1 'LineNum', T0.ItemCode 'ItemCode', T0.Dscription 'Dscription', T0.Quantity 'Quantity',
       T0.Price 'Price', 
       T0.Currency 'Currency', 
       T0.WhsCode 'WhsCode',  T0.AcctCode 'AcctCode', T0.OcrCode 'OcrCode', T0.TaxStatus 'TaxStatus', T0.DeferrTax 'DeferrTax', T0.LineTotal 'LineTotal'
FROM #Template_Entrada_Detalle1 T0
ORDER BY T0.[WhsCode], T0.[ItemCode]

SELECT T0.DocNum 'ParentKey', CONVERT(VARCHAR(10),T0.LineNum) 'LineNum', T0.ItemCode, T0.Dscription 'ItemDescription', T0.Quantity 'Quantity', T0.Price, T0.Currency, 
       T0.WhsCode 'WarehouseCode', T0.AcctCode 'AccountCode', T0.OcrCode 'CostingCode', 
       T0.TaxStatus 'TaxLiable', T0.DeferrTax 'DeferredTax', T0.LineTotal 'LineTotal'
FROM #Template_Entrada_Detalle2 T0
ORDER BY T0.DocNum, T0.[ItemCode]

--ORDER BY T0.DocNum, T0.LineNum

Este es el fin del query

andresramirez · 15 Marzo, 2022 03:58

Hola @ogallego
Las tablas temporales si funcionan en HANA. Lo que funciona diferente es el manejo de parametros, donde sql server si funciona, pero no en HANA.

/ *SELECT FROM [dbo].[OWHS] TT1* /
declare @BodegaI as varchar (8)
/* WHERE <em>/
set @BodegaI = /</em> TT1.WhsCode */’[%0]’

Esto tampoco te va funcionar en HANA. COLLATE SQL_Latin1_General_CP850_CI_AS

ogallego · 15 Marzo, 2022 12:51

Mil gracias Andres por tu respuesta y cuéntame con respecto al query que me tocaría cambiar? Sólo el tema de los parámetros y ya? Pues yo en otros querys lo que hago es no definir la variable, sino pedir el parámetro directamente utilizando los ‘[%0]’, ‘[%1]’, Etc.

Agradezco tu apoyo en esto.

ogallego · 15 Marzo, 2022 15:47

He hecho el cambio, quitando lo que no permitia HANA y lo probe de esta forma pero continua sin funcionar, me sale error de que la tabla no existe en el esquema, el query quedo asi:

CREATE COLUMN TABLE "#Template_Entrada_Detalle1" 
(
	"Num_line" integer  NOT NULL, 
	"DocNum" integer NOT NULL, 
	"LineNum" integer NOT NULL, 
	"ItemCode" nvarchar(20) NOT NULL, 
	"Dscription" nvarchar(100) NOT NULL, 
	"Quantity" decimal(19, 6) NOT NULL, 
	"Price" decimal(19, 6) NOT NULL, 
	"Currency" nvarchar(3) NOT NULL, 
	"WhsCode" nvarchar(8) NOT NULL, 
	"AcctCode" nvarchar(15) NOT NULL, 
	"OcrCode" nvarchar(8) NOT NULL, 
	"TaxStatus" nvarchar(3) NOT NULL, 
	"DeferrTax" nvarchar(3) NOT NULL, 
	"LineTotal" decimal(19, 6) NOT NULL
);

CREATE COLUMN TABLE "#Template_Entrada_Detalle2" 
(
	"DocNum" integer NOT NULL, 
	"LineNum" integer NOT NULL, 
	"ItemCode" nvarchar(20) NOT NULL, 
	"Dscription" nvarchar(100) NOT NULL, 
	"Quantity" decimal(19, 6) NOT NULL, 
	"Price" decimal(19, 6) NOT NULL, 
	"Currency" nvarchar(3) NOT NULL, 
	"WhsCode" nvarchar(8) NOT NULL, 
	"AcctCode" nvarchar(15) NOT NULL, 
	"OcrCode" nvarchar(8) NOT NULL, 
	"TaxStatus" nvarchar(3) NOT NULL, 
	"DeferrTax" nvarchar(3) NOT NULL, 
	"LineTotal" decimal(19, 6) NOT NULL
);

INSERT INTO "#Template_Entrada_Detalle1" 
(
	SELECT 1 AS "DocNum", 0 AS "LineNum", T0."Articulo" AS "ItemCode", T0."Descripcion" AS "Dscription", SUM(T0."Diferencia_Cantidad") AS "Quantity", 
	SUM(T0."Diferencia_Costo") AS "Price", '$' AS "Currency", T0."Bodega" AS "WhsCode", '73959503' AS "AcctCode", '601099' AS "OcrCode", 
	'tNO' AS "TaxStatus", 'tNO' AS "DeferrTax", SUM(T0."Diferencia_Costo") AS "LineTotal" 
	FROM 
	(
		SELECT T0."Bodega", T0."Articulo", T0."Descripcion", T0."Lote", T0."Costo_Unitario", T0."Diferencia_Cantidad", T0."Diferencia_Costo" 
		FROM 
		(
			SELECT T0."Bodega" AS "Bodega", T0."Articulo" AS "Articulo", T0."Descripcion" AS "Descripcion", IFNULL(T0."Lote", '') AS "Lote", 
				   T2."AvgPrice" AS "Costo_Unitario", SUM(T0."Existencia") AS "Existencia_Cantidad", 
				   SUM(T0."Existencia" * T2."AvgPrice") AS "Existencia_Costo", SUM(T0."Fisico") AS "Físico_Cantidad", 
				   SUM(T0."Fisico" * T2."AvgPrice") AS "Físico_Costo", SUM(T0."Fisico") - SUM(T0."Existencia") AS "Diferencia_Cantidad", 
				   (SUM(T0."Fisico") - SUM(T0."Existencia")) * T2."AvgPrice" AS "Diferencia_Costo" 
			FROM 
			(
				SELECT T0."WhsCode" AS "Bodega", T0."ItemCode" AS "Articulo", T2."ItemName" AS "Descripcion", IFNULL(T1."BatchNum", '') AS "Lote", 
  					   CASE WHEN IFNULL(T1."BatchNum", '') = '' THEN T0."OnHand" 
					   ELSE T1."Quantity" 
					   END AS "Existencia", 0 AS "Fisico" 
				FROM "OITW" T0 
				LEFT OUTER JOIN "OIBT" T1 ON T1."WhsCode" = T0."WhsCode" AND T1."ItemCode" = T0."ItemCode" 
				INNER JOIN "OITM" T2 ON T0."ItemCode" = T2."ItemCode" 
				INNER JOIN OITB T3 ON T3."ItmsGrpCod" = T2."ItmsGrpCod" 
				WHERE (T0."WhsCode" >= '[%0]' AND T0."WhsCode" <= '[%1]') AND (T3."ItmsGrpNam" >= '[%2]' AND T3."ItmsGrpNam" <= '[%3]') 
				UNION ALL 
				SELECT T0."U_CodAlm" AS "Bodega", T0."U_CodArt" AS "Articulo", T1."ItemName" AS "Descripcion", IFNULL(T0."U_NroLote", '') AS "Lote", 
				       0 AS "Existencia", SUM(T0."U_Cantidad") AS "Fisico" 
				FROM "@HBT_FISICO" T0 
				LEFT OUTER JOIN OITM T1 ON T1."ItemCode" = T0."U_CodArt" 
				INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod" 
				WHERE (T0."U_CodAlm" >= '[%0]' AND T0."U_CodAlm" <= '[%1]') AND (T2."ItmsGrpNam" >= '[%2]' AND T2."ItmsGrpNam" <= '[%3]') 
				GROUP BY T0."U_CodAlm", T0."U_CodArt", T1."ItemName", IFNULL(T0."U_NroLote", '')
			) AS T0 
			INNER JOIN OITW T2 ON T2."WhsCode" = T0."Bodega" AND T2."ItemCode" = T0."Articulo" 
			GROUP BY T0."Bodega", T0."Articulo", T0."Descripcion", IFNULL(T0."Lote", ''), 
			         T2."AvgPrice" HAVING (SUM(T0."Existencia") <> 0) OR (IFNULL(SUM(T0."Fisico"), 0) <> 0)
		) AS T0 
		WHERE T0."Diferencia_Cantidad" > 0
	) AS T0 
	GROUP BY T0."Articulo", T0."Descripcion", T0."Bodega"
);
INSERT INTO "#Template_Entrada_Detalle2" 
(
	SELECT T0."DocNum" AS "DocNum", T0."Num_Line" - 1 AS "LineNum", T0."ItemCode" AS "ItemCode", T0."Dscription" AS "Dscription", 
	       T0."Quantity" AS "Quantity", T0."Price" AS "Price", T0."Currency" AS "Currency", T0."WhsCode" AS "WhsCode", T0."AcctCode" AS "AcctCode", 
		   T0."OcrCode" AS "OcrCode", T0."TaxStatus" AS "TaxStatus", T0."DeferrTax" AS "DeferrTax", T0."LineTotal" AS "LineTotal" 
	FROM "#Template_Entrada_Detalle1" T0 
	ORDER BY T0."WhsCode", T0."ItemCode"
);
SELECT T0."DocNum" AS "ParentKey", CAST(T0."LineNum" AS varchar(10)) AS "LineNum", T0."ItemCode", T0."Dscription" AS "ItemDescription", 
       T0."Quantity" AS "Quantity", T0."Price", T0."Currency", T0."WhsCode" AS "WarehouseCode", T0."AcctCode" AS "AccountCode", 
	   T0."OcrCode" AS "CostingCode", T0."TaxStatus" AS "TaxLiable", T0."DeferrTax" AS "DeferredTax", T0."LineTotal" AS "LineTotal" 
FROM "#Template_Entrada_Detalle2" T0 
ORDER BY T0."DocNum", T0."ItemCode";

--ORDER BY T0.DocNum, T0.LineNum

ogallego · 16 Marzo, 2022 12:41

Nadie me puede colaborar con este problema?

ogallego · 18 Marzo, 2022 17:29

Por favor quien me puede ayudar?

jamh1976 · 19 Marzo, 2022 18:55

Buen día @ogallego , encontré esto en la red, espero te pueda orientar un poco en cuanto a opciones, saludos.

SQL HANA Converter es una herramienta de BoyumIT Solutions.

Para la solución SAP, consulte las entradas de blog sobre la herramienta SAP Advanced SQL Migration.

ht_ps://blogs.sap.com/2020/08/06/set-your-databases-free-with-sap-advanced-sql-migration-2/

andresramirez · 19 Mayo, 2022 23:40

Hola @ogallego
Prueba cargando el parametro de la siguiente manera
(SELECT MAX(T90."RateDate") FROM ORTT T90 WHERE T90."RateDate" ='[%0]')
Debes buscar una tabla de maestros que contenga el dato que necesitas.

Este seria un ejemplo

SELECT T0."DocNum", T0."ItemCode", T0."PostDate"
FROM OWOR T0  
INNER JOIN WOR1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0."Status" = 'L' AND T1."ItemType" = '4'
AND T0."PostDate" >=(SELECT MAX(T90."RateDate") FROM ORTT T90 WHERE T90."RateDate" ='[%0]')
AND T0."PostDate" <=(SELECT MAX(T90."RateDate") FROM ORTT T90 WHERE T90."RateDate" ='[%1]')

Andres Ramirez Jaramillo

system · 7 Abril, 2023 07:55

Este tema se cerró automáticamente 30 días después de la última publicación. No se permiten nuevas respuestas.