on 06-04-2014 6:31 PM
Hi all,
below is the generic pattern of the query I am using on my Analytic view,
Select a. col1, a.col2, a. sum(col3)
from _SYS_BIC.SIMPLE_AN_VW a
where a. col3_date_field > a. col4_date_field and
a. col5<=15
group by a.col1,a.col2;
I am getting the following error
Could not execute '
Select a. col1, a.col2, a. sum(col3) from _SYS_BIC.SIMPLE_AN_VW a where ...' in 43 ms 446 µs .
SAP DBTech JDBC: [7]: feature not supported: not allowed over OLAP VIEW : search without aggregation or grouping by expression other than view column
when I removed the where clause 'a. col3_date_field > a. col4_date_field' it is working fine.
I know that this error occurs when there is no group by clause but I have the group by clause but I am still getting the error.
Does any one have any idea on why this is happening??
Please advice!! thank you!!
-Gayathri
Hi Gayathri,
You may use HAVING clause instead of applying where clause on aggregated fields.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE
column_name operator value
GROUP BY column_name
HAVING
aggregate_function(column_name) operator value;
Sreehari
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Reason:
If you keep those extra columns in the select clause and group by clause then the query will work.
You can try the below options:
1) Keep those 2 columns in the select clause and have a outer query to remove the unwanted columns later.
2)Can you try adding a dummy calculated column and activate the model and check the same query if it is working fine?
3) Also if the where condition is static , can you keep them as static filters in the model?
Regards,
Krishna Tangudu
Hi Krishna,
Thank you for the reply.
Option #2 :I added a dummy calculate column and the query just ran successfully.the calculated column is just a column with hard-coded value.
Do you by any chance know the reason why this works??
option #3 : Do you know how to do that filter ?
the static filters can be applied if we have a know value like col1>50 but how can I apply them to filters such as col 1>col2
Please advice!!. Thank you
-G
Hi Gayathri,
Option 2:
Check in _SYS_BIC on how many views got generated for this analytic view. You will see one with /OLAP and another one without it.
When you added the calculated column it will generate one more view other /OLAP in the database, the other view is similar to a calc view wrapper. Hence after addition of calculated column, the select query is trying to invoke the other column view and thereby you are breaking that limitations caused due to /OLAP view
Option 3:
You cannot apply like that. Instead create a calculated column: if(col1>col2,1,0)
then apply filter on calculated column = 1 using variable.
Regards,
Krishna Tangudu
User | Count |
---|---|
81 | |
9 | |
9 | |
7 | |
7 | |
7 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.