Query Object in NAV - SQL "WHERE ... IS NULL"

norsenynorseny Member Posts: 19
edited 2019-03-20 in NAV Three Tier
Hi,

I have a huge amount of records in one table, the structure and sample data is more less like this:

ID | Name | Related ID
1 | Name1 | 2
3 | Name3 | 1
4 | Name4 | 3
5 | Name5 | 1
6 | Name6 | 2
etc.

I need to find only those records whose Related ID exists as ID in this table. So from the records above, the query should return 3, 4, 5. 1 and 6 should be excluded, because there is no record with ID = 2.

---
Edit (I noticed now the logical mistake in my description in sentence above):
I need to find only those records whose Related IDs do not exist as ID in this table. So from the records above, the query should return 1 and 6, because there is no record with ID = 2. Records 3, 4, 5 should be excluded.
---

In SQL it is quite simple:

SELECT t1.Name
FROM table1 t1
LEFT JOIN table1 t2 ON t1."Related ID" = t2.ID
WHERE t2.ID IS NULL

Is there a way to do it efficiently in NAV (maybe using query object?).
I am trying to avoid "IF NOT Table.GET("Related ID") THEN..." for every record, it would generate so many queries from NAV to DB.


Answers

  • vremeni4vremeni4 Member Posts: 323
    I think you can do this with one query , by using the Property DataItemLinkType and option
    SQL Advanced Options
    Then In SQLJoinType you can select "Inner Join"
    I would use then the totals=1 to get the correct entries.

    I hope this helps.
  • norsenynorseny Member Posts: 19
    edited 2019-03-20
    @vremeni4, thank you for the response!
    I however noticed that I made a mistake in my description above, I already edited it.

    So my task is the opposite of what you, @vremeni4, suggested. Have you got any idea how to solve this?

    For now I just loop through all the records and check each one with
    "IF NOT Table.GET("Related ID") THEN..."
  • lubostlubost Member Posts: 611
    NAV doesn't use NULL. Each NAV table must not use NULL. All columns in NAV table are filled with default value ('' for text, 1.1.1753 for date etc.).
  • norsenynorseny Member Posts: 19
    @lubost, thanks for the answer.
    I know, but it has nothing to do with what I ask about.
    The SQL condition WHERE t2.ID IS NULL in my case just helps finding the records that in "Related ID" field have the value that does not exist in the same table as "ID".
Sign In or Register to comment.