Navision range .. convert to sql statement

Business Central/NAV User Forum
1

Hi guys …I need to ask u guys how do i write an sql statement which is dynamic to produce the same result as this operator … in navision? i tried to surf the net but couldn’t find the right answer…wish u guys can lead me …I’m doing a .NET project integration with Navision 5.0 SP1…

Example : Say we have a table name “User Setup” and a column named “Purch RC Filter” in that table…some of the value in the column contains :-

KLG-1…KLG-99

BP-1…BP-99

AND

We have another table named “Customer” and a column named “Resp. Center” and in this column the values are:-

KLG1

KLG2

BP1

BP2

The question is : How do i write my sql statement to fetch all those data in “Customer” based on the value “Purch RC Filter” ? I can use “BETWEEN” , but then i need the sql statement to be dynamic as i dont wanna hardcode the value of “Purch RC Filter”…

2

Maybe something like this could work:

declare @purch_rc_filtervarchar(30)
set @purch_rc_filter = ‘KLG-1…KLG-99’ – test
– select @purch_rc_filter = [Purch RC Filter] from [dbo].[Company$User Setup] where [User ID] = suser_name() – real

declare

select

@query – test
– exec (@query) – real@queryvarchar(1000)
set @query =
‘SELECT * FROM [dbo].[Company$Customer] WHERE [Resp. Center] BETWEEN ‘’’ + replace(@purch_rc_filter, ‘…’, ‘’’ AND ‘’’) + ‘’’’

Hope this could help you.
Cheers,
Jörg

3

sorry about that annoying formatting … [+o(]