CODEWITHSUNDEEP
sqlsapb1
Andrew Gray
Andrew Gray

Reputation: 47

Invalid Syntax Near The Word "FOR" (but no FOR)

I've written a piece of SQL which works perfectly inside MS SQL studio 2017 with no problems. I have brought this over to SAP B1 and get the error as below:

Invalid Syntax near the keyword "FOR"

However I can not see any "FOR" in my code which is a bit confusing as this does work inside MS SQL studio.

I have seen one mention where it is because I have FROM in the select FROM and FROM in the WHERE clause. I tried removing this from this WHERE however it duplicated my results in the query (May have been a join issue) but i don't feel this needs joining as it's not a direct link to it?

SELECT T1.[ItemCode] AS 'Item No'
  ,T1.[Dscription] AS 'Item Description'
  ,'' AS 'Price List'
  ,'' AS 'Primary Currency'
  ,T1.[DiscPrcnt] AS 'Discount'
  ,'' AS 'Source Price'
  ,T1.[Price] AS 'Price after Discount'
FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[DocNum] = '1207006'
AND T1.[DiscPrcnt] > 0
AND T1.[ItemCode] <> 'ONEOFF'
AND T1.[ItemCode] NOT IN (
    SELECT T2.[ItemCode]
    FROM OSPP T2
    WHERE T2.[CardCode] = 'C001174'
    );

Thanks for any help.

Kind Regards Andy

Upvotes: 1

Views: 403

Answers (2)

Fabiano Cores
Fabiano Cores

Reputation: 199

SAP Business One Recordset, right?

Do not use ";" at the end of the query... just that.

The recordset will aways concatenate "for output" in your queries (don't know why).

Upvotes: 2

Andrew Gray
Andrew Gray

Reputation: 47

I re-written this code from scratch and it appeared to work the second time round, it must have been some strange formatting error or something causing the problem. It is working now though.

thanks

Upvotes: 0

Related Questions