Declare Temp table query error

[Former Member]

Hi experts,

I try the below query, whereas the below error is coming. thanks to help me to fix this.

Error:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '10'. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Unclosed quotation mark after the character string ' as 'GR'     from @COUNTRY     where BPGp NOT IN  'User-Defined Values' (CSHS) ('102')     GROUP BY AcctCode,AcctName'. 3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ' as 'GR'     from @COUNTRY     where BPGp NOT IN ('102')     GROUP BY AcctCode,AcctName'. 4). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

Query

------------------------------------------------------------------------------------------------------------

-- Company -- SALES by Business Unit --------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

-- Version 1

-- DWP query

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

-- CREATION OF TEMPORARY TABLE

DECLARE @COUNTRY TABLE (

DocEntry nvarchar(30),

DocDate DATETIME,

AcctCode nvarchar(15),

AcctName nvarchar(100),

LineTotal numeric,

Total numeric,

Country nvarchar(50),

BU nvarchar(8),

Area nvarchar(8),

DocType nvarchar(50),

DocNum nvarchar(30),

SocID nvarchar(3),

BP nvarchar(15),

BPName nvarchar(100),

BPGp nvarchar(15),

Slp nvarchar(50),

GR numeric

)

INSERT INTO @COUNTRY

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

-- SELECTION FOR TEMPORARY TABLE

SELECT

T0.DocEntry, T0.DocDate,

T0.AcctCode, T4.AcctName,

(T0.[LineTotal] - T0.[LineTotal]*T1.DiscPrcnt/100), 0 as 'Total',

case when (T1.PayToCode=T6.Address) then T7.Name else T8.Name end as 'Country',

T0.OcrCode as 'BU', T0.OcrCode2 as 'Area', 'Sales Order',

T1.DocNum,

T5.U_SOCInterCoID,

T1.CardCode,

T1.CardName,

T5.GroupCode,

T9.Memo,

T10.TrgetEntry

-- SOURCES

FROM RDR1 T0

INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OACT T4 ON T0.AcctCode = T4.AcctCode

INNER JOIN OCRD T5 ON T1.CardCode = T5.CardCode

LEFT JOIN CRD1 T6 ON T6.Address = T1.PayToCode AND T6.CardCode = T1.CardCode AND T6.AdresType = 'B'

LEFT JOIN OCRY T7 ON T7.Code = T6.Country

LEFT JOIN OCRY T8 ON T8.Code = T5.Country

LEFT JOIN OSLP T9 ON T9.SlpCode = T1.SlpCode

LEFT JOIN DLN1 10 ON T10.BaseEntry= T0.DocEntry

-- CRITERIA

WHERE T1.[DocDate] >= '[%1]' and  T1.[DocDate] <= '[%2]' -- Date range as parameter

and T0.OcrCode = '[%0]' -- The AREA as the parameter

AND LEFT(T4.AcctCode,1) = '4' -- Revenue account ONLY (code starts by 4)

AND T4.ActType = 'I' -- only income account

-- For canceled sales order

AND T1.CANCELED = 'N'

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

-- EXTRACTION OF DATA

------------------------------------------------------------------------------------------------------------

-- Extract all data

SELECT * from @COUNTRY

------------------------------------------------------------------------------------------------------------

-- by GL ACCOUNT

------------------------------------------------------------------------------------------------------------

UNION ALL

-- Total GL Account

SELECT

'TOTAL GL Account' as 'DocEntry',

'' as 'DocDate',

AcctCode as 'AcctCode',

AcctName as 'AcctName',

0 as 'LineTotal',

sum(LineTotal) as 'Total',

'' as 'Country',

''as 'BU' ,

''as 'Area',

''as 'DocType',

''as 'DocNum',

'' as 'SocID',

'' as 'BP',

'' as 'BP Name',

''as 'BP Group',

'' as 'Sales',

" as 'GR'

from @COUNTRY

where BPGp NOT IN ('102')

GROUP BY

AcctCode,AcctName

Thanks in advance,

Regards,

Dwaraka

[Johan_H · Accepted solution]

Hi Dwaraka,

As Shachar said, there is a small syntax error in the JOIN satement, but the error is caused by this:

" as 'GR'  <-- these are not two single quotation marks, but one double quotation mark. It looks the same, but it is not the same.

Shachar's points 2 and 3 are incorrect in your case.

Regards,

Johan

[Former Member]

Hi

you have some problem in the query:

1. in the select query (after insert INTO @COUNTRY ) you have to define

left  JOIN DLN1 T10 ON T10.BaseEntry= T0.DocEntry

instate of left  JOIN DLN1 T10 ON 10.BaseEntry= T0.DocEntry

you wrote only 10 and you need T10 in the alias

2. table with @ in the start need [ ]  -  you have to write [@COUNTRY]

3 it is better to define UDT within the SAP instead of in query - it will prevent future problem wile upgrading the software

shachar