I've been missing parameterizable views in Oracle for many years.. with SQL macros they have now become possible: (from 19.7)
/* teste sql macros */
with parms(p_von_date,p_bis_date) as
(select trunc(sysdate) - 14 p_von_date,
trunc(Sysdate) p_bis_date
from dual)
select * from trips_and_location('username');
--Welches Statement generiert das SQL Macro?
variable x clob;
exec dbms_utility.expand_sql_text(q'# with parms(p_von_date,p_bis_date) as
(select trunc(sysdate) - 14 p_von_date,
trunc(Sysdate) p_bis_date
from dual)
select * from trips_and_location('username'); #',:x );
set serveroutput on
begin dbms_output.put_line(:x); end;
/
I love SQL macros - they're great for avoiding complex statements in Apex while still dynamically processing parameters.
Unfortunately there are limitations in with clauses, as I have with my macro, these are not processed. Hence the trick with the CTE parms which is defined outside of the sqlmacro. I think and hope the limit will eventually drop.
Here is the code of my SQL table macro (be careful with 19c replace sql_macro(table) with sql_macro)
The main idea of this query is to retrieve information about a user's trips, including start and end times, locations, distances, drive time, idle time, and speed metrics.
This query uses a separate subquery to find the closest locations for the start and end points of each trip. This happens because the geo-coordinates of user locations are collected asynchronously and do not necessarily exactly match the start and end times of a trip.
create or replace function trips_and_location(p_appusername varchar2) return varchar2 sql_macro(table)
is begin
/* with clause cannot have parameters in sql macro (OMG) */
return q'{
with min_loc_per_day as (
select appusername,
status_date,
replace(latitude, ',', '.') as latitude,
replace(longitude, ',', '.') as longitude
from location_no_odo , parms /* get parameter from outerspace */
where status_date between parms.p_von_date-1 and parms.p_bis_Date+1
),
tripinfo_filtered as (
select appusername,
tripstart,
tripend,
to_number(distance) as distance,
drivetime,
idletime,
speedavg,
speedmax,
row_number() over (partition by tripstart order by tripend asc) as rn
from tripinfo t, parms
where drivetime > 5
and to_number(distance) > 5
and tripstart between parms.p_von_date and parms.p_bis_Date+1
),
first_loc as (
select row_number() over (partition by t.appusername,tripstart order by tripend asc) as rnfl,
tripstart,
first_value(ml.status_date) over (partition by t.appusername,tripstart order by abs((case when tripstart > ml.status_date then get_seconds(tripstart - ml.status_date) else 99999999 end)) asc) as first_status_date,
to_char(first_value(ml.status_date) over (partition by t.appusername,tripstart order by abs((case when tripstart > ml.status_date then get_seconds(tripstart - ml.status_date) else 99999999 end)) asc), 'HH24:MI') as first_status_time,
first_value(ml.latitude) over (partition by t.appusername,tripstart order by abs((case when tripstart > ml.status_date then get_seconds(tripstart - ml.status_date) else 99999999 end)) asc) as first_latitude,
first_value(ml.longitude) over (partition by t.appusername,tripstart order by abs((case when tripstart > ml.status_date then get_seconds(tripstart - ml.status_date) else 99999999 end)) asc) as first_longitude
from tripinfo_filtered t
join min_loc_per_day ml on (t.appusername = ml.appusername)
where rn = 1
),
last_loc as (
select row_number() over (partition by t.appusername,tripstart order by tripend asc) rnll,
tripstart,tripend,
first_value(ml.status_date) over (partition by t.appusername,tripstart order by abs(get_seconds(tripend - ml.status_date)) asc) as last_status_date,
to_char(first_value(ml.status_date) over (partition by t.appusername,tripstart order by abs(get_seconds(tripend - ml.status_date)) asc), 'HH24:MI') as last_status_time,
first_value(ml.latitude) over (partition by t.appusername,tripstart order by abs(get_seconds(tripend - ml.status_date)) asc) as last_latitude,
first_value(ml.longitude) over (partition by t.appusername,tripstart order by abs(get_seconds(tripend - ml.status_date)) asc) as last_longitude
from tripinfo_filtered t
join min_loc_per_day ml on (t.appusername = ml.appusername)
where rn = 1
)
select
t.tripstart,
to_char(t.tripstart,'HH24:MI') tripstartdetail,
to_char(t.tripend,'HH24:MI') tripenddetail,
first_loc.first_status_date,
first_loc.first_status_time,
first_loc.first_latitude,
first_loc.first_longitude,
last_loc.last_status_date,
last_loc.last_status_time,
last_loc.last_latitude,
last_loc.last_longitude,
t.distance,
t.drivetime,
t.idletime,
t.speedavg,
t.speedmax
from tripinfo_filtered t
join first_loc on t.tripstart = first_loc.tripstart
join last_loc on t.tripstart = last_loc.tripstart
where t.rn = 1 and rnll = 1 and rnfl=1
and t.appusername = p_appusername /* SQL Macro parameter */
}';
end trips_and_location;
/
Look now like this
Refresh under 1.5 seconds
yes