nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

[Microsoft][ODBC SQL Server Driver][SQL Server]'LISTAGG' is not a recognized built-in function name

2838447

Dears

kindly i face an issue when use the following formula with SQL DB

cast(EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"AS_INT_REQUEST_DIM"."REQUEST STATUS",',',"AS_INT_REQUEST_DIM"."REQUEST STATUS") as char(500))

it return

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 16001] ODBC error state: 37000 code: 8180 message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.. [nQSError: 16001] ODBC error state: 37000 code: 195 message: [Microsoft][ODBC SQL Server Driver][SQL Server]'LISTAGG' is not a recognized built-in function name.. [nQSError: 16002] Cannot obtain number of columns for the query result. (HY000)

SQL Issued: SELECT 0 s_0, cast(EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Intranet"."AS_INT_REQUEST_DIM"."REQUEST STATUS",',',"Intranet"."AS_INT_REQUEST_DIM"."REQUEST STATUS") as char(500)) s_1 FROM "Intranet" WHERE ("AS_INT_REQUEST_DIM"."REQUEST STATUS" IN ('Closed', 'Completed')) FETCH FIRST 65001 ROWS ONLY

while it work with oracle DB

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi,

LISTAGG is an Oracle DB function, and when you use EVALUATE or EVALUATE_AGGR you are bypassing the OBIEE logical SQL layer and OBIEE will send your piece of code directly to your physical database, so it's up to you to make sure you are going to use a piece of code in these 2 functions that your DB can understand (so if your DB is SQL Server don't even try to send to it an Oracle function or you get the same result you are having now).

So everything works as expected ...

[Deleted User]

Microsoft and Oracle are two different companies with two different dialects of SQL. You can't just take Oracle SQL and think it will work on MSSQL.

Also: Why not just use google? 1.2 seconds to find this:

http://stackoverflow.com/questions/24810262/how-to-convert-oracle-listagg-function-to-sql-server-using-stuff

http://stackoverflow.com/questions/15477743/listagg-in-sqlserver

whereas it took you about a minute to write this post and then wait half an hour for answers. It's always a clever idea to think and search first and only then ask your question on a proper basis with a valid background.

2838447

dears

kindly i cannot find SQL formula replace this

[Deleted User]

Did you see that I posted two links? Did you read what's written there? Do you understand that there just isn't a 1:1 replacement for LISTAGG unless you write one?

2838447

Dear Christian

kindly i read your posted links

and i know LISTAGG not work on SQL

but i do not have a knowledge on SQL server and also i need some thing on OBIEE level not on database level

thank you

Gianni Ceresa

Mmmhhh, your original solution wasn't in OBIEE but was at the database level (as said EVALUATE and EVALUATE_AGGR bypass the logical SQL layer in OBIEE and is sent to your physical database).

So you must solve it "at the database level" to get the right syntax you can then use in OBIEE ....

[Deleted User]

+1 to Gianni: EVALUATE is precisely NOT doing things in OBIEE! Official documentation:

http://docs.oracle.com/middleware/1221/biee/BIVUG/GUID-7035040C-BB40-4392-920A-9A435593F659.htm#BILUG683

Evaluate functions are database functions that can be used to pass through expressions to get advanced calculations.

Evaluate: Passes the specified database function with optional referenced columns as parameters to the database for evaluation.

There is no such thing as LISTAGG inside OBIEE. You will have to solve the issue inside MSSQL

[Deleted User]

@2838447 Is your question still open or what are you going to do about this? Currently the thread is marked as "Not answered" and with the current approach you won't find any solution to this as we stated above. Can you please close the thread?

2838447

hello

i will check with DBA what they can do for this issue and feedback

thank you

Upcoming Event

Analytics - Fusion Data Intelligence: Semantic Model Extensions in FDI: Implementation Patterns, Security, and Best Practices
Build on the foundational concepts from Part 1 with a deeper look at implementation patterns for extending the FDI semantic model. This session covers common use cases such as modifying subject areas, creating custom subject areas, combining external application models, and supporting custom security requirements. It also…

Top Leaders