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 Analytics and AI

Child Item

I attempted to write a query using CTEs and subqueries, similar to those involving temporary tables

User_PWOBY

I attempted to write a query using CTEs and subqueries, similar to those involving temporary tables in SQL Server using manual query option in OAS. However, it resulted in an error stating: Unsupported SQL statements. Could you clarify if CTEs and subqueries are supported in the desktop version of Oracle Analytical Server, or provide a solution to execute these queries?

Find more posts tagged with

Connection

Dashboard

Oracle Analytics Server

SQL Server

Accepted answers

All comments

Ram-Oracle

@User_PWOBY Please clarify the database for which the query is sourced to?

in the query Is there a with clause ?

please note down the credentials configured to run this manual query at dataset and open sql developer or toad and create a sql developer connection and check if the whole query is executing without any errors?

Also as you are using desktop version of oracle analytics ,Is it possible to push the query logic to database by creating a view using same query at the database and use this view in manual query of dataset like select * from <database view>

User_PWOBY

https://community.oracle.com/products/oracleanalytics/discussion/comment/61104#Comment_61104

Actually have this sample query where there is no database needed b/c here create some dummy data and it causes this error "

Unsupported SQL statements."

The query is:

: -- Step 1: Create CTEs with Dummy Data
WITH Products AS (
SELECT 1 AS ProductID, 'Laptop' AS ProductName, 'Electronics' AS Category, 1000 AS Price, 50 AS Stock
UNION ALL
SELECT 2, 'Smartphone', 'Electronics', 800, 200
UNION ALL
SELECT 3, 'Washing Machine', 'Appliances', 500, 30
UNION ALL
SELECT 4, 'Refrigerator', 'Appliances', 1200, 20
UNION ALL
SELECT 5, 'Desk Chair', 'Furniture', 150, 100
),
Orders AS (
SELECT 101 AS OrderID, 1 AS ProductID, 2 AS Quantity, '2024-11-01' AS OrderDate
UNION ALL
SELECT 102, 3, 1, '2024-11-03'
UNION ALL
SELECT 103, 5, 4, '2024-11-05'
UNION ALL
SELECT 104, 1, 1, '2024-11-06'
UNION ALL
SELECT 105, 2, 5, '2024-11-08'
)

-- Step 2: Query the Dummy Data
SELECT
p.ProductID,
p.ProductName,
p.Category,
p.Price,
o.OrderID,
o.Quantity,
(p.Price * o.Quantity) AS TotalRevenue
FROM
Products AS p
INNER JOIN
Orders AS o
ON
p.ProductID = o.ProductID
ORDER BY
p.Category, p.ProductName;

Also want to create dashboard on OAS on live data for reporting so can't use views for it. And it runs well on sql server management studio but not in oracle analytics server . Please suggest a solution for it.

User_PWOBY

https://community.oracle.com/products/oracleanalytics/discussion/comment/61115#Comment_61115

also exists clause is not supported in OAS, and need to run query that uses exists clause so what's the alternative on Oracle analytics server?

Ram-Oracle

@User_PWOBY The query is manual query which is executed at db (sql server) and if db supports the exists clause then query will run fine.

In oas desktop ,please try use portion of small queries from the whole query and identify which portion of query is causing the issue .

As you suspect exists clause ,as part of testing please rewrite the sql with out exists clause and see if the query executes fine with out exists clause.

User_PWOBY

https://community.oracle.com/products/oracleanalytics/discussion/comment/61129#Comment_61129

Yes, it works without the EXISTS clause, but the problem is that I need to create CTEs, which are not supported on OAS Desktop. Also what's the alternatives forEXISTS clause in OAS?

Ram-Oracle

@User_PWOBY Please refer documentation on exists operator

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/EXISTS-Condition.html From the syntax we use exists along with sub query.If sub query returns atleast one row then condition is met and also sub query will be run for every row in outer query's table. when there are tables with huge volume of data there will be performance issues and probably due to this oracle analytics desktop complains about unsupported sql statements.

example with exists:

SELECT department_id  FROM departments d  WHERE EXISTS  (SELECT * FROM employees e    WHERE d.department_id     = e.department_id)   ORDER BY department_id;

alternative query:

select * from deparments d inner join employees e on d.department_id=e.department.id

order by department_id.

This is just an basic example and you may rewrite your query with out exists.

Please see if it helps.

Gianni Ceresa

@User_PWOBY ,

You mixed up a number of languages…

Are you sure the field where you are entering the query expects SQL? If it does expect LSQL, that's a different language, and it doesn't have all the SQL operators, because it is just a different language.

At first you had a WITH clause, which can't be used because the query is embedded into another piece of SQL with a WITH clause itself. Also, LSQL doesn't have WITH.

If you try to use EXIST in LSQL, I don't believe it does exist. LSQL is not a database language, it's the language spoken by the BI Server (which is by far not a database).

Another example you posted was with SQL in the 23ai style without the "FROM" clause, that one can only work on a 23ai Oracle database or a different database engine that does support it.

All in all, take a step back and make sure you are using the right language in the right place: SQL and LSQL could look similar but are very different languages. And LSQL is extremely limited, because it isn't interpreted by a database.

[Deleted User]

And LSQL is extremely limited, because it isn't interpreted by a database.

And LSQL is meant to be source agnostic. Meaning LSQL is built to support the lowest common denominator of functionalities from a purely functional side of things. It's not mean to be an abstract of Oracle SQL. Or Microsoft's SQL. Or MDX for that matter…