0

a subquery is provided to create a view and the view should cater to dynamic month variable.

the subquery like this:

select col1,col2,col3,col4, sum(money) as KPI_single_month from table1 group by col1,col2,col3,col4 where month='202104'

-- month is not fixed, maybe 202103,202106

the view should be called like this:

select col1,col2,col3,col4,KPI from view1 where month='202104'

--month is not fixed, maybe 202103,maybe 202106

the key output of this view is KPI, which is the sum of KPI_single_month from 202101 to 202104. if month is 202106, then sum(KPI_single_month) from January to June(total 6 months).

so the difficulty of creating view is that the month variable is unknown, how many subquery should I sum in the SQL statement of creating view? Thanks!

fred wu
  • 55
  • 6
  • This should be handled in your reporting front end tool(s). You could do it in a stored proc, but (at least in my opinion) it's not worth the hassle. – Andrew Jun 25 '21 at 15:02
  • You might try to add `month` to the group by. Of course, you should check if the optimizer is capable of pushing the condition iinto the select *before* aggregation. – dnoeth Jun 25 '21 at 15:37

2 Answers2

0

Why are trying to create a view instead of a stored procedure to get the required sum You pass the month to the procedure and it create a temp table then query it or just ouput the sum

Mahmed Amer
  • 11
  • 1
  • 4
  • in my production environment, Stored Procedure need to be reviewed with restricted measures while view need not. – fred wu Jun 26 '21 at 01:46
0

In general it is not possible to pass parameters to the view (not sure about Teradata specific)

Sergey
  • 4,719
  • 1
  • 6
  • 11
  • I agree with you. My goal is not passing parameter to the view. – fred wu Jun 26 '21 at 01:46
  • anyone know if Teradata support passing parameter to view? In Oracle database, there is a feature of syscontext which can pass parameter to view. – fred wu Jun 26 '21 at 10:35
  • In Teradata, views do not allow parameters. Macros and Stored Procedures allow parameters and can return answer sets - but cannot be used in a FROM clause. Table Functions / Table Operators (C/C++ or Java code) can potentially accept parameters and return result sets that can be processed by an outer query but that's significantly more complex. – Fred Jun 28 '21 at 18:42