Error while executing the the SQL on top of Analytical view

[Former Member]

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

[sreehari_vpillai · Accepted solution]

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

[Former Member · Accepted solution]

Hi Sreehari,

Thanks for your reply.

The Having clause is used when we have a aggregated function in the where clause but I don't have an aggregated function in my where clause hence there is no need to use the Having clause.

Please advice. Thank you!!

-G

[former_member182302 · Accepted solution]

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

[sreehari_vpillai · Accepted solution]

Hi,

I had not noticed that before.

Can't you try they filter "a. col3_date_field > a. col4_date_field" in the data foundation itself if possible ?

Sreehari

[Former Member · Accepted solution]

Hi Sreehari,

Thank you for your reply.

Do you know how to do that filter ?

when i tried to apply filter , I am getting the popup window with operator and values of the same column. Hence how can I use filter  for values from another column??

Please advice!!. Thank you

-G

[Former Member · Accepted solution]

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

[former_member182302 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Krishna,

Thank you

-G