must specify table to select from

[Former Member]

hi everybody

i think sometime we received this error message:

1).[Microsoft][SQL Server Native Client 10.0][SQL Server]Must specify table to selet from. 2).[Microsoft][SQL Server Native Client 10.0][SQL Server]Statment 'Contrato de Servicio' (OCTR)(s) could not be prepared.

when i'm working in SQL Server the qry run fine, but when i run in SAP, i receive the error before mentioned.

the Qry is this:

DECLARE @FechaIni datetime,	
        @FechaFin datetime,
        @FechaAct datetime

SET @FechaAct = GETDATE()
SET @FechaIni = [%0]
SET @FechaFin = [%1]

CREATE TABLE #Libo_Mayor_CS (
                                CodCta nvarchar(max),
                                CtasPadre nvarchar(max),
                                NbreCta nvarchar(max),
                                Nivel numeric(1),
                                Rubro numeric(1),
                                SldoInicial numeric(19,6),
                                Debe numeric(19,6),
                                Haber numeric(19,6),
                                SldoFinal numeric(19,6)
                             );
                               
INSERT INTO #Libo_Mayor_CS

SELECT T0.[AcctCode],
       CASE 
           WHEN T0.[Levels] <=3 THEN T0.[AcctName]
           ELSE  ''
       END,
       '',
       T0.[Levels],
       T0.[GroupMask],
       0.00,
       0.00,
       0.00,
       0.00                                           
FROM OACT T0
	LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
WHERE T0.[Levels] BETWEEN 1 AND 3 AND 
      T0.[GroupMask]   BETWEEN 1 AND 5
GROUP BY T0.[AcctCode],
         T1.[Account],
         T0.[Levels],
         T0.[AcctName],
         T0.[GroupMask]

INSERT INTO #Libo_Mayor_CS

SELECT T0.[AcctCode],
       '',
       CASE 
           WHEN T0.[Levels] >3 THEN T0.[AcctName]
           ELSE  ''
       END,
       T0.[Levels],
       T0.[GroupMask],
       CASE
           WHEN T0.GroupMask  = 1 THEN (select t11.[CurrTotal]+sum(t10.[Debit])-sum(t10.[credit]) from oact t11 left join jdt1 t10 on t11.[AcctCode] = t10.[Account] where t10.[RefDate] between @FechaIni and @FechaAct and t11.[AcctCode] = T0.[AcctCode] group by t11.[CurrTotal])
           WHEN T0.GroupMask  = 2 THEN (select t11.[CurrTotal]-sum(t10.[Credit])-sum(t10.[Debit]) from oact t11 left join jdt1 t10 on t11.[AcctCode] = t10.[Account] where t10.[RefDate] between @FechaIni and @FechaAct and t11.[AcctCode] = T0.[AcctCode] group by t11.[CurrTotal])
           WHEN T0.GroupMask  = 4 THEN (select t11.[CurrTotal]+sum(t10.[Debit])-sum(t10.[credit]) from oact t11 left join jdt1 t10 on t11.[AcctCode] = t10.[Account] where t10.[RefDate] between @FechaIni and @FechaAct and t11.[AcctCode] = T0.[AcctCode] group by t11.[CurrTotal])
           WHEN T0.GroupMask  = 5 THEN (select t11.[CurrTotal]-sum(t10.[Credit])-sum(t10.[Debit]) from oact t11 left join jdt1 t10 on t11.[AcctCode] = t10.[Account] where t10.[RefDate] between @FechaIni and @FechaAct and t11.[AcctCode] = T0.[AcctCode] group by t11.[CurrTotal])
       END,
       SUM(T1.[Credit]),
       SUM(T1.[Debit]),
       0.00                                           
FROM OACT T0
	LEFT JOIN JDT1 T1 ON T0.[AcctCode]  = T1.[Account]
WHERE T0.[Levels] > 3 AND
      T0.[GroupMask]  BETWEEN 1 AND 5 AND
     (T1.[RefDate] between @FechaIni AND @FechaFin)
GROUP BY T0.[AcctCode],
         T1.[Account],
         T0.[Levels],
         T0.[AcctName],
         T0.[GroupMask]
                 
