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

[Brenden]

Hi,

I am trying to get data in Power BI. My query works in Oracle SQL Developer, but in PBI I get an error "Details: "Oracle: ORA-00900: invalid SQL statement".

I noticed in Oracle SQL Dev, there is this spot underlined with pink, but I am not aware of any syntax error here (though it may be the culprit causing PBI to fail). This query still works despite the pink underline.

The purpose of this query is to get all entries for a ticketing department, where the ticket status is changed to "closed" or "sleep" in the past day. Adjusting for 4 hours due to database update delay. Closed is a Date, Sleep is a Unix timestamp. I believe I have some redundancy/ code that doesn't do anything in my defined values - but it gives the expected results anyway.

Here is my SQL simplified a bit: 

define value1 = 'TRUNC((SYSDATE - 1)) + 0.1666666666666667 - 1/(24*60*60)'
define value2 = (&&value1 + 0.1666666666666667)
define sstart = ROUND(((&&value2 - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60),0)

define value3 = 'TRUNC((SYSDATE)) + 0.1666666666666667 - 1/(24*60*60)'
define value4 = (&&value3 + 0.1666666666666667)
define ssend = ROUND(((&&value4 - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60),0)

SELECT TICKET_ID "Ticket ID"
,TICKET_STATUS "Ticket Status"
,TICKET_CLOSE_DATETIME "Ticket Close DateTime"

,SLEEPTIME "SleepTime"
,SLEEPSTART "SleepStart"

,CASE_LEVEL_1 "Case Level 1"

,TICKET_OWNER_GROUP "Ticket Owner Group"

FROM tablexyz

WHERE (CASE_LEVEL_1 IN ('Group1', 'Group2') OR (TICKET_OWNER_GROUP = 'Group3' OR TICKET_OWNER_GROUP = 'Group4'))

AND (TICKET_STATUS = 'Closed' OR TICKET_STATUS = 'Sleep')

AND (TICKET_CLOSE_DATETIME BETWEEN TRUNC(SYSDATE - 1) - 1/(24*60*60) AND TRUNC(SYSDATE) - INTERVAL '1' SECOND
OR (SLEEPSTART > &&sstart AND SLEEPSTART < &&ssend))

[pranit828 · Accepted solution]

Hi @Brenden 

The SQL part will need to be re written
as One query per call is excutes and all the statements begining with define is a statement in itself.
Also, "&&value1" will need an input in Developer and if you have provided it once it may not ask untill session is reset. However, this cannot be carried over to PBO query editor. 

You may need to work on parameters instead to achieve the "&&" functionality.