on 06-14-2013 12:18 AM
Jóvenes expertos tengo el siguiente problema realice un query en donde me muestra todas las ordenes de venta que esta abiertas pero cuando lo coloco en SAP y cuando el usuario tiene que elegir el proyecto me da un error si me muestra la ventana para seleccionar el proyecto pero cuando ya se lecciona y le da aceptar me da el siguiente error
les dejo el query para que lo vean y me indiquen en donde esta mi error
select Distinct T0.CardCode, T0.CardName,
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%' AND Y.DocStatus='O'),0)[Costo Exceso],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'AGU%%%' AND Y.DocStatus='O'),0)[Costo Cuota Fija Agua],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SEG%%%' AND Y.DocStatus='O'),0)[Costo Seguridad],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'RCONX%%%' AND Y.DocStatus='O'),0)[Reconexion],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SUSP%%%' AND Y.DocStatus='O'),0)[Suspencion],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'ADM%%%' AND Y.DocStatus='O'),0)[Administrativo],
(ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'AGU%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SEG%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'RCONX%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SUSP%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'ADM%%%' AND Y.DocStatus='O'),0))[Costo Total],
T2.BALANCE, T2.OrdersBal
from ORDR T0 INNER JOIN
RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
OCRD T2 ON T0.CardCode = T2.CardCode
Where T2.ProjectCod = [%0] and T0.DocStatus = 'O'
Group By T0.CardCode, T0.CardName, T0.DocNum, T0.DocEntry, T2.BALANCE, T2.OrdersBal
de antemano muy agradecido por la ayuda.
Saludos
Entonces podría ser alguna restricción del generador de consultas de SAP, te recomiendo generar un store procedure y lo mandas a llamar desde el generador de consultas con el comando EXEC
Ejemplo :
creas un store prodecure llamado "prueba" y dentro del sap donde pones el código de sql pones lo siguiente
EXEC prueba
De esta maneja ejecuta la consulta en SQLserver y los datos los muestra en SAP.
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Roger este es el query ya sin el projyeto
select Distinct T0.CardCode, T0.CardName,
ISNULL((SELECT SUM(X.PriceAfVAT) FROM dbo.ORDR Y INNER JOIN dbo.RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%' AND Y.DocStatus='O'),0)[Costo Exceso],
ISNULL((SELECT SUM(W.PriceAfVAT) FROM dbo.ORDR Z INNER JOIN dbo.RDR1 W ON W.DocEntry=Z.DocEntry WHERE Z.CardCode=T0.CardCode AND W.ItemCode LIKE 'AGU%%%' AND Z.DocStatus='O'),0)[Costo Cuota Fija Agua],
ISNULL((SELECT SUM(B.PriceAfVAT) FROM dbo.ORDR A INNER JOIN dbo.RDR1 B ON B.DocEntry=A.DocEntry WHERE A.CardCode=T0.CardCode AND B.ItemCode LIKE 'SEG%%%' AND A.DocStatus='O'),0)[Costo Seguridad],
ISNULL((SELECT SUM(D.PriceAfVAT) FROM dbo.ORDR C INNER JOIN dbo.RDR1 D ON D.DocEntry=C.DocEntry WHERE C.CardCode=T0.CardCode AND D.ItemCode LIKE 'RCONX%%%' AND C.DocStatus='O'),0)[Reconexion],
ISNULL((SELECT SUM(F.PriceAfVAT) FROM dbo.ORDR E INNER JOIN dbo.RDR1 F ON F.DocEntry=E.DocEntry WHERE E.CardCode=T0.CardCode AND F.ItemCode LIKE 'SUSP%%%' AND E.DocStatus='O'),0)[Suspencion],
ISNULL((SELECT SUM(H.PriceAfVAT) FROM dbo.ORDR G INNER JOIN dbo.RDR1 H ON H.DocEntry=G.DocEntry WHERE G.CardCode=T0.CardCode AND H.ItemCode LIKE 'ADM%%%' AND G.DocStatus='O'),0)[Administrativo],
(ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'AGU%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SEG%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'RCONX%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SUSP%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'ADM%%%' AND Y.DocStatus='O'),0))[Costo Total],
T2.BALANCE, T2.OrdersBal
from [dbo].ORDR T0 INNER JOIN
[dbo].RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
[dbo].OCRD T2 ON T0.CardCode = T2.CardCode
Where T0.DocStatus = O
Group By T0.CardCode, T0.CardName, T0.DocNum, T0.DocEntry, T2.BALANCE, T2.OrdersBal
Saludos
Prueba la siguiente query:
select
Distinct T0.CardCode, T0.CardName,
ISNULL
((SELECT SUM(X.PriceAfVAT) FROM [dbo].[ORDR] Y INNER JOIN [dbo].[RDR1] X ON X.DocEntry=Y.DocEntry
WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%'
AND Y.DocStatus='O'),0)[Costo Exceso],
ISNULL
((SELECT SUM(A.PriceAfVAT) FROM [dbo].[ORDR] B INNER JOIN [dbo].[RDR1] A ON a.DocEntry=b.DocEntry
WHERE b.CardCode=T0.CardCode AND a.ItemCode LIKE 'AGU%%%'
AND b.DocStatus='O'),0)[Costo Cuota Fija Agua],
ISNULL
((SELECT SUM(c.PriceAfVAT) FROM [dbo].[ORDR] d INNER JOIN [dbo].[RDR1] c ON c.DocEntry=d.DocEntry
WHERE d.CardCode=T0.CardCode AND c.ItemCode LIKE 'SEG%%%' AND d.DocStatus='O'),0)[Costo Seguridad],
ISNULL
((SELECT SUM(e.PriceAfVAT) FROM [dbo].[ORDR] f INNER JOIN [dbo].[RDR1] e ON e.DocEntry=f.DocEntry
WHERE f.CardCode=T0.CardCode AND e.ItemCode LIKE 'RCONX%%%' AND f.DocStatus='O'),0)[Reconexion],
ISNULL
((SELECT SUM(g.PriceAfVAT) FROM [dbo].[ORDR] h INNER JOIN [dbo].[RDR1] g ON g.DocEntry=h.DocEntry
WHERE h.CardCode=T0.CardCode AND g.ItemCode LIKE 'SUSP%%%' AND h.DocStatus='O'),0)[Suspencion],
ISNULL
((SELECT SUM(i.PriceAfVAT) FROM [dbo].[ORDR] j INNER JOIN [dbo].[RDR1] i ON j.DocEntry=i.DocEntry
WHERE j.CardCode=T0.CardCode AND i.ItemCode LIKE 'ADM%%%' AND j.DocStatus='O'),0)[Administrativo],
(
ISNULL((SELECT SUM(k.PriceAfVAT) FROM [dbo].[ORDR] l INNER JOIN [dbo].[RDR1] k ON k.DocEntry=l.DocEntry
WHERE l.CardCode=T0.CardCode AND k.ItemCode LIKE 'EXC%%%' AND l.DocStatus='O'),0)+
ISNULL((SELECT SUM(m.PriceAfVAT) FROM [dbo].[ORDR] n INNER JOIN [dbo].[RDR1] m ON m.DocEntry=n.DocEntry
WHERE n.CardCode=T0.CardCode AND m.ItemCode LIKE 'AGU%%%' AND n.DocStatus='O'),0)+
ISNULL((SELECT SUM(o.PriceAfVAT) FROM [dbo].[ORDR] p INNER JOIN [dbo].[RDR1] o ON o.DocEntry=p.DocEntry
WHERE p.CardCode=T0.CardCode AND o.ItemCode LIKE 'SEG%%%' AND p.DocStatus='O'),0)+
ISNULL((SELECT SUM(q.PriceAfVAT) FROM [dbo].[ORDR] r INNER JOIN [dbo].[RDR1] q ON q.DocEntry=r.DocEntry
WHERE r.CardCode=T0.CardCode AND q.ItemCode LIKE 'RCONX%%%' AND r.DocStatus='O'),0)+
ISNULL((SELECT SUM(s.PriceAfVAT) FROM [dbo].[ORDR] t INNER JOIN [dbo].[RDR1] s ON s.DocEntry=t.DocEntry
WHERE t.CardCode=T0.CardCode AND s.ItemCode LIKE 'SUSP%%%' AND t.DocStatus='O'),0)+
ISNULL((SELECT SUM(u.PriceAfVAT) FROM [dbo].[ORDR] v INNER JOIN [dbo].[RDR1] u ON u.DocEntry=v.DocEntry
WHERE v.CardCode=T0.CardCode AND u.ItemCode LIKE 'ADM%%%' AND v.DocStatus='O'),0))[Costo Total],
T2
.BALANCE, T2.OrdersBal
from
[dbo].[ORDR] T0 INNER JOIN
[dbo]
.[RDR1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
[dbo]
.[OCRD] T2 ON T0.CardCode = T2.CardCode
Where
T2.ProjectCod = [%0] and T0.DocStatus = 'O'
Group
By T0.CardCode, T0.CardName, T0.DocNum, T0.DocEntry, T2.BALANCE, T2.OrdersBal
Mira esta es la forma de crear tu store procedure solo cambia el nombre que deseas donde puse Nombre_consulta.
Esto lo corres dentro de SQL en la base que requieras como un query común y corriente.
create PROC [dbo].[Nombre_consulta]
AS
BEGIN
select Distinct T0.CardCode, T0.CardName,
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%' AND Y.DocStatus='O'),0)[Costo Exceso],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'AGU%%%' AND Y.DocStatus='O'),0)[Costo Cuota Fija Agua],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SEG%%%' AND Y.DocStatus='O'),0)[Costo Seguridad],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'RCONX%%%' AND Y.DocStatus='O'),0)[Reconexion],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SUSP%%%' AND Y.DocStatus='O'),0)[Suspencion],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'ADM%%%' AND Y.DocStatus='O'),0)[Administrativo],
(ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'AGU%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SEG%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'RCONX%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SUSP%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'ADM%%%' AND Y.DocStatus='O'),0))[Costo Total],
T2.BALANCE, T2.OrdersBal
from ORDR T0 INNER JOIN
RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
OCRD T2 ON T0.CardCode = T2.CardCode
Where T2.ProjectCod = [%0] and T0.DocStatus = 'O'
Group By T0.CardCode, T0.CardName, T0.DocNum, T0.DocEntry, T2.BALANCE, T2.OrdersBal
END
y ya dentro de SAP en el generador de consultas pones
Gracias Fernando en efecto si le quito el filtro del proyecto si me despliega la información que necesito pero tambien necesito crear un filtro por proyecto ya que tengo varios y necesito desplegarlos individualmente.
Gracias por tu ayuda y si hubiera una forma te lo agradeceria bastante.
Salduos
Sucede que en el filtro:
T2.ProjectCod = [%0] esta malo ya que el campo projectcode de la tabla ocrd no existe, debieras filtrarlo por otra tabla como por ejemplo los proyectos que están en la tabla de ordr, siendo asi la query final seria:
select
Distinct T0.CardCode, T0.CardName,
ISNULL
((SELECT SUM(X.PriceAfVAT) FROM [dbo].[ORDR] Y INNER JOIN [dbo].[RDR1] X ON X.DocEntry=Y.DocEntry
WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%'
AND Y.DocStatus='O'),0)[Costo Exceso],
ISNULL
((SELECT SUM(A.PriceAfVAT) FROM [dbo].[ORDR] B INNER JOIN [dbo].[RDR1] A ON a.DocEntry=b.DocEntry
WHERE b.CardCode=T0.CardCode AND a.ItemCode LIKE 'AGU%%%'
AND b.DocStatus='O'),0)[Costo Cuota Fija Agua],
ISNULL
((SELECT SUM(c.PriceAfVAT) FROM [dbo].[ORDR] d INNER JOIN [dbo].[RDR1] c ON c.DocEntry=d.DocEntry
WHERE d.CardCode=T0.CardCode AND c.ItemCode LIKE 'SEG%%%' AND d.DocStatus='O'),0)[Costo Seguridad],
ISNULL
((SELECT SUM(e.PriceAfVAT) FROM [dbo].[ORDR] f INNER JOIN [dbo].[RDR1] e ON e.DocEntry=f.DocEntry
WHERE f.CardCode=T0.CardCode AND e.ItemCode LIKE 'RCONX%%%' AND f.DocStatus='O'),0)[Reconexion],
ISNULL
((SELECT SUM(g.PriceAfVAT) FROM [dbo].[ORDR] h INNER JOIN [dbo].[RDR1] g ON g.DocEntry=h.DocEntry
WHERE h.CardCode=T0.CardCode AND g.ItemCode LIKE 'SUSP%%%' AND h.DocStatus='O'),0)[Suspencion],
ISNULL
((SELECT SUM(i.PriceAfVAT) FROM [dbo].[ORDR] j INNER JOIN [dbo].[RDR1] i ON j.DocEntry=i.DocEntry
WHERE j.CardCode=T0.CardCode AND i.ItemCode LIKE 'ADM%%%' AND j.DocStatus='O'),0)[Administrativo],
(
ISNULL((SELECT SUM(k.PriceAfVAT) FROM [dbo].[ORDR] l INNER JOIN [dbo].[RDR1] k ON k.DocEntry=l.DocEntry
WHERE l.CardCode=T0.CardCode AND k.ItemCode LIKE 'EXC%%%' AND l.DocStatus='O'),0)+
ISNULL((SELECT SUM(m.PriceAfVAT) FROM [dbo].[ORDR] n INNER JOIN [dbo].[RDR1] m ON m.DocEntry=n.DocEntry
WHERE n.CardCode=T0.CardCode AND m.ItemCode LIKE 'AGU%%%' AND n.DocStatus='O'),0)+
ISNULL((SELECT SUM(o.PriceAfVAT) FROM [dbo].[ORDR] p INNER JOIN [dbo].[RDR1] o ON o.DocEntry=p.DocEntry
WHERE p.CardCode=T0.CardCode AND o.ItemCode LIKE 'SEG%%%' AND p.DocStatus='O'),0)+
ISNULL((SELECT SUM(q.PriceAfVAT) FROM [dbo].[ORDR] r INNER JOIN [dbo].[RDR1] q ON q.DocEntry=r.DocEntry
WHERE r.CardCode=T0.CardCode AND q.ItemCode LIKE 'RCONX%%%' AND r.DocStatus='O'),0)+
ISNULL((SELECT SUM(s.PriceAfVAT) FROM [dbo].[ORDR] t INNER JOIN [dbo].[RDR1] s ON s.DocEntry=t.DocEntry
WHERE t.CardCode=T0.CardCode AND s.ItemCode LIKE 'SUSP%%%' AND t.DocStatus='O'),0)+
ISNULL((SELECT SUM(u.PriceAfVAT) FROM [dbo].[ORDR] v INNER JOIN [dbo].[RDR1] u ON u.DocEntry=v.DocEntry
WHERE v.CardCode=T0.CardCode AND u.ItemCode LIKE 'ADM%%%' AND v.DocStatus='O'),0))[Costo Total],
T2
.BALANCE, T2.OrdersBal
from
[dbo].[ORDR] T0 INNER JOIN
[dbo]
.[RDR1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
[dbo]
.[OCRD] T2 ON T0.CardCode = T2.CardCode left join
[dbo]
.[oprj] t3 on t0.Project =t3.prjcode
Where
T3.prjcode = [%0] and T0.DocStatus = 'O'
Group
By T0.CardCode, T0.CardName, T0.DocNum, T0.DocEntry, T2.BALANCE, T2.OrdersBal
Angel gracias por la informacion de como crear los SP, te comento que hice una prueba y me dio el error en el SQl en la Where T2.ProjectCod = [%0] y lo que hice fue colocalo de esta forma
Where T2.ProjectCod = '[%0] ' con comillas y lo grabo correctamente pero cuando lo ejecuto en el generador de consultas de SAP me indica que no encontro ningun registro y no me muestra la ventana para seleccionar el proyecto.
y me da una ventana en blanco
Gracias por la ayuda y la enseñansa
DEbo rectificar, el campo si existe pero no me había dado cuenta, entonces el join debiera ser:
[dbo].[ORDR] T0 INNER JOIN
[dbo]
.[RDR1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
[dbo]
.[OCRD] T2 ON T0.CardCode = T2.CardCode left join
[dbo]
.[oprj] t3 on t2.Projectcode =t3.prjcode
Where
T3.prjcode = [%0] and T0.DocStatus = 'O'
fernando madriaza wrote:
¿si al parámetro lo colocas entre comillas simples te da el mismo error?
fernando madriaza wrote:
¿si al parámetro lo colocas entre comillas simples te da el mismo error?
Te estoy agradecido Fernando ya quedo el Query y me desplego la informacion como la deseaba, adjunto encontraran como quedo el query final.
select Distinct T0.CardCode, T0.CardName,
ISNULL((SELECT SUM(X.PriceAfVAT) FROM [dbo].[ORDR] Y INNER JOIN [dbo].[RDR1] X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%'AND Y.DocStatus='O'),0)[Costo Exceso],
ISNULL((SELECT SUM(A.PriceAfVAT) FROM [dbo].[ORDR] B INNER JOIN [dbo].[RDR1] A ON a.DocEntry=b.DocEntry WHERE b.CardCode=T0.CardCode AND a.ItemCode LIKE 'AGU%%%'AND b.DocStatus='O'),0)[Costo Cuota Fija Agua],
ISNULL((SELECT SUM(c.PriceAfVAT) FROM [dbo].[ORDR] d INNER JOIN [dbo].[RDR1] c ON c.DocEntry=d.DocEntry WHERE d.CardCode=T0.CardCode AND c.ItemCode LIKE 'SEG%%%' AND d.DocStatus='O'),0)[Costo Seguridad],
ISNULL((SELECT SUM(e.PriceAfVAT) FROM [dbo].[ORDR] f INNER JOIN [dbo].[RDR1] e ON e.DocEntry=f.DocEntry WHERE f.CardCode=T0.CardCode AND e.ItemCode LIKE 'RCONX%%%' AND f.DocStatus='O'),0)[Reconexion],
ISNULL((SELECT SUM(g.PriceAfVAT) FROM [dbo].[ORDR] h INNER JOIN [dbo].[RDR1] g ON g.DocEntry=h.DocEntry WHERE h.CardCode=T0.CardCode AND g.ItemCode LIKE 'SUSP%%%' AND h.DocStatus='O'),0)[Suspencion],
ISNULL((SELECT SUM(i.PriceAfVAT) FROM [dbo].[ORDR] j INNER JOIN [dbo].[RDR1] i ON j.DocEntry=i.DocEntry WHERE j.CardCode=T0.CardCode AND i.ItemCode LIKE 'ADM%%%' AND j.DocStatus='O'),0)[Administrativo],
(
ISNULL((SELECT SUM(k.PriceAfVAT) FROM [dbo].[ORDR] l INNER JOIN [dbo].[RDR1] k ON k.DocEntry=l.DocEntry WHERE l.CardCode=T0.CardCode AND k.ItemCode LIKE 'EXC%%%' AND l.DocStatus='O'),0)+
ISNULL((SELECT SUM(m.PriceAfVAT) FROM [dbo].[ORDR] n INNER JOIN [dbo].[RDR1] m ON m.DocEntry=n.DocEntry WHERE n.CardCode=T0.CardCode AND m.ItemCode LIKE 'AGU%%%' AND n.DocStatus='O'),0)+
ISNULL((SELECT SUM(o.PriceAfVAT) FROM [dbo].[ORDR] p INNER JOIN [dbo].[RDR1] o ON o.DocEntry=p.DocEntry WHERE p.CardCode=T0.CardCode AND o.ItemCode LIKE 'SEG%%%' AND p.DocStatus='O'),0)+
ISNULL((SELECT SUM(q.PriceAfVAT) FROM [dbo].[ORDR] r INNER JOIN [dbo].[RDR1] q ON q.DocEntry=r.DocEntry WHERE r.CardCode=T0.CardCode AND q.ItemCode LIKE 'RCONX%%%' AND r.DocStatus='O'),0)+
ISNULL((SELECT SUM(s.PriceAfVAT) FROM [dbo].[ORDR] t INNER JOIN [dbo].[RDR1] s ON s.DocEntry=t.DocEntry WHERE t.CardCode=T0.CardCode AND s.ItemCode LIKE 'SUSP%%%' AND t.DocStatus='O'),0)+
ISNULL((SELECT SUM(u.PriceAfVAT) FROM [dbo].[ORDR] v INNER JOIN [dbo].[RDR1] u ON u.DocEntry=v.DocEntry WHERE v.CardCode=T0.CardCode AND u.ItemCode LIKE 'ADM%%%' AND v.DocStatus='O'),0))[Costo Total],
T2.BALANCE, T2.OrdersBal
from [dbo].[ORDR] T0 INNER JOIN
[dbo].[RDR1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
[dbo].[OCRD] T2 ON T0.CardCode = T2.CardCode left join
[dbo].[OPRJ] T3 on T2.[ProjectCod] = T3.[PrjCode]
Where T0.DocStatus = 'O' and T3.Prjcode = '[%0]'
Group By T0.CardCode, T0.CardName, T0.DocNum, T0.DocEntry, T2.BALANCE, T2.OrdersBal
Les agradezco a todos por la ayuda.
Saludos Cordiales
quita la columna donde haces las sumas, en una de ellas el query encontro un dato que no es sumarizable, el error que te da el sap es porque encuentra un valor que dice Anulada del tipo varchar y no lo puede convertir en INt para hacer la suma, por eso quita la suma osea el campo [Costo Total] y deja que te muestre que encuentra.
si sale algun otro error postealo para ver que es.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Intenta este query:
select T0.CardCode, T0.CardName,
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%' AND Y.DocStatus='O'),0)[Costo Exceso],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'AGU%%%' AND Y.DocStatus='O'),0)[Costo Cuota Fija Agua],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SEG%%%' AND Y.DocStatus='O'),0)[Costo Seguridad],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'RCONX%%%' AND Y.DocStatus='O'),0)[Reconexion],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SUSP%%%' AND Y.DocStatus='O'),0)[Suspencion],
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'ADM%%%' AND Y.DocStatus='O'),0)[Administrativo],
(ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'AGU%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SEG%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'RCONX%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'SUSP%%%' AND Y.DocStatus='O'),0)+
ISNULL((SELECT SUM(X.PriceAfVAT) FROM ORDR Y INNER JOIN RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'ADM%%%' AND Y.DocStatus='O'),0))[Costo Total],
T0.BALANCE, T0.OrdersBal
from OCRD T0
where T0.ProjectCod = [%0] AND t0.cardtype='C'
Gracias Fernando por tu ayuda te comento que hice lo que me indica y me quedo de esta forma el query
select Distinct T0.CardCode, T0.CardName,
ISNULL((SELECT SUM(X.PriceAfVAT) FROM dbo.ORDR Y INNER JOIN dbo.RDR1 X ON X.DocEntry=Y.DocEntry WHERE Y.CardCode=T0.CardCode AND X.ItemCode LIKE 'EXC%%%' AND Y.DocStatus='O'),0)[Costo Exceso],
ISNULL((SELECT SUM(W.PriceAfVAT) FROM dbo.ORDR Z INNER JOIN dbo.RDR1 W ON W.DocEntry=Z.DocEntry WHERE Z.CardCode=T0.CardCode AND W.ItemCode LIKE 'AGU%%%' AND Z.DocStatus='O'),0)[Costo Cuota Fija Agua],
ISNULL((SELECT SUM(B.PriceAfVAT) FROM dbo.ORDR A INNER JOIN dbo.RDR1 B ON B.DocEntry=A.DocEntry WHERE A.CardCode=T0.CardCode AND B.ItemCode LIKE 'SEG%%%' AND A.DocStatus='O'),0)[Costo Seguridad],
ISNULL((SELECT SUM(D.PriceAfVAT) FROM dbo.ORDR C INNER JOIN dbo.RDR1 D ON D.DocEntry=C.DocEntry WHERE C.CardCode=T0.CardCode AND D.ItemCode LIKE 'RCONX%%%' AND C.DocStatus='O'),0)[Reconexion],
ISNULL((SELECT SUM(F.PriceAfVAT) FROM dbo.ORDR E INNER JOIN dbo.RDR1 F ON F.DocEntry=E.DocEntry WHERE E.CardCode=T0.CardCode AND F.ItemCode LIKE 'SUSP%%%' AND E.DocStatus='O'),0)[Suspencion],
ISNULL((SELECT SUM(H.PriceAfVAT) FROM dbo.ORDR G INNER JOIN dbo.RDR1 H ON H.DocEntry=G.DocEntry WHERE G.CardCode=T0.CardCode AND H.ItemCode LIKE 'ADM%%%' AND G.DocStatus='O'),0)[Administrativo],
T2.BALANCE, T2.OrdersBal
from [dbo].ORDR T0 INNER JOIN
[dbo].RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
[dbo].OCRD T2 ON T0.CardCode = T2.CardCode
Where T2.ProjectCod = '[%0]' and T0.DocStatus = 'O'
Group By T0.CardCode, T0.CardName, T0.DocNum, T0.DocEntry, T2.BALANCE, T2.OrdersBal
Pero lamento informar que me dio el mismo error.
De antemano agradezco la ayuda.
Hola.
Para el proyecto en la cláusula where colocalo así:
Where T2.ProjectCod = '[%0]'
Saludos cordiales.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola
Podrías correr tu query en SQLserver e indicar en que linea te esta mandando el error, al parecer el por algún campo y el tipo de datos que esta tratando de manejar.
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Entonces podría ser alguna restricción del generador de consultas de SAP, te recomiendo generar un store procedure y lo mandas a llamar desde el generador de consultas con el comando EXEC
Ejemplo :
creas un store prodecure llamado "prueba" y dentro del sap donde pones el código de sql pones lo siguiente
EXEC prueba
De esta maneja ejecuta la consulta en SQLserver y los datos los muestra en SAP.
Saludos
Qui tale las comillas al valor 'O' del status y prueba.
Un saludo,
Tere
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
107 | |
12 | |
11 | |
6 | |
5 | |
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.