SQL SELECT Statements for NAV tables

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!.