Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
[Microsoft][ODBC SQL Server Driver][SQL Server]'LISTAGG' is not a recognized built-in function name
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
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 ...
0 -
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/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.
0 -
dears
kindly i cannot find SQL formula replace this
0 -
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?
0 -
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
0 -
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 ....
0 -
+1 to Gianni: EVALUATE is precisely NOT doing things in OBIEE! Official documentation:
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
0 -
@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?
0 -
hello
i will check with DBA what they can do for this issue and feedback
thank you
0
