Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

must specify table to select from

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

Former Member
replied

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|Help for query that not work in SAP query generator.;

Kind regards.

Agustin

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question