SQL Query on NAV 2013

Hi Everybody

I read this article. And I find it very good with what I need.

https://community.dynamics.com/nav/b/arounddynamicsnavworld/archive/2016/07/27/nav-2013-sql-query-codeunit-tool

 

On SQL Query I edit :

DECLARE
	    @FromDate            DateTime,
        @ToDate              DateTime

SELECT SUM(Quantity) AS QuantityTot, [Item No_], [Location Code], [Lot No_], [Serial No_]
FROM dbo.[CRONUS Italia S_p_A_$Item Ledger Entry]
WHERE [Posting Date] BETWEEN @FromDate AND @ToDate 
AND [Item No_] LIKE '1%'
GROUP BY [Item No_], [Location Code],[Lot No_],[Serial No_]
HAVING ([Location Code]<>” AND (SUM(Quantity)) >0)

 

But when I add the variable I cannot link. Please help me solution.

Thanks so much

 

  • Can you please let me know what is the variable you added to your SQL query.
  • In reply to sbhatbng:

    Thanks you sbhatbng.

    txtSQL :='SELECT Quantity=SUM(Quantity), [Item No_], [Location Code]'+
             ' FROM dbo.[$company$Item Ledger Entry]'+
             ' WHERE CONVERT(nvarchar(10), [Posting Date], 101) BETWEEN ''FromDate'' AND  ''ToDate'''+
             ' AND [Location Code] <>''''' +
             ' AND Quantity <>0 '+
             ' GROUP BY [Item No_], [Location Code]'

    2 variable FromDate,ToDate.

    And on page i add 2 variable this

    But not work. Please help solution.

    Thanks very much

    nav2013sqlquery.zip

     

  • In reply to dinhson:

    You will have to say BETWEEN @FromDate AND @ToDate. Try this and it should work.
  • In reply to sbhatbng:

    Hello,

    You should make string concatenation.

    txtSQL :='SELECT Quantity=SUM(Quantity), [Item No_], [Location Code]'+
    ' FROM dbo.[$company$Item Ledger Entry]'+
    ' WHERE CONVERT(nvarchar(10), [Posting Date], 101) BETWEEN' + "FromDate" +' AND'
    "ToDate"+

    ' AND [Location Code] <>''''' +
    ' AND Quantity <>0 '+
    ' GROUP BY [Item No_], [Location Code]'

    Something like this.

    Best regards,
  • In reply to sbhatbng:

    Hi sbhatbng. It's not work

  • In reply to THomas Barbut:

    When run, it does not bug. But no results. I think it's the wrong date format.
    I have tried to select multiple date format, but not work.
  • In reply to dinhson:

    Sqltext:='Select * from table where date between'' + format (date1) +  ''and'' + format (date2);

    It wiuld be easier if you use text variables because of the single quotes.

    This is a working syntax. Afterwards you cand try the different formats.

    Best regards,

  • Hi Dinhson,
    May I ask you a question?
    Why are you doing it this way?
    I mean using a "tool" in which your users need to write SQL scripts to make it work.
    The tool you're using (think I've seen something similar on Mibuso) is great if you need to access data in other SQL databases. If you need users to see data inside Navision, then there are much easier and better ways to do it.
  • In reply to Erik P. Ernst:

    Thanks Erik reply.

    The exact tool I'm using is by Mibuso. I have applied it, but can not transfer variables from text boxes or Date to SQL script.
    When implementing this condition. On a SQL script already. Users only select filter conditions. And speed will really improve a lot of SQL script.

    Best Regards,
    Dinhson
  • In reply to dinhson:

    Yes, but why use it for accessing data inside NAV? Speed you say, but then use Query objects inside NAV instead.
Related