Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5229
  • Last Modified:

SAP Business One SQL Query

I need to write a query in SAP B1 which will list all the Invoices which do not have a particular item code at row level.

What I want to see is all Invoices which do not have item code C&P in any of the rows.

I have tried something like this:

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] NOT Like 'C&P%%'

Unfortunately this brings up every row in every invoice  so if I try and drill down into some orders there are entries for C&P.

I am not used to using SQL but can write some basic queries.  I would normally ask our SAP B1 support company to do something like this but they'll only tell me it is bespoke work and therefore chargeable which I don't have the budget for right now!

0
JonYen
Asked:
JonYen
  • 3
  • 2
1 Solution
 
Daniel WilsonCommented:
drop the 2nd % sign & try again

NOT like 'C&P%'
0
 
JonYenAuthor Commented:
this did the same thing -  the query is returning every item row where it is not 'C&P', but this doesnt help because when I drill down into most of the invoices they do have C&P; it just doesnt show up on the query results.

0
 
Daniel WilsonCommented:
OK ... I think I understand.

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName] FROM OINV T0  
Where not exists(Select T1.Inv1 From INV1 T1 ON T0.DocEntry = T1.DocEntry AND T1.[ItemCode] Like 'C&P%')

Open in new window

0
 
JonYenAuthor Commented:
I get error message 'Incorrect Syntax near keyword ON'
KH002.jpg
0
 
Daniel WilsonCommented:
Oops, sorry!

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName] FROM OINV T0  
Where not exists(Select T1.Inv1 From INV1 T1 WHERE T0.DocEntry = T1.DocEntry AND T1.[ItemCode] Like 'C&P%')

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now