gustafn
committed
on 16 Feb 22
Fixes for Oracle 19c: make query portable to Oracle by replacing USING by explicit ON joins

This fixed the following problem, where the pro… Show more
Fixes for Oracle 19c: make query portable to Oracle by replacing USING by explicit ON joins

This fixed the following problem, where the problem is not easy to spot on first sight:

[16/Feb/2022:13:26:09][12845.7efd2d3e1700][-sched:0:41:22-] Error: nsoracle.c:1367:OracleSelect: error in `OCIStmtExecute ()': ORA-00904: "NOTIFICATION_REQUESTS"."USER_ID": invalid identifier

:    SQL:

:                select notification_id,

:                       notif_subject,

:                       notif_text,

:                       notif_html,

:                       file_ids,

:                       user_id,

:                       request_id,

:                       type_id,

:                       delivery_method_id,

:                       response_id,

:                       notif_date,

:                       notif_user,

:                       acs_permission.permission_p(notification_requests.object_id, !>>>!notification_requests.user_id, 'read') as still_valid_p

:                from notifications inner join notification_requests using (type_id, object_id)

:                  inner join acs_objects on (notification_requests.request_id = acs_objects.object_id)

:                  left outer join notification_user_map using (notification_id, user_id)

:                where sent_date is null

:                  and creation_date <= notif_date

:                  and (notif_date is null or notif_date < current_timestamp)

:                  and interval_id = :interval_id

Show less