web
You’re offline. This is a read only version of the page.
close
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

I have the same question (0)
  • keoma Profile Picture
    keoma 32,729 on at

    use property DataItemTableFilter to filter for constant values.

  • Community Member Profile Picture
    Community Member on at

    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,729 on at

    edit c/al code (F9)

    write under trigger OnBeforeOpen()

    SETRANGE(Type,Type::Item);

  • Community Member Profile Picture
    Community Member on at

    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,729 on at

    you will see ...

  • Community Member Profile Picture
    Community Member on at

    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


  • Verified answer
    Community Member Profile Picture
    Community Member on at

    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

    Thank you. Unfortunate.

  • Community Member Profile Picture
    Community Member on at

    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?

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans