Skip to Content

How to use NOT EXISTS and JOIN at the same time?

I am using SAP Business One 9.3 With HDB

In Query Manager/Query Generatore, We are required to revise a report that display UID that is in table BOS_BARCODE_IT_LOG but not exists in table BOS_BARCODE_DO_LOG

The reason that I joined with OITM is because the user asked for the selection Criteria in SAP Business One.

SELECT X0."DATE",X0."ITEMCODE", X0."UID"FROM"BOS_BARCODE_IT_LOG" X0 JOIN OITM X1 ON

X0."ITEMCODE"= X1."ItemCode"WHEREX1."ItemCode"='[%0]'ANDNOT EXISTS(SELECT X2."UID"FROM"BOS_BARCODE_DO_LOG" X2WHERE X0."ITEMCODE"= X2."ITEMCODE"ANDX0."UID"= X2."UID"ANDX0."DATE"= X2."DATE")

The problem is that, when I tried to search any items there, the query returns no result (Even though the item exists in the item master data.

What I've tried.

I Select only from one table without OITM, and neverthless, did not provide Query Selection Criteria.

I Tried this as well

SELECT X0."DATE",X0."ITEMCODE", X0."UID"FROM"BOS_BARCODE_IT_LOG" X0

WHERE 
X0."ITEMCODE"='[%0]'ANDNOT EXISTS(SELECT X1."UID"FROM"BOS_BARCODE_DO_LOG" X1WHERE X0."ITEMCODE"= X1."ITEMCODE"ANDX0."UID"= X1."UID" ANDX0."DATE"= X1."DATE")

And it provide error.

1). [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near ")": line 14 col 1 'Received Alerts' (OAIB) (at pos 299)

I also tried a code from someone who answered my question from StackOverFlow. I can see that it provide the the desired result, but the problem is that it lacks of '[%0]' . So I followed his code with minor changes

Here is the code he suggested

select bi.uid
from bos_barcode_it_log bi
where exists(select 1 from oitm o where o.itemcode = bi.itemcode)and not exists(select 1 from bos_barcode_do_log bd where bd.uid = bi.uid)

Here is the code with minor changes

SELECT X0."DATE",X0."ITEMCODE", X0."UID"
FROM "BOS_BARCODE_IT_LOG" X0
WHERE 
EXISTS (SELECT X2."ItemCode" from OITM X2 WHERE X2."ItemCode" = X0."ITEMCODE")
AND NOT EXISTS (
SELECT X1."UID" FROM "BOS_BARCODE_DO_LOG" X1
WHERE X0."ITEMCODE" = X1."ITEMCODE"
)

I don't know where to put '[%0]'.

I tried putting additional AND X2."ItemCode" = '[0%]'" in the inner query (the EXISTS one) and it immediately provide blank result instead of providing the input selection criteria.

If I put in the WHERE X0."ITEMCODE" = '[0%]' Outer query, It also return the same result without prompting user to input like the picture below. It returns error on both case if I use '[%0]' but provides no Error with '[0%]'

Another thing, is it possible to use subquery with more than one tables and provide the expected result (Not just blank result)

What I learned in the tutorial that subquery only used for one table. This is one of the examples.

https://www.tutorialspoint.com/sql/sql-sub-queries.htm

Please help. Thank you very much.

1.png (32.3 kB)
3anik.png (49.2 kB)
hddft.png (37.1 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Aug 31 at 05:55 AM

    Hi,

    I am not sure if the same syntax works in HANA, but in MS SQL, whenever you need to use subqueries, you need to proxy the parameter. Like this in MS SQL:

    /* select * from OITM x */
    DECLARE @ItemCode AS NVARCHAR(30)
    SET @ItemCode = /* x.ItemCode */ '[%0]'
    SELECT *
    FROM OITM
    WHERE ItemCode = @ItemCode

    You can circumvent using EXIST and NOT EXIST, by using IN (again for MS SQL, don't know if it is the same for HANA):

    SELECT *
    FROM OITM
    WHERE ItemCode IN (select ItemCode from OITM where FrozenFor = 'N')
     AND NOT CardCode IN (select CardCode from OCRD where FrozenFor = 'N')

    Regards,

    Johan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Mario,

      That is correct, the B1 client has its own SQL parser that interprets several commands / syntaxes, and converts them to normal SQL parameters. This parser cannot interpret the standard [%0] parameter when it is used in a subquery. So for the purpose of more complicated queries, SAP has extended their parser with the comment syntax above. Basically it allows you to call any table inside the /* */, and then use fields from this table to show input fields for normal SQl parameters, which you can then use in your subqueries.

      This syntax should also work for HANA, but I suppose the syntax may be a little different.

      Regards,

      Johan

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.