OAS Manual Query Error [nQSError: 10058]

User_VMJII

I spent hours, but I just cant see it. Does anyone see what's wrong with my manual query (OAS DV)?

WITH RAC_SUM AS (
SELECT
SUBSTR(RAC, 7, 11) AS R_CIB,
DATE,
SUM(END_DAY) AS RAC_STANJE
FROM TABLE_A
WHERE RAC LIKE 'DS%'
AND OZNAKA IN ('X', 'Y')
GROUP BY SUBSTR(RAC, 7, 11), DATE
),
MAIN_DATA AS (
SELECT
DATE,
CIB,
SUM(END_DAY) AS MAIN_STANJE
FROM TABLE_A
WHERE DATE >= SYSDATE - 30
AND OZNAKA IN ('X', 'Y')
GROUP BY DATE, CIB
)
SELECT
main.DATE,
main.CIB,
main.MAIN_STANJE AS ORIGINAL_STANJE,
main.MAIN_STANJE + COALESCE(rac.RAC_STANJE, 0) AS STANJE_D_1,
(SELECT ROUND(AVG(sub.MAIN_STANJE + COALESCE(rac_avg.RAC_STANJE, 0)), 2)
FROM MAIN_DATA sub
LEFT JOIN RAC_SUM rac_avg
ON rac_avg.R_CIB = sub.CIB
AND rac_avg.DATE = sub.DATE
WHERE sub.DATE >= TRUNC(SYSDATE) - 30
AND sub.DATE < TRUNC(SYSDATE)
AND sub.CIB = main.CIB) AS AVG_30
FROM MAIN_DATA main
LEFT JOIN RAC_SUM rac
ON rac.R_CIB = main.CIB
AND rac.DATE = main.DATE

Gianni Ceresa

If you are using that query as source for a dataset, then the issue is the WITH clause : datasets queries do not support WITH, because the tool is going to generate a query with WITH itself, and Oracle database doesn't support nested WITH clauses. This is a known, and documented somewhere, limitation.

Rewrite your query without WITH clause, or create a view in your database with that query and build your dataset on that view (not using a WITH clause in DV).

Gianni Ceresa

Hi,

Does anyone see what's wrong with my manual query (OAS DV)?

Well … nobody sees your screen or knows what you are thinking about.

When something is wrong, there is generally some signs allowing to say "it is wrong"…

Do you have an error? Then post the error.

You don't get what you expect as result? Then you maybe have to share what you expect.

You maybe also want to provide some context of what is going on, because otherwise it is just a piece of SQL and nothing else…

User_VMJII

Yes, I have an error: "Error during query processing (SQLExecDirectW).

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred." But this query works correctly in sql developer, so I conclude that some minor change is needed Context: I want the balance and average of the last 30 days (with some filters)

User_VMJII

Thank you very much! I didn't know about this limitation, I changed the query and now it works, thanks!