I need to get parameters for a view , but since it has COUNT(DISTINCT DTA_HOR) I can not get the where external. Would it have any other way?
SELECT EX1 ,EX2, EX3, EX4,
(SELECT COUNT(DISTINCT DTA_HOR) FROM TB_1
WHERE EX1 = EX5 AND DTA_HOR >= '!!PARAM_DATA_INI!!!'
AND DTA_HOR <= '!!PARAM_DATA_FIM!!!') AS FREQUENCIA
FROM TB_2
WHERE DTA >= '!!PARAM_DATA_INI!!!' AND DTA <= '!!PARAM_DATA_FIM!!!' AND LOCAL = '!!PARAM_LOCAL!!!'
I need to receive parameters for a view,
I can not remember how to use parameters for a view ( view ). What is possible is to add clauses like WHERE and ORDER BY in the display call.
Here's the approach using function. I have not had the opportunity to test the suggested codes, but I hope there are no mistakes.Would it have any other way?
FUNCTION
To pass the values as a parameter, the suggestion is to use a inline table-valued function.
-- código #1 v2
CREATE FUNCTION fnFreq (@dataIni datetime, @dataFim datetime, @local xxx)
returns table
return
SELECT EX1 ,EX2, EX3, EX4,
(SELECT count(distinct DTA_HOR)
from TB_1 as T1
where T2.EX1 = T1.EX5
and T1.DTA_HOR >= @dataIni
and T1.DTA_HOR <= @dataFim) as FREQUENCIA
from TB_2 as T2
where T2.DTA >= @dataIni
and T2.DTA <= @dataFim
and T2.LOCAL = @local;
go
As the data type of the LOCAL column has not been entered, xxx must be replaced with the correct information.
To call the function, a form is
-- código #2
SELECT EX1, EX2, EX3, EX4, FREQUENCIA
from dbo.fnFreq (@PARAM_DATA_INI, @PARAM_DATA_FIM, @LOCAL) as F;
The variables to be passed as a parameter must be pre-declared and have values marked.