Hello Community,
I am encountering a critical error when trying to search using a text filter in a Direct Query report connected to an Oracle 11g database.
My issue appears similar to the one described in the thread: Searching a slicer in my Direct Query report blows... - Microsoft Fabric Community.
Upon investigation, I found that the Power BI Oracle connector generates an incorrect SQL query, causing the error. Below are the details of my environment:
- Power BI Desktop version: 2.138.1004.0 64-bit (November 2024)
- Oracle Driver: Oracle Client for Microsoft Tools (OCMT) x64
- Oracle version: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64-bit Production
The error I receive is: ORA-00904: "t4"."CIG": invalid identifier.
This error arises from the UPPER("t4"."CIG") condition in the SQL WHERE clause generated by Power BI, which is deeply nested in subqueries and leads to runtime issues.
DAX Generated by Power BI:
VAR __DS0FilterTable = FILTER(
KEEPFILTERS(VALUES('V_G4_LOTTI'[CIG])),
SEARCH("ZY", 'V_G4_LOTTI'[CIG], 1, 0) >= 1
)
VAR __DS0Core = CALCULATETABLE(
VALUES('V_G4_LOTTI'[CIG]),
KEEPFILTERS(__DS0FilterTable)
)
VAR __DS0PrimaryWindowed = TOPN(101, __DS0Core, 'V_G4_LOTTI'[CIG], 1)
EVALUATE __DS0PrimaryWindowed
ORDER BY 'V_G4_LOTTI'[CIG]
SQL Generated by Power BI Connector:
SELECT *
FROM (
SELECT
"t4"."CIG"
FROM
((
SELECT
"_"."ID_INTERNO_LOTTO" AS "ID_INTERNO_LOTTO",
"_"."CIG" AS "CIG",
"_"."OGGETTO_LOTTO" AS "OGGETTO_LOTTO"
FROM "GGAP3_PREP"."V_G4_LOTTI" "_"
)) "t4"
WHERE
(
(
SELECT INSTR(Op2, Op1, Op3)
FROM (
SELECT UPPER('ZY') AS Op1,
UPPER("t4"."CIG") AS Op2,
1 AS Op3
FROM DUAL
) AuxTable
) >= 1
)
GROUP BY "t4"."CIG"
ORDER BY "t4"."CIG" ASC
) WHERE ROWNUM <= 101
The invalid SQL seems to stem from the reference to UPPER("t4"."CIG") in the WHERE clause. Since "t4"."CIG" is nested within the subqueries, Oracle does not recognize it at runtime.
I would greatly appreciate any insights or solutions the community can offer to resolve this issue.
Thank you in advance for your help!
