# Qracle SQL using Partition By throws a keyword expected error **URL:** \ **Category:** General Discussion\ **Created:** [April 11, 2024, 8:47am UTC](https://forum.inductiveautomation.com/t/qracle-sql-using-partition-by-throws-a-keyword-expected-error/88682 "2024-04-11T08:47:14Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Adam\_Carrow](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@Adam\_Carrow](https://forum.inductiveautomation.com/u/Adam_Carrow)\ **Post date:** [April 11, 2024, 8:47am UTC](https://forum.inductiveautomation.com/t/qracle-sql-using-partition-by-throws-a-keyword-expected-error/88682/1 "2024-04-11T08:47:14Z")
Qracle SQL using Partition By throws a keyword expected error. Specifically im trying Select ROW\_NUMBER() OVER (PARTITION BY (wonum) ORDER BY closeddate DESC) as rownum it seems that while Partition By is std for Oracle sql it doesnt appear to be supported by the sql in Ignition. Thoughts ? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 11, 2024, 8:57am UTC](https://forum.inductiveautomation.com/t/qracle-sql-using-partition-by-throws-a-keyword-expected-error/88682/2 "2024-04-11T08:57:48Z")
I know you've only posted one line of code, but you can make it stand out by formatting it as code. See [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831). Thanks. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 11, 2024, 11:52am UTC](https://forum.inductiveautomation.com/t/qracle-sql-using-partition-by-throws-a-keyword-expected-error/88682/3 "2024-04-11T11:52:44Z")
Ignition doesn't care what SQL you supply, as SQL, though it can scramble SQL scripts and comments. Show your entire SQL and the method by which you are calling it. (Made neat as Transistor suggests.) ---
**Author:** ![Adam\_Carrow](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@Adam\_Carrow](https://forum.inductiveautomation.com/u/Adam_Carrow)\ **Post date:** [April 11, 2024, 12:14pm UTC](https://forum.inductiveautomation.com/t/qracle-sql-using-partition-by-throws-a-keyword-expected-error/88682/4 "2024-04-11T12:14:17Z")
```auto Select ReportDate, statusdate, Duration, locdesc, assetdesc, description, reportedby, wonum, siteid, status, tech_name from ( select ROW_NUMBER() Over(Partition by Trunc(wonum) order by Statusdate desc) as rownum, to_char(TO_TIMESTAMP(ReportDate , 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS') as ReportDate, to_char(TO_TIMESTAMP(statusdate , 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS') as statusdate, (cast(TO_TIMESTAMP(statusdate , 'YYYY-MM-DD HH24:MI:SS') as date) - cast(TO_TIMESTAMP(ReportDate , 'YYYY-MM-DD HH24:MI:SS')as date))*60*24 as Duration, locdesc, assetdesc, description, reportedby, wonum, siteid, status, tech_name from MAXIMO.Z_WO_ALL where siteid like 1119 and worktype like 'EM' and (status like 'RESOLVED' or status like 'COMP') and to_char(TO_TIMESTAMP(ReportDate , 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS') >= '2024-04-10 06:00:00.000') where rownum = 1 ``` I actually tried removing the trunc and didnt help ... the inner select works fine without the row\_number line ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 11, 2024, 12:46pm UTC](https://forum.inductiveautomation.com/t/qracle-sql-using-partition-by-throws-a-keyword-expected-error/88682/5 "2024-04-11T12:46:22Z")
> [@Adam\_Carrow](#): > > I actually tried removing the trunc I would expect that to help. Do you mean you used `Partition by wonum` ? What happens if you run just the inner select, with partition by, by itself? ---
**Author:** ![Adam\_Carrow](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@Adam\_Carrow](https://forum.inductiveautomation.com/u/Adam_Carrow)\ **Post date:** [April 11, 2024, 7:20pm UTC](https://forum.inductiveautomation.com/t/qracle-sql-using-partition-by-throws-a-keyword-expected-error/88682/6 "2024-04-11T19:20:52Z")
> [@Adam\_Carrow](#): > > ... > `select ROW_NUMBER() Over(Partition by (wonum) order by Statusdate desc) as rownum,` > ... Same error when running the inner select ... my current running query is the inner select without the line that has row\_num in it so it was running and what i built off of see below . Error running query: SQLQuery(query=select ROW\_NUMBER() Over(Partition by (wonum) order by Statusdate desc) as rownum, to\_char(TO\_TIMESTAMP(ReportDate , 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS') as ReportDate, to\_char(TO\_TIMESTAMP(statusdate , 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS') as statusdate, (cast(TO\_TIMESTAMP(statusdate , 'YYYY-MM-DD HH24:MI:SS') as date) - cast(TO\_TIMESTAMP(ReportDate , 'YYYY-MM-DD HH24:MI:SS')as date))_60_24 as Duration, locdesc, assetdesc, description, reportedby, wonum, siteid, status, tech\_name from MAXIMO.Z\_WO\_ALL where siteid like 1119 and worktype like 'EM' and (status like 'RESOLVED' or status like 'COMP') and to\_char(TO\_TIMESTAMP(ReportDate , 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS') \>= '2024-04-11 06:00:00.000' this is the error i get On: WorkOrder\_Table\_Popup\_AC\_1.Root Container.Power Table 1.data caused by GatewayException: ORA-00923: FROM keyword not found where expected ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 11, 2024, 8:02pm UTC](https://forum.inductiveautomation.com/t/qracle-sql-using-partition-by-throws-a-keyword-expected-error/88682/7 "2024-04-11T20:02:13Z")
You might need an Oracle person to decipher what is going wrong. ☹ ---
**Author:** ![Adam\_Carrow](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@Adam\_Carrow](https://forum.inductiveautomation.com/u/Adam_Carrow)\ **Post date:** [April 11, 2024, 9:37pm UTC](https://forum.inductiveautomation.com/t/qracle-sql-using-partition-by-throws-a-keyword-expected-error/88682/8 "2024-04-11T21:37:55Z")
I really appreciate your time ... I'll keep on digging .. thank you