Error de Query en la Consola de SAP

[Former Member]

Buenas Les escribo para ver si alguien sabe que debo hacer para solucionar un problema que tengo con un query, este me funciona perfectamente en el sql server 2005 pero en sap me da un error "Service Contratc OCTR".

[Humberto_Neira · Accepted solution]

Cuál es la query?

[Former Member · Accepted solution]

select VE.[slpname] as Vendedor,

VE.[slpcode] as 'Codigo de Vendedor',

(select sum(COB.pago_con_tax)

from

(select ((fp.sumapplied - (fp.sumapplied * (select (TotalExpns / doctotal)

from oinv xfa

where xfa.docnum = fa.docnum))) * (isnull((select top(1) 107

from inv1 xfp

where xfp.docentry = fa.docentry and

xfp.taxcode <> 1),100))/107) as pago_con_tax

from OINV FA,

RCT2 FP,

ORCT PA

where FP.[DocNum] = PA.[DocNum] and

FA.[DocEntry] = FP.[DocEntry] and

FA.[slpcode] = VE.[slpcode] and

--PA.docdate <= '[%1]' and

--PA.docdate >='[%0]' and

pa.canceled = 'N' --and

--datediff(day,FA.DocDate,'[%1]') <= 90

) COB) as Cobros,

(select COM.U_porc

from COM

where COM.U_desde <=

(select sum(COB.pago_con_tax)

from

(select ((fp.sumapplied - (fp.sumapplied * (select (TotalExpns / doctotal)

from oinv xfa

where xfa.docnum = fa.docnum))) * (isnull((select top(1) 107

from inv1 xfp

where xfp.docentry = fa.docentry and

xfp.taxcode <> 1),100))/107) as pago_con_tax

from OINV FA,

RCT2 FP,

ORCT PA

where FP.[DocNum] = PA.[DocNum] and

FA.[DocEntry] = FP.[DocEntry] and

FA.[slpcode] = VE.[slpcode] and

--PA.docdate <= '[%1]' and

--PA.docdate >='[%0]' and

pa.canceled = 'N' --and

--datediff(day,FA.DocDate,'[%1]') <= 90

) COB) and

COM.U_hasta >=

(select sum(COB.pago_con_tax)

from

(select ((fp.sumapplied - (fp.sumapplied * (select (TotalExpns / doctotal)

from oinv xfa

where xfa.docnum = fa.docnum))) * (isnull((select top(1) 107

from inv1 xfp

where xfp.docentry = fa.docentry and

xfp.taxcode <> 1),100))/107) as pago_con_tax

from OINV FA,

RCT2 FP,

ORCT PA

where FP.[DocNum] = PA.[DocNum] and

FA.[DocEntry] = FP.[DocEntry] and

FA.[slpcode] = VE.[slpcode] and

--PA.docdate <= '[%1]' and

--PA.docdate >='[%0]' and

pa.canceled = 'N' --and

--datediff(day,FA.DocDate,'[%1]') <= 90

) COB)) as Porcentaje,

((select sum(COB.pago_con_tax)

from

(select ((fp.sumapplied - (fp.sumapplied * (select (TotalExpns / doctotal)

from oinv xfa

where xfa.docnum = fa.docnum))) * (isnull((select top(1) 107

from inv1 xfp

where xfp.docentry = fa.docentry and

xfp.taxcode <> 1),100))/107) as pago_con_tax

from OINV FA,

RCT2 FP,

ORCT PA

where FP.[DocNum] = PA.[DocNum] and

FA.[DocEntry] = FP.[DocEntry] and

FA.[slpcode] = VE.[slpcode] and

--PA.docdate <= '[%1]' and

--PA.docdate >='[%0]' and

pa.canceled = 'N' --and

--datediff(day,FA.DocDate,'[%1]') <= 90

) COB) *

(select COM.U_porc

from COM

where COM.U_desde <=

(select sum(COB.pago_con_tax)

from

(select ((fp.sumapplied - (fp.sumapplied * (select (TotalExpns / doctotal)

from oinv xfa

where xfa.docnum = fa.docnum))) * (isnull((select top(1) 107

from inv1 xfp

where xfp.docentry = fa.docentry and

xfp.taxcode <> 1),100))/107) as pago_con_tax

from OINV FA,

RCT2 FP,

ORCT PA

where FP.[DocNum] = PA.[DocNum] and

FA.[DocEntry] = FP.[DocEntry] and

FA.[slpcode] = VE.[slpcode] and

--PA.docdate <= '[%1]' and

--PA.docdate >='[%0]' and

pa.canceled = 'N' --and

--datediff(day,FA.DocDate,'[%1]') <= 90

) COB) and

COM.U_hasta >=

(select sum(COB.pago_con_tax)

from

(select ((fp.sumapplied - (fp.sumapplied * (select (TotalExpns / doctotal)

from oinv xfa

where xfa.docnum = fa.docnum))) * (isnull((select top(1) 107

from inv1 xfp

where xfp.docentry = fa.docentry and

xfp.taxcode <> 1),100))/107) as pago_con_tax

from OINV FA,

RCT2 FP,

ORCT PA

where FP.[DocNum] = PA.[DocNum] and

FA.[DocEntry] = FP.[DocEntry] and

FA.[slpcode] = VE.[slpcode] and

--PA.docdate <= '[%1]' and

--PA.docdate >='[%0]' and

pa.canceled = 'N' --and

--datediff(day,FA.DocDate,'[%1]') <= 90

) COB))/100) as Comisión

from OSLP VE

asi corre en sql perfectamente

PF no se porque se ve asi deberia ser Corchetes y no verse asi como tachado

Edited by: Yechezkel Rosales` on Sep 30, 2008 4:17 PM

[Humberto_Neira · Accepted solution]

1. la query no anda en SQL Server porque me falta tu definicion de tabla de usuario.

2. Recomendación:

-. Este tipo de querys no las ejecutes desde B1, pues serán muy degradantes en performance. Analiza el uso de Tablas temporales en SQL Server, el uso de Store Procedures o crear Vistas para hacer querys intermedias, tal que la query final no sea tan degradante y compleja en el SQL.

-. La query final debe ser muy simple para que los %1 de las variables funcionen correctamente, sino seguirás obtenido el error que es casi un DEFAULT para decirte: QUERY NO SE PUEDE EVALUAR DESDE B1.

saludos