SELECT A.[CodCta] 'Codigo de Cuenta',
       A.[CtasPadre] 'Cuentas Padre',
       A.[NbreCta] 'Nombre de Cuenta',
       A.[Nivel] 'Nivel',
       A.[Rubro] 'Rubro',
       A.[SldoInicial] 'Saldo Inicial',
       A.[Debe] 'Debito',
       A.[Haber] 'Credito',
       CASE 
           WHEN A.[Rubro] = 1 THEN (A.[SldoInicial] + A.[Debe] - A.[Haber])
           WHEN A.[Rubro] = 2 THEN (A.[SldoInicial] + A.[Haber] - A.[Debe])
           WHEN A.[Rubro] = 4 THEN (A.[SldoInicial] + A.[Debe] - A.[Haber])
           WHEN A.[Rubro] = 5 THEN (A.[SldoInicial] + A.[Haber] - A.[Debe])
           ELSE 0
       END 'Saldo'
FROM #Libo_Mayor_CS A
ORDER BY A.[CodCta]

DROP TABLE #Libo_Mayor_CS

if assign the value to variables directly the Qry run in SAP but when i change the comodin to capture the variables value of SAP , there is when the problem ocurre

DECLARE @FechaIni datetime,	
                @FechaFin datetime,
                @FechaAct datetime

SET @FechaAct = GETDATE()
SET @FechaIni =  '20110601'
SET @FechaFin = '20110630'

i wait for your recomendations

Edited by: AlexMeza on Jun 15, 2011 1:29 AM

[agustin_marcoscividanes · Accepted solution]

Hi

you have to write this before declaring the SQL variables:

/* SELECT T0.[refdate] FROM JDT1 T0 */

DECLARE @FechaIni DATETIME

You can check this [thread|;

Kind regards.

Agustin

[Former Member · Accepted solution]

thank all for your replies, helped me to understand better the DECLARE statement.

Gracias Agustin, in the thread that you recomend me i find the answer.

The statement DECLARE to me is:

/*SELECT FROM [DBO].[JDT1] T0*/
DECLARE @FechaIni datetime
/*WHERE*/
SET @FechaIni = /*T0.[RefDate]*/ '[%0]'

/*SELECT FROM [DBO].[JDT1] T1*/
DECLARE @FechaFin datetime
/*WHERE*/
SET @FechaFin = /*T1.[RefDate]*/'[%1]'

/*SELECT FROM [DBO].[JDT1] T2*/
DECLARE @FechaAct datetime
/*WHERE*/
SET @FechaAct = /*T2.[RefDate]*/ GETDATE()

[Former Member]

Hi all !

Sorry because i dont know where post new.

My issue, when i using querry manager to call store in Database like this

Declare @Fromdate Datetime

Declare @Todate Datetime

Declare @AcctCode nvarchar (20)

set @Fromdate = (select distinct (T1.RefDate) from OJDT T1 where T1.RefDate = '[%0]')

set @Todate = (select distinct (T2.RefDate) from OJDT T2 where T2.RefDate = '[%1]')

set @AcctCode = (select distinct(T3.AcctCode) from OACT as T3 where T3.AcctCode ='[%2]' )

exec [USP_RPT_FI_GENERALLEDGER] @Fromdate, @Todate, @AcctCode

and i run this querry

this error:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.

Please help me fix this issue

Regards!

John Le.

[former_member204969]

If you want the system ask you for a parameter you should use a Select statement with a table known by the system. It may be a system or a user defined table.

But the system uses this table only for showing the existing values and for the prepare phase. So such a select may be inside a comment, and you can use the entered value for anything,

You can use e.g. the next in front of your set statements:

/*Select j.Refdate from OJDT j 
  where j.Refdate=[%0] and j.Duedate=[%1]*/

The problem can be the name of the parameter which the system asks. If you want this name to define, you can create and use inside the comment an UDT (even without data) with the appropriate field names.

[kvbalakumar]

Hi AlexMeza,

Try this

DECLARE @FechaIni datetime,	
        @FechaFin datetime,
        @FechaAct datetime
 
SET @FechaAct = GETDATE()
SET @FechaIni = /* Select T0.DocDate from OINM T0 where T0.DocDate = */ [%0]
SET @FechaFin = /* Select T1.DocDate from OINM T1 where T1.DocDate = */ [%1]

Regards,

Bala