Consulta del en SAP no me funciona

[former_member1044896]

Tengo la una consulta, la diseñe desde el Visual Studio de SQL, donde con valores fijos en las variables [%0], [%1], [%2] me da los resultados esperados, mientras que si lo corro desde el generador de consultas de SAP y con las variables no me da el siguiente error

"1). [Microsoft][SQL Native Client][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Native Client][SQL Server]Statement 'Alertas recibidas' (OAIB) (s) could not be prepared.."

La consulta es la siguiente:

SELECT

[VEMP].[Date] AS 'Fecha', [VEMP].[DocNum] AS 'Docto', [VEMP].[CardCode] AS 'Cdigo', [VEMP].[CardName] AS 'Nombre Cliente',

ROUND(SUM([VEMP].[LineTotal]),2) AS 'Venta', ROUND(SUM(([VEMP].[Liberalidad1])+([VEMP].[Liberalidad2])),2) AS 'Liberalidad',

ROUND(SUM([VEMP].[CostoArt]),2) AS 'Costo',

ROUND(((SUM([VEMP].[LineTotal])-SUM([VEMP].[Liberalidad1])-SUM([VEMP].[Liberalidad2])-SUM([VEMP].[CostoArt]))/NULLIF(SUM([VEMP].[CostoArt]),0)*100),2) AS 'Ganancia'

FROM

(

SELECT T0.[DocDate] AS 'Date', T0.[DocNum] AS 'DocNum', T0.[CardCode] AS 'CardCode', T0.[CardName] AS 'CardName', T1.[ItemCode] AS 'ItemCode',

T1

.[Dscription] AS 'Dscription', T1.[Quantity] AS 'Quantity', T1.[Price] AS 'Price', T1.[LineTotal] AS 'LineTotal',

T1

.[GrossBuyPr] AS 'CostoU', ISNULL(T1.[U_PorcTecn1],0) AS 'Tecn1', ISNULL(T1.[U_PorcTecn2],0)AS 'Tecn2', T2.[SlpName] AS 'VenName',

(ISNULL(T1.[U_PorcTecn1],0)*(T1.[LineTotal]))/100 AS 'Liberalidad1',

(ISNULL(T1.[U_PorcTecn2],0)*(T1.[LineTotal]))/100 AS 'Liberalidad2', ((T1.[Quantity])*ISNULL(T1.[GrossBuyPr],0)) AS 'CostoArt'

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry -- Facturas

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode -- Empleados

WHERE T2.[SlpName] = '[%0]' AND T0.[DocDate]  BETWEEN '[%1]' AND '[%2]' AND T1.[LineType] = 'R'

AND T1.[ItemCode] NOT IN ('10MEN-VARIOS','10MOM-INST-AC','10VIA-VARIOS','3OMAT','3OMAT-INST-AC','5FLE-SUC','5FLE-VAR','SI-99999')

) AS VEMP

GROUP

BY [VEMP].[Date],[VEMP].[DocNum],[VEMP].[CardCode],[VEMP].[CardName]

UNION

ALL

SELECT

[VEMP].[Date] AS 'Fecha', [VEMP].[DocNum] AS 'Docto', [VEMP].[CardCode] AS 'Cdigo', [VEMP].[CardName] AS 'Nombre Cliente',

ROUND(SUM([VEMP].[LineTotal]),2) AS 'Venta', -(ROUND(SUM(([VEMP].[Liberalidad1])+([VEMP].[Liberalidad2])),2)) AS 'Liberalidad',

ROUND(SUM([VEMP].[CostoArt]),2) AS 'Costo',

-ROUND(((SUM([VEMP].[LineTotal])-SUM([VEMP].[Liberalidad1])-SUM([VEMP].[Liberalidad2])-SUM([VEMP].[CostoArt]))/NULLIF(SUM([VEMP].[CostoArt]),0)*100),2) AS 'Ganancia'

FROM

