Define Query works in Oracle SQL Developer but not in Power BI

[Anonymous]

Hi, I have a query that looks something like this:

DEFINE enddate = ADD_MONTHS(TRUNC(current_date), -1);

SELECT *
FROM TABLE1
WHERE TRUNC(local_date) >= &enddate

UNION

SELECT *
FROM TABLE2
WHERE TRUNC(local_date) >= &enddate

It is working in Oracle SQL Developer but when I run it in Power BI, the following message is returned:

"Oracle: ORA-00900: invalid SQL statement"

Is there any way to modify this query for it to run in Power BI?  I think the Define part is causing this error.

I have tried to remove the semicolon (;) but it is still not working.

Thank you

[v-xuxinyi-msft · Accepted solution]

Hi @Anonymous 

With reference to the information provided by you, the following is the case description: You have an error running a query in Power Bi, but the query runs fine in Oracle SQL Developer.

You can first try to import the two tables into Power Bi separately, and then do the UNION operation in Power Bi.

For the usage of the UNION function you can refer to the following link: https://learn.microsoft.com/en-us/dax/union-function-dax 

If I've misunderstood you, please provide more detailed information. We can better understand the problem and help you. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.