Home › Leveraging SQL Server › SQL SELECT Statements for NAV tables

SQL SELECT Statements for NAV tables

Posted on by

Yikes!  It’s wonderful to have NAV data in SQL Server tables, but getting to this data using a SELECT statement can be enormously challenging because of the NAV data “nuances”.  Let’s check our major table with critical data, the G/L Entry table in the Cronus Canada company.  or CRONUS Canada, Inc_$G_L Entry.

LOVELY!

  • every table is prefixed by the company name.  Company names with spaces between words will cause further problems generating a SELECT statement
  • Names in NAV are not “SQL friendly”.  In NAV the table is “G/L Entry”.  In SQL it’s “G_L Entry”

Then when we actually start examining columns, we see the same naming issues plus a few additional nuances:

  • option fields such as Document Type in NAV are shown as “Payment” or “Invoice”, but in SQL are integer values
  • Boolean fields in NAV are Yes or No, but 0 or 1 in SQL
  • Flowfields are not real fields at all as their data is in an underlying detail table and requires a nested SELECT statement to see the value.

So a fairly simple SELECT statement becomes a slight adventure as shown below.  Note the need for brackets to handle the spaces and underscores to replace special characters.

SELECT [G_L Account No_]
,[Posting Date]
,[Document Type]
,[Document No_]
,[Description]
,[Amount]
,[Global Dimension 1 Code]
,[Global Dimension 2 Code]
FROM [NavR2Demo].[dbo].[CRONUS Canada, Inc_$G_L Entry]

SAVE YOURSELF THE HEADACHE!

Check out the Jet Pivotier product from Jet Reports for tools to build complex SQL SELECT statements in a jiff!.