Help for query that not work in SAP query generator.

[Former Member]

Hi All,

I have done this request :

Select T0.slpname as 'Nom du commercial', T10.CA2008 as 'CA N-2'

FROM OSLP T0 left outer join (SELECT T11.SlpCode, sum(T11.[DocTotal]- T11.[VatSum]) as 'CA2008'

FROM ORDR T11 INNER JOIN OFPR T12 ON T11.FinncPriod = T12.AbsEntry INNER JOIN OACP T13 ON T12.Category = T13.PeriodCat

WHERE T11.DocDate>='[%0]' AND T11.DocDate<='[%1]' Group by T11.SlpCode) T10 on T0.SlpCode=T10.SlpCode

Group by T0.slpname, T10.CA2008

This work fine in SQL management studio but not in SAP with the query generator. When i'm execute this query i've got this error :

'Must specify table to select from'

Thanks for your help.

Best Regards,

Michael

[Former Member]

Thanks all.

[gerritberberich]

Hello,

i think a have a similar problem/question (same error). But I can't find a solution. Could you help me?

/*Declare Item you search for */

/*SELECT FROM [DBO].[OITM] T0*/
Declare @Item varchar(20)
/*WHERE*/
SET @Item = /*T0.[ItemCode]*/'[%0]'


/*Definition of Result-Table */
DECLARE @ItemPrices TABLE
(
  ListName varchar(50),
  Price money,
  U_CardCode varchar(20), 
  Quantity numeric

)

/* - Std. Purchase Price (Pricelist)*/
/* - Std. Sales Price (Pricelist)*/
INSERT INTO @ItemPrices (ListName, Price, U_CardCode, Quantity)
Select t1.ListName, t0.Price, '' As U_CardCode, 1 As Quantity
from ITM1 t0, OPLN t1 
where ItemCode = @Item and 
(t0.PriceList = '1' or t0.PriceList = '2') and
t1.ListNum = t0.PriceList

/* - Specific-Price for BP*/
INSERT INTO @ItemPrices (U_CardCode, Quantity, Price, ListName )
Select U_CardCode, U_MinQuantity As Quantity, U_SpecialPrice As 

Price,'Customer Spec. Price epM' As Listname
from [dbo].[@EBM_SPECIALPRICE]
Where U_ItemCode = @Item


/* - Customer-Specific-Price (Special_Price)*/
/* --> more than one result possible (volume discounts) */
INSERT INTO @ItemPrices (Quantity, Price, U_CardCode, Listname ) 
Select t0.Amount As Quantity, t0.Price As Price, t1.CardCode As U_CardCode, 

' Specialprice' As Listname 
From SPP2 t0
Join OITM t1 on (t0.ItemCode = t1.ItemCode) 
Where 
t0.CardCode = (Select Top 1 CardCode from OITM where ItemCode = @Item)


/* Select Result-Table */
SELECT *
FROM @ItemPrices

thank you

Edited by: GB on Aug 12, 2011 6:29 PM

Edited by: GB on Aug 12, 2011 6:30 PM

[agustin_marcoscividanes]

Hi

try this:

/* SELECT T0.[docdate] FROM OINV T0 */

DECLARE @fecha_ini AS DATETIME SET @fecha_ini = '[%0]' --> with

DECLARE @fecha_fin AS DATETIME SET @fecha_fin = '[%1]' --> with

Select T0.[slpname] as 'Nom du commercial', T10.[CA2008] as 'CA N-2' FROM OSLP T0 left outer join (SELECT T11.[SlpCode], sum(T11.[DocTotal] - T11.[VatSum]) as 'CA2008' FROM ORDR T11 INNER JOIN OFPR T12 ON T11.[FinncPriod] = T12.[AbsEntry] INNER JOIN OACP T13 ON T12.[Category] = T13.[PeriodCat] WHERE T11.[DocDate]>= @fecha_ini AND T11.[DocDate]<= @fecha_fin Group by T11.[SlpCode]) T10 on T0.[SlpCode] =T10.[SlpCode] Group by T0.[slpname], T10.[CA2008]

Surely you have to write [] in all used table fields.

All the best

Agustín

Edited by: Agustin Marcos Cividanes on Jun 8, 2011 5:20 PM

Edited by: Agustin Marcos Cividanes on Jun 8, 2011 5:20 PM

[Former Member]

Hi Michael,

Try:

SELECT T0.SlpName as 'Nom du commercial', sum(T1.DocTotal- T1.VatSum) as 'CA N-2'

FROM OSLP T0

LEFT JOIN ORDR T1 ON T0.SlpCode = T1.SlpCode AND T1.DocDate>='[%0\]' AND T1.DocDate<='[%1\]'

LEFT JOIN OFPR T3 ON T1.FinncPriod = T3.AbsEntry

LEFT JOIN OACP T4 ON T3.Category = T4.PeriodCat

Group by T0.SlpName

Thanks,

Gordon

[former_member206488]

Hi,

Try this:

/*SELECT FROM [dbo].[ORDR] T0*/
declare  @fromdate as datetime
/* WHERE */
set @fromdate = /* T0.DocDate */ '[%0]'

/*SELECT FROM [dbo].[ORDR] T1*/
declare  @tilldate as datetime
/* WHERE */
set @tilldate = /* T0.DocDate */ '[%1]'
Select T0.slpname as 'Nom du commercial', T10.CA2008 as 'CA N-2'
FROM OSLP T0 
   left outer join 
     (SELECT T11.SlpCode, sum(T11.DocTotal- T11.VatSum) as 'CA2008' 
        FROM ORDR T11 INNER JOIN OFPR T12 ON T11.FinncPriod = T12.AbsEntry INNER JOIN OACP T13 ON T12.Category = T13.PeriodCat 
        WHERE T11.DocDate>=@fromdate AND T11.DocDate<=@tilldate Group by T11.SlpCode) T10 
        on T0.SlpCode=T10.SlpCode
Group by T0.slpname, T10.CA2008

Donot remove comments from query , paste as it is mentioned above.

Thanks,

Neetu