Skip to Content
0

column is invalid in the select list

Jul 07, 2017 at 02:20 PM

119

avatar image

Hi,

I'm working in BOBI 4.2 to create webi report based in univers that are connected to SQL Server 2014 database. Now i'm facing some issues when using sql analytic functions in my indicators.

In my test, I'm analysing the turnover by shop and month. When I add an indicator defined by SUM(xxx) over (partition by xxx) to my two demensions, I get a GROUP BY statement generated by the editor then when I refresh the webi I get the following error window ' column ... is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause'.

here's the sql:

SELECT RSCOM.HT_H_TRAV_CUMUL_MENS.MOIS_HT, SUM(RSCOM.HT_H_TRAV_CUMUL_MENS.CA) OVER (PARTITION BY (RSCOM.HT_H_TRAV_CUMUL_MENS.MOIS_HT) ), DWAT_DWH.DWAT_DWH.MAGASIN.CD_MAG FROM RSCOM.HT_H_TRAV_CUMUL_MENS INNER JOIN DWAT_DWH.DWAT_DWH.MAGASIN ON (RSCOM.HT_H_TRAV_CUMUL_MENS.CD_MAG_HT=DWAT_DWH.DWAT_DWH.MAGASIN.CD_MAG) WHERE ( RSCOM.HT_H_TRAV_CUMUL_MENS.MOIS_HT >= @Prompt('Entrer Mois Analyse Mensuelle par magasin :','N','Analyses au magasin\Analyse Mensuelle par Magasin\Mois Analyse Mensuelle par magasin',Mono,Free,Persistent,,User:0) AND DWAT_DWH.DWAT_DWH.MAGASIN.CD_MAG IN @Prompt('Entrer Code Magasin :','N','Magasin\Code Magasin',Multi,Free,Persistent,,User:1) )GROUP BY RSCOM.HT_H_TRAV_CUMUL_MENS.MOIS_HT, DWAT_DWH.DWAT_DWH.MAGASIN.CD_MAG

by the way, I don't get the error when I add to this same request the basic indicator that I add the over (partition by ) condition:

SELECT RSCOM.HT_H_TRAV_CUMUL_MENS.CA, RSCOM.HT_H_TRAV_CUMUL_MENS.MOIS_HT,SUM(RSCOM.HT_H_TRAV_CUMUL_MENS.CA) OVER (PARTITION BY (RSCOM.HT_H_TRAV_CUMUL_MENS.MOIS_HT) ), DWAT_DWH.DWAT_DWH.MAGASIN.CD_MAG FROM RSCOM.HT_H_TRAV_CUMUL_MENS INNER JOIN DWAT_DWH.DWAT_DWH.MAGASIN ON (RSCOM.HT_H_TRAV_CUMUL_MENS.CD_MAG_HT=DWAT_DWH.DWAT_DWH.MAGASIN.CD_MAG)WHERE ( RSCOM.HT_H_TRAV_CUMUL_MENS.MOIS_HT >= 201703 AND DWAT_DWH.DWAT_DWH.MAGASIN.CD_MAG IN (630,363) )GROUP BY RSCOM.HT_H_TRAV_CUMUL_MENS.CA, RSCOM.HT_H_TRAV_CUMUL_MENS.MOIS_HT, DWAT_DWH.DWAT_DWH.MAGASIN.CD_MAG

- The connexion parameters file "sqlsrv.prm" of the server have been edited. we added the defintion of SUM and other functions this way:

<Parameter Name="RISQL_FUNCTIONS">RANK,SUM,AVG,COUNT,MIN,MAX,RATIO_TO_REPORT,CUME_DIST,CORR,DENSE_RANK,FIRST_VALUE,LAST_VALUE,LAG,LEAD,NTILE,PERCENT_RANK,PERCENTILE_CONT,PERCENTILE_DISC,ROW_NUMBER,CORR,COVAR_POP,COVAR_SAMP,REGR_SLOPE,REGR_INTERCEPT,REGR_COUNT,REGR_R2,REGR_AVGX,REGR_AVGY,REGR_SXX,REGR_SYY,REGR_SXY,STDDEV,STDDEV_POP,STDDEV_SAMP,VAR_POP,VAR_SAMP,VARIANCE</Parameter>

..

..

<Function Group="False" ID="SUM_OVER" InMacro="False" Name="SUM OVER" Type="Numeric"> <Arguments> <Argument Type="Numeric"></Argument> <Argument Type="All"></Argument> <Argument Type="All"></Argument> </Arguments> <SQL>SUM($1) OVER (PARTITION BY ($2) ORDER BY ($3))</SQL> </Function>


Can someone explain please?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Basma Benmbarek Aug 17, 2017 at 09:28 AM
0

Hi,

I found the solution to this. It was related to my prm files. It wasn't well configured. I had to add the "OVER_CLAUSE" parameter in the prm file, plus all the the defintions of analytic functions I needed.

Share
10 |10000 characters needed characters left characters exceeded