Skip to Content
avatar image
Former Member

Must Specify table to select from

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 27, 2016 at 11:56 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 28, 2016 at 07:48 AM

    Dear Diego Lother,

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

    Regards,

    Karthik B

    Add comment
    10|10000 characters needed characters exceeded