Qracle SQL using Partition By throws a keyword expected error

General Discussion
1

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 ?

2

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. Thanks.

3

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.)

4 
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

5

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?

6

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))6024 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

7

You might need an Oracle person to decipher what is going wrong. :frowning_face:

8

I really appreciate your time ... I'll keep on digging .. thank you