Ayuda con Query

[Former Member]

Colegas,

Cuando quiero especificarle una condición al query me marca un error de Must specify to select from.

Esto es muy raro porque si no le pongo el T0.[Project]= '[%0]' el query se ejecuta bien.

Por qué podrá ser? Qué es lo que estoy haciendo mal?

La condición es la siguiente:

WHERE T0.[Project]= '[%0]'and T1.[TargetType] = '-1' and T0.[PaidSum] = T0.[DocTotal]

Espero que me puedan.

Saludos!!

[felipe_loyolarodriguez · Accepted solution]

Hola

Bueno, supongo que las tablas deben ser OPCH T0, PCH1 T1 o OINV T0, INV1 T1

Trata colocando las tablas con un dbo. antes del nombre

Ej:

FROM dbo.OINV T0

INNER JOIN dbo.INV1 T1 ON T1.DocEntry = T0.DocEntry

Slds

[Former Member · Accepted solution]

Este es el From del query.

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

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN .[@COMISIONES] T3 ON T1.[U_Comision] = T3.[Code]

INNER JOIN OSLP T4 ON T1.SlpCode = T4.SlpCode

ya lo intenté con lo que me comentaste y me sigue apareciendo el mismo error.

Alguna otra sugerencia?

Saludos!

[felipe_loyolarodriguez · Accepted solution]

Pruebalo asi

FROM OINV T0 
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
INNER JOIN [dbo].[@COMISIONES] T3 ON T1.U_Comision = T3.Code 
INNER JOIN OSLP T4 ON T1.SlpCode = T4.SlpCode

[Former Member · Accepted solution]

Me sigue apareciendo el mismo error.

[felipe_loyolarodriguez · Accepted solution]

Podria postear el query¿?

asi vere mejor el problema

slds

[Former Member · Accepted solution]

Este es el query:

SELECT T0.[Project],

T4.[SlpName] as Vendedor,

T3.[Name] AS 'Concepto',

SUM(T1.[LineTotal]) AS 'Ventas',

(T1.[Quantity]*T2.[LastPurPrc]) as 'Costo',

(SUM(T1.[LineTotal])-(T1.[Quantity]*T2.[LastPurPrc])) as 'Margen',

((SUM(T1.[LineTotal])-(T1.[Quantity]T2.[LastPurPrc]))/(SUM(T1.[LineTotal]))100) as '% Margen',

T3.[U_Minimo] as '% Mínimo', T3.[U_ComBus] as '%Buscado',

(T3.[U_PorComision]*100) AS '%Comision Remedy',

(SUM(T1.[LineTotal])*(T3.[U_ComBus]/100)) as 'Importe Buscado',

(SUM(T1.[LineTotal])-(T1.[Quantity]T2.[LastPurPrc]))-((SUM(T1.[LineTotal])(T3.[U_ComBus]/100))) as 'Excedente',

(((SUM(T1.[LineTotal])-(T1.[Quantity]T2.[LastPurPrc]))/SUM(T1.[LineTotal])100)-T3.[U_ComBus]) as '%Excedente',

(((SUM(T1.[LineTotal])-(T1.[Quantity]T2.[LastPurPrc]))/SUM(T1.[LineTotal])100)-T3.[U_ComBus])/T3.[U_ComBus] as 'Proporción',

'Comision Licencia Buscado Remedy'=(

SELECT CASE WHEN ((SUM(T1.[LineTotal])-(T1.[Quantity]T2.[LastPurPrc]))/(SUM(T1.[LineTotal]))100)>=T3.[U_ComBus] THEN

(SUM(T1.[LineTotal])*T3.[U_PorComision]) END),

'Excedente Comision Remedy'=(

SELECT CASE WHEN ((SUM(T1.[LineTotal])-(T1.[Quantity]T2.[LastPurPrc]))/(SUM(T1.[LineTotal]))100)>=T3.[U_ComBus] THEN

(SUM(T1.[LineTotal])T3.[U_PorComision])(((SUM(T1.[LineTotal])-(T1.[Quantity]T2.[LastPurPrc]))/SUM(T1.[LineTotal])100)-T3.[U_ComBus])/T3.[U_ComBus] END)

FROM dbo.OINV T0 INNER JOIN dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN dbo.OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN .[@COMISIONES] T3 ON T1.[U_Comision] = T3.[Code] INNER JOIN dbo.OSLP T4 ON T1.SlpCode = T4.SlpCode

