on 12-11-2015 10:58 PM
Buenas tardes compañeros estoy teniendo algunos problemas con la siguiente consulta.
SELECT DISTINCT T.ItemCode, T.ItemName,
IsNull((SELECT Sum(B.CountQty)
FROM OINC A inner join INC1 B on A.DocEntry = B.DocEntry
WHERE B.ItemCode=T.ItemCode AND A.CountDate >= '[%2]' and B.WHSCODE = 'MP'),0) as 'Cierre de Inventario',
IsNull((SELECT Sum(B.Quantity)
FROM PDN1 B INNER JOIN OPDN A ON B.DocEntry = A.DocEntry
WHERE B.ItemCode=T.ItemCode AND A.DocDate >= '[%0]' AND A.DocDate<='[%1]' and A.Canceled='N'),0) as 'Entradas de Mescancia',
IsNull((SELECT Sum(B.Quantity)
FROM RPD1 B INNER JOIN ORPD A ON B.DocEntry = A.DocEntry
WHERE B.ItemCode=T.ItemCode AND A.DocDate >= '[%0]' AND A.DocDate<='[%1]' and A.Canceled='N'),0) as'Devoluciones',
IsNull((SELECT SUM(B.Quantity)
FROM OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry
WHERE B.ItemCode=T.ItemCode AND A.DocDate >= '[%0]' AND A.DocDate<='[%1]' and A.Canceled='N'),0)as 'Cantidad solicitada',
ISNULL((SELECT SUM(Y.Quantity)FROM OWTR X INNER JOIN WTR1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >='[%0]' AND X.DocDate<='[%1]' AND Y.FromWhsCod = 'MP' AND Y.WhsCode = 'WIP'),0)as 'Cantidad Entregada',
ISNULL((SELECT SUM(Y.Quantity) FROM OIGE X INNER JOIN IGE1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >='[%0]' AND X.DocDate<='[%1]' and Y.WhsCode = 'WIP' AND Y.AcctCode = '1200.2.1' AND X.Comments LIKE '%Nº-OT%'),0) as'Cantidad Consumida'
FROM OITM T
where T.SellItem = 'N' and T.PrchSeItem = 'Y'
GROUP BY T.ItemCode, T.ItemName
ORDER BY T.ItemCode
al correrla en sap me marca el siguiente error
must specify table to select from. 'alertas recibidas'
yo al mirar la consulta no encuentro ningún error.
la corro en mi sql y esta correcto, y la corro individual mente tabla por tabla y de igual manera no me manda ningún error y me devuelve los datos correctos.
el error solo me lo manda cuando intento correr toda la consulta junta.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
buenos dias carlos oye estoy poniendo el [dbo]. entre los from y los inner pero ahora no me manda ni el error ni los datos =/
SELECT DISTINCT T.ItemCode, T.ItemName,
IsNull((SELECT Sum(B.CountQty)
FROM [dbo].OINC A INNER JOIN [dbo].INC1 B on A.DocEntry = B.DocEntry
WHERE B.ItemCode=T.ItemCode AND A.CountDate >= '[%2]' and B.WHSCODE = 'MP'),0) as 'Cierre de Inventario',
IsNull((SELECT Sum(B.Quantity)
FROM [dbo].PDN1 B INNER JOIN [dbo].OPDN A ON B.DocEntry = A.DocEntry
WHERE B.ItemCode=T.ItemCode AND A.DocDate >= '[%0]' AND A.DocDate<='[%1]' and A.Canceled='N'),0) as 'Entradas de Mescancia',
IsNull((SELECT Sum(B.Quantity)
FROM [dbo].RPD1 B INNER JOIN [dbo].ORPD A ON B.DocEntry = A.DocEntry
WHERE B.ItemCode=T.ItemCode AND A.DocDate >= '[%0]' AND A.DocDate<='[%1]' and A.Canceled='N'),0) as'Devoluciones',
IsNull((SELECT SUM(B.Quantity)
FROM [dbo].OWTR A INNER JOIN [dbo].WTR1 B ON A.DocEntry = B.DocEntry
WHERE B.ItemCode=T.ItemCode AND A.DocDate >= '[%0]' AND A.DocDate<='[%1]' and A.Canceled='N'),0)as 'Cantidad solicitada',
ISNULL((SELECT SUM(Y.Quantity)
FROM [dbo].OWTR X INNER JOIN [dbo].WTR1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >='[%0]' AND X.DocDate<='[%1]' AND Y.FromWhsCod = 'MP' AND Y.WhsCode = 'WIP'),0)as 'Cantidad Entregada',
ISNULL((SELECT SUM(Y.Quantity)
FROM [dbo].OIGE X INNER JOIN [dbo].IGE1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >='[%0]' AND X.DocDate<='[%1]' and Y.WhsCode = 'WIP' AND Y.AcctCode = '1200.2.1' AND X.Comments LIKE '%Nº-OT%'),0) as'Cantidad Consumida'
FROM OITM T
where T.SellItem = 'N' and T.PrchSeItem = 'Y'
GROUP BY T.ItemCode, T.ItemName
ORDER BY T.ItemCode
Hola Debes crear variables llamadas por ejemplo @FecIni @FecFin y luego asignarlas en tu query
/* Select * From [dbo].[OITM] T0 */
Declare @FecIni DateTime
Declare @FecFin DateTime
/* Where */
select @FecIni /* T0.[CreateDate] */= '[%0]'
/* And */
Select @FecFin /* T0.[CreateDate] */= '[%1]'
y en tu consulta reemplazas los '[%xx]' por @FecFin - @FecIni
Ejemplo:
AND X.DocDate >=@FecIni AND X.DocDate<= @Fecfin
saludos
me puedes apoya con un ejemplo de como quedaria ya que no entiendo muy bien eso de los parametros tengo algo asi pero me marca error
/* Select * From [dbo].[OITM] T */
Declare @FecIni DateTime
Declare @FecFin DateTime
Declare @FecInv DateTime
/* Where */
select @FecIni /* T.[CreateDate] */= '[%0]'
/* And */
Select @FecFin /* T.[CreateDate] */= '[%1]'
/* And*/
select @FecInv /* T.[CreateDate] */= '[%2]'
SELECT DISTINCT T.[ItemCode], T.[ItemName],
IsNull((SELECT Sum(B.[CountQty])
FROM [dbo].[OINC] A INNER JOIN [dbo].[INC1] B on A.[DocEntry] = B.[DocEntry]
WHERE B.[ItemCode]=T.[ItemCode] AND A.[CountDate] >= @FecInv and B.[WHSCODE] = 'MP'),0) as 'Cierre de Inventario',
IsNull((SELECT Sum(B.[Quantity])
FROM [dbo].[PDN1] B INNER JOIN [dbo].[OPDN] A ON B.[DocEntry] = A.[DocEntry]
WHERE B.[ItemCode]=T.[ItemCode] AND A.[DocDate] >= @FecIni AND A.[DocDate]<=@FecFin and A.[Canceled]='N'),0) as 'Entradas de Mescancia',
IsNull((SELECT Sum(B.[Quantity])
FROM [dbo].[RPD1] B INNER JOIN [dbo].[ORPD] A ON B.[DocEntry] = A.[DocEntry]
WHERE B.[ItemCode]=T.[ItemCode] AND A.[DocDate] >= @FecIni AND A.[DocDate]<=@FecFin and A.[Canceled]='N'),0) as'Devoluciones',
IsNull((SELECT SUM(B.[Quantity])
FROM [dbo].[OWTR] A INNER JOIN [dbo].[WTR1] B ON A.[DocEntry] = B.[DocEntry]
WHERE B.[ItemCode]=T.[ItemCode] AND A.[DocDate] >= @FecIni AND A.[DocDate]<=@FecFin and A.[Canceled]='N'),0)as 'Cantidad solicitada',
ISNULL((SELECT SUM(Y.[Quantity])
FROM [dbo].[OWTR] X INNER JOIN [dbo].[WTR1] Y ON Y.[DocEntry]=X.[DocEntry]
WHERE Y.[ItemCode]=T.[ItemCode] AND X.[DocDate] >=@FecIni AND A.[DocDate]<=@FecFin AND Y.[FromWhsCod] = 'MP' AND Y.[WhsCode] = 'WIP'),0)as 'Cantidad Entregada',
ISNULL((SELECT SUM(Y.[Quantity])
FROM [dbo].[OIGE] X INNER JOIN [dbo].[IGE1] Y ON Y.[DocEntry]=X.[DocEntry]
WHERE Y.[ItemCode]=T.[ItemCode] AND X.[DocDate] >=@FecIni AND A.[DocDate]<=@FecFin and Y.[WhsCode] = 'WIP' AND Y.[AcctCode] = '1200.2.1' AND X.[Comments] LIKE '%Nº-OT%'),0) as'Cantidad Consumida'
FROM [OITM] T
where T.[SellItem] = 'N' and T.[PrchSeItem] = 'Y'
GROUP BY T.[ItemCode], T.[ItemName]
ORDER BY T.[ItemCode]
en la fecha de Cierre de Inventario solo quiero que el usuario coloque una fecha que es la fecha del cierre de inventarios lo que contaron en el inventario
Hola Antonio.
Es por el alias de las sub consultas("A"), debes cambiarlo de la siguiente forma
/* Select * From [dbo].[OINC] T0 */
Declare @FecIni DateTime
Declare @FecFin DateTime
/* Where */
select @FecIni /* T0.[CountDate] */= '[%0]'
/* And */
Select @FecFin /* T0.[CountDate] */= '[%1]'
SELECT DISTINCT T.ItemCode, T.ItemName,
IsNull((SELECT Sum(B.CountQty)
FROM [dbo].OINC A INNER JOIN [dbo].INC1 B on A.DocEntry = B.DocEntry
WHERE B.ItemCode=T.ItemCode AND A.CountDate >= @FecFin and B.WHSCODE = 'MP'),0) as 'Cierre de Inventario',
IsNull((SELECT Sum(B.Quantity)
FROM [dbo].PDN1 B INNER JOIN [dbo].OPDN A1 ON B.DocEntry = A1.DocEntry
WHERE B.ItemCode=T.ItemCode AND A1.DocDate >= @FecIni AND A1.DocDate<= @Fecfin and A1.Canceled='N'),0) as 'Entradas de Mescancia',
IsNull((SELECT Sum(B.Quantity)
FROM [dbo].RPD1 B INNER JOIN [dbo].ORPD A2 ON B.DocEntry = A2.DocEntry
WHERE B.ItemCode=T.ItemCode AND A2.DocDate >= @FecIni AND A2.DocDate<= @Fecfin and A2.Canceled='N'),0) as'Devoluciones',
IsNull((SELECT SUM(B.Quantity)
FROM [dbo].OWTR A3 INNER JOIN [dbo].WTR1 B ON A3.DocEntry = B.DocEntry
WHERE B.ItemCode=T.ItemCode AND A3.DocDate >= @FecIni AND A3.DocDate<= @Fecfin and A3.Canceled='N'),0)as 'Cantidad solicitada',
ISNULL((SELECT SUM(Y.Quantity)
FROM [dbo].OWTR X INNER JOIN [dbo].WTR1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >=@FecIni AND X.DocDate<= @Fecfin AND Y.FromWhsCod = 'MP' AND Y.WhsCode = 'WIP'),0)as 'Cantidad Entregada',
ISNULL((SELECT SUM(Y.Quantity)
FROM [dbo].OIGE X INNER JOIN [dbo].IGE1 Y ON Y.DocEntry=X.DocEntry
WHERE Y.ItemCode=T.ItemCode AND X.DocDate >=@FecIni AND X.DocDate<= @Fecfin and Y.WhsCode = 'WIP' AND Y.AcctCode = '1200.2.1' AND X.Comments LIKE '%Nº-OT%'),0) as'Cantidad Consumida'
FROM OITM T
where T.SellItem = 'N' and T.PrchSeItem = 'Y'
GROUP BY T.ItemCode, T.ItemName
ORDER BY T.ItemCode
La ejecute y no manda error. Ahora debes chequear que te traiga los datos que tu necesitas.
Saludos
User | Count |
---|---|
99 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.