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

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
141
Views
9
Comments
2838447
2838447 Rank 2 - Community Beginner

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

Answers

  • 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]
    [Deleted User] Rank 2 - Community Beginner

    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
    2838447 Rank 2 - Community Beginner

    dears  

    kindly i cannot find SQL formula replace this

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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
    2838447 Rank 2 - Community Beginner

    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

  • 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]
    [Deleted User] Rank 2 - Community Beginner

    +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]
    [Deleted User] Rank 2 - Community Beginner

    @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
    2838447 Rank 2 - Community Beginner

    hello

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

    thank you