我有一个SQL查询,该查询接受用于过滤报告的参数($ {p_year,$ {p_month},$ {p_comp}(在pentaho报告设计师中。
[在使用视图之前,在Pentaho报告设计师中]
SELECT INSERT_DATE,EXT,DURATION_S
FROM TABLE1
WHERE CONVERT(VARCHAR(6),INSERT_DATE,112) = CONCAT(${p_year}, ${p_month})
AND DIRECTION = 'OUT'
AND EXT IN (
SELECT DISTINCT TABLE2.EXT
FROM TABLE2
WHERE ((COMP = ${p_comp} OR 'ALL' = ${p_comp}))
)
我正在尝试创建视图,以在SQL Server中包含该查询。但是,我不确定这是正确的视图。[在SQL Server中]
CREATE VIEW CALLING AS
SELECT INSERT_DATE,EXT,DURATION_S
FROM TABLE1
WHERE DIRECTION = 'OUT' AND EXT IN (
SELECT DISTINCT TABLE2.EXT
FROM TABLE2
)
之后,在报告设计师中,我将代码更改为
select * from CALLING
但是,我不知道如何将参数($ {p_year},$ {p_month},$ {p_comp}(添加到新代码中。任何想法,请建议。
您的当前视图似乎对我来说是正确的,但在VIEW
JOIN
CREATE VIEW Calling
AS
SELECT
t1.INSERT_DATE, t2.EXT, t1.DURATION_S, t2.COMP
FROM table1 t1
INNER JOIN table2 t2 ON t2.EXT = t1.EXT
WHERE t1.DIRECTION = 'OUT'
,您需要使用WHERE
子句
SELECT * FROM Calling
WHERE INSERT_DATE = @parameter AND COMP = @parameter
或者,您还可以使用存储过程,您可以通过多个参数