Query works in Oracle SQL, but PowerBI returns nothing

[Anonymous]

My query works as expected in Oracle SQL Developer. When I use the same query to connect to the Oracle data source in PowerBI, it does the usual loading steps, but there is no data returned, only column headers. There are no blank rows, just no data at all. No error message is given.

I tested another query that has worked in the past and it still works and returns data. So I believe the issue is with my query which PowerBI does not like for some reason.

Here is an example of the query with sensitive info replaced with "redacted" : 

SELECT
C.TICKET_ID "Ticket ID"
,C.TXT_ORIGINATINGTICKETID "Case"
,C.TICKET_STATUS
,cast(from_tz(cast(C.CREATE_DATE as timestamp), 'UTC') at time zone 'US/Eastern' as date) "Create Date"

,cast(from_tz(cast(c.REFERRED_DATE_TIME as timestamp), 'UTC') at time zone 'US/Eastern' as date) "Referred DateTime"
,c.ASSIGNED_TO_GROUP "Current Assigned to Group"
,'Incoming' as "TYPE"

,A.BYUSER "BYUSER"
,A.TMSTMP "Referred Timestamp"
,A.FIELDNAME "FIELDNAME"
,A.NEWVALUE "NEWVALUE"
,A.OLDVALUE "OLDVALUE"

, C.CASE_LEVEL_1 "Case Level 1"
, C.CASE_LEVEL_2 "Case Level 2"
, C.CASE_LEVEL_3 "Case Level 3"
, C.CASE_LEVEL_4 "Case Level 4"

FROM "redacted" C

FULL OUTER JOIN "redacted" A
ON C.TICKET_ID = A.TICKETID

WHERE
(c.CASE_LEVEL_1 IN ('"redacted"', '"redacted"') OR (c.TICKET_OWNER_GROUP = '"redacted"' OR c.TICKET_OWNER_GROUP = '"redacted"' OR c.TICKET_OWNER_GROUP = '"redacted"'))

AND c.REFERRED_DATE_TIME IS NOT NULL
AND A.FIELDNAME = 'ASSIGNED_GROUP'

AND
--returned incoming

(DBMS_LOB.SUBSTR(A.OLDVALUE, 25) NOT IN ('"redacted"','"redacted"','"redacted"','"redacted"','"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"') AND (DBMS_LOB.SUBSTR(A.OLDVALUE, 25) IS NOT NULL)

AND (DBMS_LOB.SUBSTR(A.NEWVALUE, 25) IN ('"redacted"','"redacted"','"redacted"','"redacted"','"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"', '"redacted"') OR DBMS_LOB.SUBSTR(A.NEWVALUE, 25) IS NULL)
)

AND A.SCHEMAID = 'CommonTicket'
AND A.TMSTMP LIKE ('%2023%')
AND (C.CREATE_DATE LIKE ('%2022%') OR C.CREATE_DATE LIKE ('%2023%'))
ORDER BY C.TICKET_ID, A.TMSTMP DESC

[Ghhousuddin · Accepted solution]

There are a few possible reasons why your query is not returning any data in Power BI, even though it works in Oracle SQL Developer. Here are some suggestions to troubleshoot the issue:

1. Check if your query contains any date-related or Oracle-specific functions that might not be supported or might behave differently in Power BI. For example, FROM_TZ, CAST, and DBMS_LOB.SUBSTR are Oracle-specific functions. Try to replace these functions with Power Query transformations after importing the data.

2. Instead of using LIKE for date comparisons, use proper date comparisons. For example, change:

3. AND A.TMSTMP LIKE ('%2023%')
   AND (C.CREATE_DATE LIKE ('%2022%') OR C.CREATE_DATE LIKE ('%2023%'))
4. to:
5. AND EXTRACT(YEAR FROM A.TMSTMP) = 2023
   AND (EXTRACT(YEAR FROM C.CREATE_DATE) = 2022 OR EXTRACT(YEAR FROM C.CREATE_DATE) = 2023)
   

6. 1. Remove comments from the query or move them to a separate line. In some cases, comments can cause issues when importing queries into Power BI.

   2. Try importing data in multiple steps by simplifying the query. For example, import the data from the "redacted" table first, and then apply the JOIN and filter conditions using Power Query transformations in Power BI.

   3. Verify the connection settings and credentials in Power BI. Make sure you are using the correct connection settings, and your user account has the necessary privileges to access the data.

   4. Ensure that the data types of the columns in your query match the data types in Power BI. Power BI might not recognize some data types or might convert them differently than Oracle SQL Developer.

   If none of these suggestions resolve the issue, consider breaking down the query into smaller parts to identify which part of the query is causing the problem. Then, modify the problematic part accordingly or use Power Query transformations in Power BI to achieve the same result.

[Anonymous]

Hi Ghhousuddin,

Thanks for your reply.

After trying the troubleshooting you suggested, it was the "CREATE DATE LIKE" part of the query causing the problem. I ended up using "AND C.CREATE_DATE BETWEEN TO_DATE('2022-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD')" which worked. 

The thing about all this I don't understand is that in this MS documention https://learn.microsoft.com/en-us/power-query/native-database-query it says under "Connectors that support native database queries" - Oracle database native queries are supported. So, would it not be correct to think that a query that works in Oracle SQL Developer is considered "working" as a native Oracle SQL query?

I have already discovered (in a previous post here) that using variables that work in Oracle does not work in PowerBI. Is my understanding of a "native query" wrong, or is there a gap in support, despite what the documentation says? And is there some other source that I can refer to?

I find it a bit ridiculous that a working query will fail in PowerBI, and the only way to troubleshoot is to modify it in the tiny Advanced Editor window, looking for the mystery cause.

[Ghhousuddin]

The Microsoft documentation you referred to is correct in stating that Oracle database native queries are supported in Power Query. However, there are still some limitations and differences between the SQL syntax and features supported in Oracle and those supported in Power Query, which can lead to some queries not working as expected.

In your case, it's possible that the "CREATE DATE LIKE" syntax is not fully supported in Power Query, which is why it was causing issues. While this syntax works in Oracle SQL Developer, it may not be fully compatible with Power Query's implementation of Oracle database queries.

In general, it's always a good idea to test your queries in Power Query before relying on them in your reports or dashboards. This can help you identify any compatibility issues or syntax differences that may exist between your SQL source and Power Query.

If you're looking for additional sources of information on using native database queries in Power Query, you can refer to the official Microsoft documentation or community forums These resources can provide additional tips, best practices, and troubleshooting advice for working with native database queries in Power Query.