(

SELECT T0.[DocDate] AS 'Date', T0.[DocNum] AS 'DocNum', T0.[CardCode] AS 'CardCode', T0.[CardName] AS 'CardName', T1.[ItemCode] AS 'ItemCode',

T1

.[Dscription] AS 'Dscription', T1.[Quantity] AS 'Quantity', -(T1.[Price]) AS 'Price', -(T1.[LineTotal]) AS 'LineTotal',

-(T1.[GrossBuyPr]) AS 'CostoU', -(ISNULL(T1.[U_PorcTecn1],0)) AS 'Tecn1', -(ISNULL(T1.[U_PorcTecn2] ,0))AS 'Tecn2', T2.[SlpName] AS 'VenName',

-((ISNULL(T1.[U_PorcTecn1],0)*(T1.[LineTotal]))/100) AS 'Liberalidad1',

-((ISNULL(T1.[U_PorcTecn2],0)*(T1.[LineTotal]))/100) AS 'Liberalidad2', -((T1.[Quantity])*ISNULL(T1.[GrossBuyPr],0)) AS 'CostoArt'

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry -- Facturas

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode -- Empleados

WHERE T2.[SlpName] = '[%0]' AND T0.[DocDate]  BETWEEN '[%1]' AND '[%2]' AND T1.[LineType] = 'R'

AND T1.[ItemCode] NOT IN ('10MEN-VARIOS','10MOM-INST-AC','10VIA-VARIOS','3OMAT','3OMAT-INST-AC','5FLE-SUC','5FLE-VAR','SI-99999')

) AS VEMP

GROUP

BY [VEMP].[Date],[VEMP].[DocNum],[VEMP].[CardCode],[VEMP].[CardName]

Gracias por su apoyo.

Atte.

German Arita

[felipe_loyolarodriguez · Accepted solution]

Buenas

Cuando te suceda eso, antepone un dbo. a las tablas en el FROM y los JOIN

Pruebalo así:

SELECT
           [VEMP].[Date] AS 'Fecha'
          ,[VEMP].[DocNum] AS 'Docto'
          ,[VEMP].[CardCode] AS 'Cdigo'
          , [VEMP].[CardName] AS 'Nombre Cliente'
          ,ROUND(SUM([VEMP].[LineTotal]),2) AS 'Venta', ROUND(SUM(([VEMP].[Liberalidad1])+([VEMP].[Liberalidad2])),2) AS 'Liberalidad'
          ,ROUND(SUM([VEMP].[CostoArt]),2) AS 'Costo'
          ,ROUND(((SUM([VEMP].[LineTotal])-SUM([VEMP].[Liberalidad1])-SUM([VEMP].[Liberalidad2])-SUM([VEMP].[CostoArt]))/NULLIF(SUM([VEMP].[CostoArt]),0)*100),2) AS 'Ganancia'
