Query on G/L Entry including dimension value 4

Hugo4711

Hi,
I want to list all g/L entries including dim4 value. In order to include entries without dimensions I select "Use Default Values if No Match". But when I filter on "Dimension Code=FILTER(KONCERN)" it kind of overrides "Use Default Values if No Match" and leaves me without entries without dimensions. Any advice on how to solve this?

Slawek_Guzek

You cannot do that in Query I'm afraid.

The "Use Default Values if No Match" is an equivalent to LEFT JOIN

SELECT ... FROM "G/L Entry" 
LEFT JOIN Dimension Set Entry
  ON  "Dimension Set Entry"."Dimension Set ID" = "Dimension Set Entry"."Dimension Set ID"

the LEFT JOIN will return always all the entries from "G/L Entry"

What you need is something like this:

SELECT ... FROM "G/L Entry"  
INNER JOIN Dimension Set Entry
  ON  ("G/L Entry" ."Dimension Set ID" = "Dimension Set Entry"."Dimension Set ID")
    OR ("G/L Entry" ."Dimension Set ID" = 0) // G/L Entry with NO dimensions

The latter is not possible to achieve in NAV Query

The workaround would be to use query with INNER JOIN, and combine in code its output with entries taken directly from G/L Entry table, filtered to "Dimension Set ID" = 0

Slawek_Guzek

You cannot do that in Query I'm afraid.

The "Use Default Values if No Match" is an equivalent to LEFT JOIN

SELECT ... FROM "G/L Entry" 
LEFT JOIN Dimension Set Entry
  ON  "Dimension Set Entry"."Dimension Set ID" = "Dimension Set Entry"."Dimension Set ID"

the LEFT JOIN will return always all the entries from "G/L Entry"

What you need is something like this:

SELECT ... FROM "G/L Entry"  
INNER JOIN Dimension Set Entry
  ON  ("G/L Entry" ."Dimension Set ID" = "Dimension Set Entry"."Dimension Set ID")
    OR ("G/L Entry" ."Dimension Set ID" = 0) // G/L Entry with NO dimensions

The latter is not possible to achieve in NAV Query

The workaround would be to use query with INNER JOIN, and combine in code its output with entries taken directly from G/L Entry table, filtered to "Dimension Set ID" = 0

Hugo4711

Thanx, Will try that and feed back.

Hugo4711

Thanx again, Testad successfully.