Handle SQL Query in NAV

Andwian
Andwian Member Posts: 627
edited 2010-08-13 in NAV/Navision Classic Client
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!
Regards,
Andwian

Comments

  • bangswit
    bangswit Member Posts: 265
    do you mean to join 2 tables in 1 forms?
    using function...
  • Andwian
    Andwian Member Posts: 627
    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?
    Regards,
    Andwian
  • bangswit
    bangswit Member Posts: 265
    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
    Sunset Member Posts: 201
    The NAV application translates all commands to SQL statements for you, and then executes them
    Don't just take my word for it, test it yourself
  • Andwian
    Andwian Member Posts: 627
    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.
    Regards,
    Andwian
  • bangswit
    bangswit Member Posts: 265
    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
    Andwian Member Posts: 627
    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. :D

    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!
    Regards,
    Andwian
  • stryk
    stryk Member Posts: 645
    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.
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • DenSter
    DenSter Member Posts: 8,307
    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. :D
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • vaprog
    vaprog Member Posts: 1,163
    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.

Categories