WHERE T0.[Project]= '[%0]'and T1.[TargetType] = '-1' and T0.[PaidSum] = T0.[DocTotal]

GROUP BY T3.[Name], T1.[Quantity], T2.[LastPurPrc], T3.[U_Minimo], T3.[U_PorComision], T3.[U_ComBus], T4.[SlpName], T0.[Project]

[felipe_loyolarodriguez · Accepted solution]

Pruebala asi

SELECT T0.Project, 
T4.SlpName as Vendedor,
T3.Name AS 'Concepto', 
SUM(T1.LineTotal) AS 'Ventas', 
(T1.Quantity*T2.LastPurPrc) as 'Costo', 
(SUM(T1.LineTotal)-(T1.Quantity*T2.LastPurPrc)) as 'Margen', 
((SUM(T1.LineTotal)-(T1.Quantity*T2.LastPurPrc))/(SUM(T1.LineTotal))*100) as '% Margen', 
T3.U_Minimo as '% Mínimo', T3.U_ComBus as '%Buscado', 
(T3.U_PorComision*100) AS '%Comision Remedy', 
(SUM(T1.LineTotal)*(T3.U_ComBus/100)) as 'Importe Buscado', 
(SUM(T1.LineTotal)-(T1.Quantity*T2.LastPurPrc))-((SUM(T1.LineTotal)*(T3.U_ComBus/100))) as 'Excedente', 
(((SUM(T1.LineTotal)-(T1.Quantity*T2.LastPurPrc))/SUM(T1.LineTotal)*100)-T3.U_ComBus) as '%Excedente', 
(((SUM(T1.LineTotal)-(T1.Quantity*T2.LastPurPrc))/SUM(T1.LineTotal)*100)-T3.U_ComBus)/T3.U_ComBus as 'Proporción', 
CASE 
WHEN ((SUM(T1.LineTotal)-(T1.Quantity*T2.LastPurPrc))/(SUM(T1.LineTotal))*100)>=T3.U_ComBus 
THEN (SUM(T1.LineTotal)*T3.U_PorComision) 
END 'Comision Licencia Buscado Remedy', 
CASE 
WHEN ((SUM(T1.LineTotal)-(T1.Quantity*T2.LastPurPrc))/(SUM(T1.LineTotal))*100)>=T3.U_ComBus 
THEN (SUM(T1.LineTotal)*T3.U_PorComision)*(((SUM(T1.LineTotal)-(T1.Quantity*T2.LastPurPrc))/SUM(T1.LineTotal)*100)-T3.U_ComBus)/T3.U_ComBus 
END 'Excedente Comision Remedy'
FROM dbo.OINV T0 INNER JOIN dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN dbo.OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN [dbo].[@COMISIONES] T3 ON T1.U_Comision = T3.Code INNER JOIN dbo.OSLP T4 ON T1.SlpCode = T4.SlpCode
WHERE T0.Project= '[%0]'and T1.TargetType = '-1' and T0.PaidSum = T0.DocTotal
GROUP BY T3.Name, T1.Quantity, T2.LastPurPrc, T3.U_Minimo, T3.U_PorComision, T3.U_ComBus, T4.SlpName, T0.Project 

Corregi los SELECT CASE, porque si haces un select debes especificar la tabla, en este caso era solo CASE

Slds

Edited by: Floyola on Apr 18, 2011 3:09 PM

[Former Member · Accepted solution]

Ok, ya aparece el criterio de selección de proyecto, pero me dice que hay un error de syntaxis en @comisiones.

[felipe_loyolarodriguez · Accepted solution]

mmm SAP y sus mañas...

Pruebala asi

INNER JOIN dbo.[@COMSIONES] T3 ON T1.U_Comision = T3.Code 

[Former Member · Accepted solution]

Asi es!!! las mañas de SAP porque me sigue diciendo el mismo error =S

[felipe_loyolarodriguez · Accepted solution]

Hola,

Lei en un [post|] que habia que colocar corchetes a todas las tablas...

bueno como ultimo recurso pruebalo asi

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode INNER JOIN [dbo].[@COMISIONES] T3 ON T1.U_Comision = T3.Code INNER JOIN [dbo].[OSLP] T4 ON T1.SlpCode = T4.SlpCode

Ojala funcione...slds