Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Dynamics NAV Query - L...
Microsoft Dynamics NAV (Archived)

Dynamics NAV Query - Left outer join using a constant

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I would like to create a NAV Query which joins "Purchase Header" > "Purchase Line" > Item, creating a Left Outer Join that would look like this:  

"Purchase Line".Type = 'Item' AND "Purchase Line"."No." = Item."No."

The DataItemLink helper seems only to be able to join on table fields, not on hard constants. Is there a way around this? I've tried using a statement in DataItemLink like Purchase_Line.Type='Item',No.=Purchase_Line."No." but this results in the following error: 

'_Line.Type='Item',No.=Purchase_Line."No." is not an option. The existing options are: =

*This post is locked for comments

  • Community Member Profile Picture
    Community Member on at
    RE: Dynamics NAV Query - Left outer join using a constant

    I am confused with this issue: I see in 2013 R2 there is indeed a "Left Outer Join" and a possible filter on either the table or the column itself.  I tried filtering for null and a value to get the left join... Can we officially confirm we cannot have an outer join with a column filter?

  • Community Member Profile Picture
    Community Member on at
    RE: Dynamics NAV Query - Left outer join using a constant

    Thank you. Unfortunate.

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    RE: Dynamics NAV Query - Left outer join using a constant

    Unfortunately you can't add filter conditions to the JOIN with the current Query design.
    The DataItemTableFilter (and SETRANGE) adds the filter condition to the SQL WHERE clause. This is by design see: Understanding Query Filters http://msdn.microsoft.com/en-us/library/hh169211(v=nav.70).aspx

    Good suggestion for a Query enhancement :-)

  • Community Member Profile Picture
    Community Member on at
    RE: Dynamics NAV Query - Left outer join using a constant

    Unfortunately it's as I expected. It doesn't work..

    A SQL trace clearly shows it's ending up in the where statement. 

    SELECT 
	TOP (1000) ISNULL("Purchase_Header"."No_",@2) AS "purchase_header_no",
	ISNULL("Purchase_Header"."Document Type",@3) AS "doc_type",
	ISNULL("Purchase_Header"."Buy-from Vendor No_",@2) AS "vendor_no",
	ISNULL("Purchase_Line"."Document Type",@3) AS "line_doc_type",
	ISNULL("Purchase_Line"."Buy-from Vendor No_",@2) AS "line_vendor_no",
	ISNULL("Purchase_Line"."Document No_",@2) AS "line_doc_no",
	ISNULL("Purchase_Line"."Line No_",@4) AS "line_line_no",
	ISNULL("Purchase_Line"."Type",@3) AS "line_type",
	ISNULL("Purchase_Line"."No_",@2) AS "line_no",
	ISNULL("Item"."No_",@2) AS "item_no",
	ISNULL("Item"."Description",@5) AS "item_descr" 
FROM 
	"db".dbo."Company$Purchase Header" AS "Purchase_Header" 
	WITH(READUNCOMMITTED)  
	JOIN "db".dbo."Company$Purchase Line" AS "Purchase_Line" 
	WITH(READUNCOMMITTED)  ON ("Purchase_Line"."Document Type"="Purchase_Header"."Document Type" AND "Purchase_Line"."Document No_"="Purchase_Header"."No_") LEFT OUTER JOIN "db".dbo."Company$Item" AS "Item" 
	WITH(READUNCOMMITTED)  ON ("Item"."No_"="Purchase_Line"."No_") 
WHERE 
	("Purchase_Header"."Document Type"=@0 AND "Purchase_Header"."No_"=@1) 
	AND (ISNULL("Purchase_Line"."Type",@3)=@6) 
ORDER BY "doc_type" ASC,"purchase_header_no" ASC,"line_doc_type" ASC,"line_doc_no" ASC,"line_line_no" ASC,"item_no" ASC 
OPTION(OPTIMIZE FOR UNKNOWN, FAST 50, FORCE ORDER, LOOP JOIN)

exec sp_execute 12,@0=1,@1=N'IOR00026',@2=N'',@3=0,@4=0,@5=N'',@6=2


  • keoma Profile Picture
    keoma 32,675 on at
    RE: Dynamics NAV Query - Left outer join using a constant

    you will see ...

  • Community Member Profile Picture
    Community Member on at
    RE: Dynamics NAV Query - Left outer join using a constant

    That sounds like it would have the same effect as setting the DataItemTableFilter, but I will give it a try on monday.

  • keoma Profile Picture
    keoma 32,675 on at
    RE: Dynamics NAV Query - Left outer join using a constant

    edit c/al code (F9)

    write under trigger OnBeforeOpen()

    SETRANGE(Type,Type::Item);

  • Community Member Profile Picture
    Community Member on at
    RE: Dynamics NAV Query - Left outer join using a constant

    This does not work, because DataItemTableFilter is like using the where statement. That would cause filtering out of Purchase Line rows, effectively make the join an inner join instead of an outer join. I want all of the purchase line rows. But only join Item to the purchase line rows where type=item.

    Or perhaps i'm doing something wrong, ofcourse ;)

  • keoma Profile Picture
    keoma 32,675 on at
    RE: Dynamics NAV Query - Left outer join using a constant

    use property DataItemTableFilter to filter for constant values.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,409 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans