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