Skip to Content
0

Must Specify table to select from

Nov 27, 2016 at 05:20 PM

131

avatar image

Dear Experts,

While executing below query its working i sql but when running in query generator is sap getting error MUST SPECIFY TABLE TO SELECT FROM

Declare @Fd as date

Declare @TD as date

set @Fd = [%0]

set @TD = [%1]

select sum(T0.doctotal) as 'Sale',0 as 'Purchase',0 as 'License Sale net' from OINV T0 Where T0.DocDate > = @Fd and T0.DocDate <= @TD

union all

select 0 as 'Sale',sum(T0.doctotal) as 'Purchase', 0 as 'License Sale net' from OPCH T0 Where T0.DocDate > = @Fd and T0.DocDate <= @TD

union all

select 0 as 'Sale', 0 as 'Purchase',sum(T0.Credit) as 'License Sale net' from JDT1 T0 Where T0.refdate >= @Fd and T0.RefDate <= @TD and T0.TransType = '30' and (T0.Account = '11110002' or T0.ShortName = 'VEND0101')

Regards,

Karthik B

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
DIEGO LOTHER Nov 27, 2016 at 11:56 PM
0

Hi Karthik,

A parameter must be linked to a B1 table. To do what you are trying, use this format:

DECLARE @Fd AS DATE, @TD AS DATE


/* SELECT 1 FROM OINV T0 WHERE */ SET @Fd /* T0.DocDate >= */ = '[%0]' /* AND */ SET @TD /* T0.DocDate <= */ = '[%1]'


SELECT 
	SUM(T0.doctotal) AS 'Sale', 
	0 AS 'Purchase', 
	0 AS 'License Sale net' 
FROM 
	OINV T0 
WHERE 
	T0.DocDate >= @Fd AND T0.DocDate <= @TD
UNION ALL
SELECT 
	0 AS 'Sale', 
	SUM(T0.doctotal) AS 'Purchase', 
	0 AS 'License Sale net' 
FROM 
	OPCH T0 
WHERE 
	T0.DocDate > = @Fd AND T0.DocDate <= @TD
UNION ALL
SELECT 
	0 AS 'Sale', 
	0 AS 'Purchase', 
	SUM(T0.Credit) AS 'License Sale net' 
FROM 
	JDT1 T0 
WHERE 
	T0.refdate >= @Fd AND T0.RefDate <= @TD 
	AND T0.TransType = '30' 
	AND (T0.Account = '11110002' OR T0.ShortName = 'VEND0101')

Kind Regards,

Diego Lother

Share
10 |10000 characters needed characters left characters exceeded
karthik B Nov 28, 2016 at 07:48 AM
0

Dear Diego Lother,

Thanks for the help the issue was solved modified the query as you said

Regards,

Karthik B

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Karthik,

Don't forget to accept the answer as correct, clicking in the accept button below the answer.

Kind Regards,

Diego Lother

0