Forum Discussion
Query works in Oracle SQL, but PowerBI returns nothing
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:
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.
Instead of using LIKE for date comparisons, use proper date comparisons. For example, change:
- AND A.TMSTMP LIKE ('%2023%')
AND (C.CREATE_DATE LIKE ('%2022%') OR C.CREATE_DATE LIKE ('%2023%')) - to:
- AND EXTRACT(YEAR FROM A.TMSTMP) = 2023
AND (EXTRACT(YEAR FROM C.CREATE_DATE) = 2022 OR EXTRACT(YEAR FROM C.CREATE_DATE) = 2023) 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.
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.
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.
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.
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:
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.
Instead of using LIKE for date comparisons, use proper date comparisons. For example, change:
- AND A.TMSTMP LIKE ('%2023%')
AND (C.CREATE_DATE LIKE ('%2022%') OR C.CREATE_DATE LIKE ('%2023%')) - to:
- AND EXTRACT(YEAR FROM A.TMSTMP) = 2023
AND (EXTRACT(YEAR FROM C.CREATE_DATE) = 2022 OR EXTRACT(YEAR FROM C.CREATE_DATE) = 2023) 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.
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.
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.
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.
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.