FROM
( 
SELECT 
           T0.[DocDate] AS 'Date'
          ,T0.[DocNum] AS 'DocNum'
          ,T0.[CardCode] AS 'CardCode'
          ,T0.[CardName] AS 'CardName'
          ,T1.[ItemCode] AS 'ItemCode'
          ,T1.[Dscription] AS 'Dscription'
          ,T1.[Quantity] AS 'Quantity'
          ,T1.[Price] AS 'Price'
          ,T1.[LineTotal] AS 'LineTotal'
          ,T1.[GrossBuyPr] AS 'CostoU'
          ,ISNULL(T1.[U_PorcTecn1],0) AS 'Tecn1'
          ,ISNULL(T1.[U_PorcTecn2],0)AS 'Tecn2'
          ,T2.[SlpName] AS 'VenName'
          ,(ISNULL(T1.[U_PorcTecn1],0)*(T1.[LineTotal]))/100 AS 'Liberalidad1'
          ,(ISNULL(T1.[U_PorcTecn2],0)*(T1.[LineTotal]))/100 AS 'Liberalidad2'
          ,((T1.[Quantity])*ISNULL(T1.[GrossBuyPr],0)) AS 'CostoArt'
FROM [dbo].[OINV] T0
INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry -- Facturas
INNER JOIN [dbo].[OSLP] T2 ON T0.SlpCode = T2.SlpCode -- Empleados
WHERE 
          T2.[SlpName] = '[%0]' 
          AND T0.[DocDate]  BETWEEN '[%1]' AND '[%2]' AND T1.[LineType] = 'R'
          AND T1.[ItemCode] NOT IN ('10MEN-VARIOS','10MOM-INST-AC','10VIA-VARIOS','3OMAT','3OMAT-INST-AC','5FLE-SUC','5FLE-VAR','SI-99999')
) AS VEMP
GROUP
BY [VEMP].[Date],[VEMP].[DocNum],[VEMP].[CardCode],[VEMP].[CardName]
UNION
ALL
SELECT
           [VEMP].[Date] AS 'Fecha'
           ,[VEMP].[DocNum] AS 'Docto'
           ,[VEMP].[CardCode] AS 'Cdigo'
           ,[VEMP].[CardName] AS 'Nombre Cliente'
           ,ROUND(SUM([VEMP].[LineTotal]),2) AS 'Venta'
           ,-(ROUND(SUM(([VEMP].[Liberalidad1])+([VEMP].[Liberalidad2])),2)) AS 'Liberalidad'
           ,ROUND(SUM([VEMP].[CostoArt]),2) AS 'Costo'
           ,-ROUND(((SUM([VEMP].[LineTotal])-SUM([VEMP].[Liberalidad1])-SUM([VEMP].[Liberalidad2])-SUM([VEMP].[CostoArt]))/NULLIF(SUM([VEMP].[CostoArt]),0)*100),2) AS 'Ganancia'
FROM
(
SELECT 
           T0.[DocDate] AS 'Date'
           ,T0.[DocNum] AS 'DocNum'
           ,T0.[CardCode] AS 'CardCode'
           ,T0.[CardName] AS 'CardName'
           ,T1.[ItemCode] AS 'ItemCode'
           ,T1.[Dscription] AS 'Dscription'
           ,T1.[Quantity] AS 'Quantity'
           ,-(T1.[Price]) AS 'Price'
           ,-(T1.[LineTotal]) AS 'LineTotal'
           ,-(T1.[GrossBuyPr]) AS 'CostoU'
           ,-(ISNULL(T1.[U_PorcTecn1],0)) AS 'Tecn1'
           ,-(ISNULL(T1.[U_PorcTecn2] ,0))AS 'Tecn2'
           ,T2.[SlpName] AS 'VenName'
           ,-((ISNULL(T1.[U_PorcTecn1],0)*(T1.[LineTotal]))/100) AS 'Liberalidad1'
           ,-((ISNULL(T1.[U_PorcTecn2],0)*(T1.[LineTotal]))/100) AS 'Liberalidad2'
           ,-((T1.[Quantity])*ISNULL(T1.[GrossBuyPr],0)) AS 'CostoArt'
FROM [dbo].[ORIN] T0
INNER JOIN [dbo].[RIN1] T1 ON T0.DocEntry = T1.DocEntry -- Facturas
INNER JOIN [dbo].[OSLP] T2 ON T0.SlpCode = T2.SlpCode -- Empleados
WHERE 
          T2.[SlpName] = '[%0]' 
          AND T0.[DocDate]  BETWEEN '[%1]' AND '[%2]' AND T1.[LineType] = 'R'
          AND T1.[ItemCode] NOT IN ('10MEN-VARIOS','10MOM-INST-AC','10VIA-VARIOS','3OMAT','3OMAT-INST-AC','5FLE-SUC','5FLE-VAR','SI-99999')
) AS VEMP
GROUP
BY [VEMP].[Date],[VEMP].[DocNum],[VEMP].[CardCode],[VEMP].[CardName]

Saludos

Felipe

[former_member1044896 · Accepted solution]

Gracias Felipe,

Pequeño detalle para un gran saber.

Mi problema ha sido solucionado.

Muchas gracias por su ayuda.

Atte.

German Arita