on 09-27-2011 4:59 PM
Hola
Estamos corriendo una consulta en SAP y nos manda un mensaje de error y sii la corro en SQL funciona perfectamente
No sé si tenga que hacer alguna otra cosa para que logre correrla
La versión de SQL que Tengo es la siguiente.
SAP Business One 8.8 (8.80.236) PL: 18
La consulta es la siguiente
SELECT q1.Marca, SUM(q1."CORDOBA") as Cordoba, SUM(q1."PUEBLA") as Puebla, SUM(q1."CUAUHTEMOC") as Cuhuatemoc, SUM(q1."DIAZ MIRON") as DiazMiron FROM
(
select omrc.FirmName as 'Marca',
case when nnm1.SeriesName='CORDOBA' then sum(inv1.quantity) else 0 end as "CORDOBA",
case when nnm1.SeriesName='PUEBLA' then sum(inv1.quantity) else 0 end as "PUEBLA",
case when nnm1.SeriesName='VERCT' then sum(inv1.quantity) else 0 end as "CUAUHTEMOC",
case when nnm1.SeriesName='VERDM' then sum(inv1.quantity) else 0 end as "DIAZ MIRON"
from oinv
inner join inv1 on oinv.docentry= inv1.docentry
inner join oitm on oitm.itemcode=inv1.itemcode
inner join ohem on ohem.salesprson=oinv.slpcode
inner join oitb on oitb.itmsgrpcod=oitm.itmsgrpcod
inner join omrc on omrc.FirmCode=oitm.FirmCode
inner join nnm1 on nnm1.Series=oinv.series
--where oinv.taxdate between '2011/01/01' AND '2011/01/31' and FirmName in ('Michelin', 'BF Goodrich', 'Uniroyal', 'Medalist')and oitb.ItmsGrpNam='Auto/Camioneta'
where oinv.taxdate between '[%0]' AND '[%1]' and FirmName in ('Michelin', 'BF Goodrich', 'Uniroyal', 'Medalist')and oitb.ItmsGrpNam='[%2]'
group by OMRC.FirmName, nnm1.SeriesName
)as q1
GROUP BY q1.Marca
Y el mensaje de error en SAP ES
microsoft[SQL Native Client][SQL Server]Must specify table to select from 2[SQL Native Client][SQL Server] Statment ´Contratos de Servicio´ (ocrt) COULD NOT BE PREPARED
Ojala puedan ayudarme un poco con esto
Saludos
Yen
el problema era que no acepta consultas cuando no se le ponen alias a las tablas, les agradezco muchísimo.
Saludosu2026
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Es un pequeño problema de SBO
Pruebalo asi
SELECT q1.Marca, SUM(q1.CORDOBA) as Cordoba, SUM(q1.PUEBLA) as Puebla, SUM(q1.CUAUHTEMOC) as Cuhuatemoc, SUM(q1.DIAZ MIRON) as DiazMiron FROM
(
select omrc.FirmName as 'Marca',
case when nnm1.SeriesName='CORDOBA' then sum(inv1.quantity) else 0 end as "CORDOBA",
case when nnm1.SeriesName='PUEBLA' then sum(inv1.quantity) else 0 end as "PUEBLA",
case when nnm1.SeriesName='VERCT' then sum(inv1.quantity) else 0 end as "CUAUHTEMOC",
case when nnm1.SeriesName='VERDM' then sum(inv1.quantity) else 0 end as "DIAZ MIRON"
from [dbo].[oinv]
inner join [dbo].[inv1] on oinv.docentry= inv1.docentry
inner join [dbo].[oitm] on oitm.itemcode=inv1.itemcode
inner join [dbo].[ohem] on ohem.salesprson=oinv.slpcode
inner join [dbo].[oitb] on oitb.itmsgrpcod=oitm.itmsgrpcod
inner join [dbo].[omrc] on omrc.FirmCode=oitm.FirmCode
inner join [dbo].[nnm1] on nnm1.Series=oinv.series
--where oinv.taxdate between '2011/01/01' AND '2011/01/31' and FirmName in ('Michelin', 'BF Goodrich', 'Uniroyal', 'Medalist')and oitb.ItmsGrpNam='Auto/Camioneta'
where oinv.taxdate between '[%0]' AND '[%1]' and FirmName in ('Michelin', 'BF Goodrich', 'Uniroyal', 'Medalist')and oitb.ItmsGrpNam='[%2]'
group by OMRC.FirmName, nnm1.SeriesName
)as q1
GROUP BY q1.Marca
Saludos
Edited by: Floyola on Sep 27, 2011 12:06 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola, me parece no estan soportados los subquerys en SAP
Yo te recomiendo que mejor, pongas tu reporte como stored procedure y lo mandes llamar desde SAP
eso si podria funcionar
Al final, las variables que usas en SAP , puedes mandarlas como parametros a tu stored
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
10 | |
6 | |
5 | |
5 | |
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.