Passing a parameter from NAV into a SQL FUNCTION

Sinisty

Hello everyone,

first of all I want to apologize if this problem is already solved in another Thread. I couldn't find
any Posts neither discribing my problem nor giving a working answer for it.
Also I am sorry if this Thread is posted in a false area in the forum.

The situation:
In NAV I have a table which is linked to a view in Database1 of Server1. The view shows
a UNION of 2 tables of Database2 on Server2. The result of this UNION is displayed in 2 rows
("Name" and "Amount").

In NAV I do a filter on the row "Name" to get 1 record of the view corresponding to that filter. The
problem is that the view gets every record from Server2 first (it's about 1500 records at the moment)
and then filters those records with the passed filter. What I'm trying to do is to pass the filter
directly to the UNION in the SELECT-Statements on Server2 as a parameter so that the view only sums up 1 record
to increase its performance.

I already googled about passing parameters to a view and found out that a view can't get any
parameters. So I'm using a function with a parameter now which is called in the view.

The problem:
How can i pass a parameter from NAV into that function I created and is it possible to do that
in the view? I need the view to filter the table in NAV for statistical purposes.

Best regards,
Dirk

ara3n

I suggest to use ADO instead and run your statement with parameters Waldo has a how to use ADO.

Once the record set is return, you can then show or process the data in temporary form/table.

Sinisty

Thank you for the answer. ADO is a possible way, but is there still
any way I could solve that with a view? It's not that I don't want
to use ADO. We are trying to solve that

Do you (or anybody else) have any other suggestions?
I would be glad if there was a different way for that issue.