Hi
I wonder if I can get some help.
I have an application using Oracle forms 11g, Oracle database 12c database
I have the below query where I need to re-write it as such that it can work within a post-query trigger of an oracle form ie without using the WITH statement and just using it as a select statement to obtain the necessary values
I understand Oracle forms doesn't use the 'WITH' clause as I did try it and wouldn't work.
The query essentially gets the usage values between two input dates ie start_date and an end_date from the transaction_log table.
The input parameters pc_as_type, pc_as_serial, pn_start_date, pn_end_date, pc_li_parameter will be values from variables within forms
ie pc_as_type = :b1.as_type, pc_as_serial = :b1.as_serial, pn_start_date = :b1.start_date and so on ie values from within the forms application fields.
So how can i get the same values without using the WITH clause so I can use it within my forms application to get the appropriate values.
I havent included create table or insert statement here as its just thee query I need help in rewriting without the WITH clause
Regards
CURSOR transaction_log_cur(pc_as_type VARCHAR2, pc_as_serial VARCHAR2, pn_start_date DATE, pn_end_date DATE ) IS
WITH parameters (pc_as_type, pc_as_serial, pn_start_date, pn_end_date) AS
(
SELECT pc_as_type, pc_as_serial , pn_start_date, pn_end_date FROM dual
)
, unpivoted_parameters AS
(
SELECT *
FROM parameters
UNPIVOT ( p_date
FOR grp IN ( pn_start_date AS +1
, pn_end_date AS -1
)
) p
)
, got_rnk AS
(
SELECT u.pc_as_type
, u.pc_as_serial
, u.grp
, TRUNC(t.transaction_date) transaction_date
, t.usage
, t.EXCHANGE_APPLYING_USAGE
, ROW_NUMBER () OVER ( PARTITION BY u.pc_as_serial, u.grp
ORDER BY ABS (u.p_date - t.transaction_date)
, grp * (u.p_date - t.transaction_date)
) AS rnk
FROM unpivoted_parameters u
JOIN transaction_log t ON t.as_type = u.pc_as_type
AND t.as_serial = u.pc_as_serial
and t.LI_PARAMETER = pc_li_parameter
)
SELECT
s_post_eau AS eau_usage_start_date
, e_post_eau AS eau_usage_end_date
FROM got_rnk
PIVOT ( MIN (transaction_date) AS dt
, MIN (EXCHANGE_APPLYING_USAGE) AS post_eau
, MIN (usage) AS post_u
FOR grp IN ( +1 AS s
, -1 AS e
)
)
WHERE rnk = 1;