Handle SQL Query in NAV

Andwian

Dear all,

I heard that in other ERP system, the developer must code in the SQL (perhaps the SQL Query? Not sure), beside in the application itself.

My questions are:

How can we not need to code in SQL set aside?
How we handle this SQL query in NAV?

I am curious of these thing keep in my mind.

Thank you very much!

bangswit

do you mean to join 2 tables in 1 forms?
using function...

Andwian

bangswit wrote:

do you mean to join 2 tables in 1 forms?
using function...

I am not sure. However, I know that they have to code in the SQL, beside in the application itself (e.g. NAV).
I just want to know, what are they doing in SQL? and why we do not need to do that when we using NAV?

bangswit

you can do the query in SQL
for example you want to join 2 tables
and then create 1 view (in SQL)
create 1 table (in NAV)... the structure must be the same with the view in SQL that you made before
and the set it up... in properties (little bit forgot)
so you can show the query from SQL

Sunset

The NAV application translates all commands to SQL statements for you, and then executes them

Andwian

bangswit wrote:

you can do the query in SQL
for example you want to join 2 tables
and then create 1 view (in SQL)
create 1 table (in NAV)... the structure must be the same with the view in SQL that you made before
and the set it up... in properties (little bit forgot)
so you can show the query from SQL

I am sorry, I am still unable to understand this :oops:
What is "join 2 table"? In what scenario we need this?
Do we need to "query" in NAV? Since I never do the query in NAV, nor in SQL.

bangswit

join 2 tables
--> using inner join in SQL

SELECT A.A1,B.B2
FROM A
INNER JOIN B
ON A.A1=B.B1

something like this

Andwian

Sunset wrote:

The NAV application translates all commands to SQL statements for you, and then executes them

Perhaps this is the answer I looking for.

Is it done automatically in background process, and we could not see even read the SQL query?

bangswit wrote:

join 2 tables
--> using inner join in SQL

SELECT A.A1,B.B2
FROM A
INNER JOIN B
ON A.A1=B.B1

something like this

My apologies Bangswit, I do not understand the SQL Query language at all :oops:
I will look for it and learn it
Thanks anyway!

stryk

Well, besides "Views" and "Linked Objects" one could use MS ADO to execute any SQL statement.
See http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx about details.

DenSter

Andwian wrote:

Sunset wrote:

The NAV application translates all commands to SQL statements for you, and then executes them

Perhaps this is the answer I looking for.

It is the answer. As a C/AL developer, you don't need to know SQL. You write C/AL code, and as that is executed, the NAV client generates the SQL statements at run time behind the scenes.

vaprog

Dynamics NAV was originally developed around the native database server. The native database is not an SQL database, so SQL was never needed. Instead it supports an API which closely resembles the C/AL functions like GET, FIND, SETFILTER, NEXT.

Later on, the decision was made, to support Microsoft SQL Server as an alternative database. In order to not break all the legacy C/AL code (and probably for more reasons), the decision was made to let the NAV SQL database driver emulate the native (or C/AL) commands rather than adding an SQL engine to the native database. Later on, some new C/AL functions were introduced to better utilise the strengths of SQL: working with sets of data rather than working with individual records, as the native database is best fit for. These commands beeing FINDSET, FINDFIRST, FINDLAST.

If you want to see the SQL commands NAV sends to MSSQL, have a look at the client monitor.