Skip to Content
avatar image
Former Member

Declare Temp table query error

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 04, 2015 at 09:53 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 04, 2015 at 09:36 AM

    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

    Add comment
    10|10000 characters needed characters exceeded