Creating an Oracle view that takes a parameter

Question

I have a very long query:

SELECT TO_CHAR(tsc.id) AS status,
CASE WHEN tsc.description IS NULL THEN CAST('' as NVARCHAR2(50)) ELSE tsc.description END AS description,
SUM(CASE WHEN tr.USER_TYPE = 1 THEN 1 ELSE 0 END) AS "1",
SUM(CASE WHEN tr.USER_TYPE = 2 THEN 1 ELSE 0 END) AS "2",
SUM(CASE WHEN tr.USER_TYPE = 3 THEN 1 ELSE 0 END) AS "3",
SUM(CASE WHEN tr.USER_TYPE = 5 THEN 1 ELSE 0 END) AS "5",
SUM(CASE WHEN tr.USER_TYPE IS NOT NULL THEN 1 ELSE 0 END) AS total
FROM TRANSACTION_STATUS_CODES tsc
LEFT JOIN TRANSACTIONS tr ON tsc.id = tr.status AND tr.User_Type BETWEEN 1 AND 5 AND tr.status != 1 AND tr.update_date BETWEEN TO_DATE('2022-01-01', 'yyyy-mm-dd HH24:MI:SS') AND TO_DATE('2023-01-04', 'yyyy-mm-dd HH24:MI:SS')
LEFT JOIN TRANSACTION_USER_TYPES ut ON ut.id = tr.user_type 
WHERE tsc.id != 1
GROUP BY tsc.id, tsc.description
UNION ALL
SELECT 'TOTAL 2,4,5' AS status,
NULL AS description,
SUM(CASE WHEN tr.USER_TYPE = 1 THEN 1 ELSE 0 END),
SUM(CASE WHEN tr.USER_TYPE = 2 THEN 1 ELSE 0 END),
SUM(CASE WHEN tr.USER_TYPE = 3 THEN 1 ELSE 0 END),
SUM(CASE WHEN tr.USER_TYPE = 5 THEN 1 ELSE 0 END),
SUM(CASE WHEN tr.USER_TYPE IS NOT NULL THEN 1 ELSE 0 END) AS total
FROM TRANSACTION_STATUS_CODES tsc
LEFT JOIN TRANSACTIONS tr ON tsc.id = tr.status AND tr.User_Type BETWEEN 1 AND 5 AND tr.status != 1 AND tr.update_date BETWEEN TO_DATE('2022-01-01', 'yyyy-mm-dd HH24:MI:SS') AND TO_DATE('2023-01-04', 'yyyy-mm-dd HH24:MI:SS')
LEFT JOIN TRANSACTION_USER_TYPES ut ON ut.id = tr.user_type 
WHERE tsc.id != 1 AND tsc.id IN (2, 4, 5)
UNION ALL
SELECT 'Total for All' AS status,
NULL AS description,
SUM(CASE WHEN tr.USER_TYPE = 1 THEN 1 ELSE 0 END),
SUM(CASE WHEN tr.USER_TYPE = 2 THEN 1 ELSE 0 END),
SUM(CASE WHEN tr.USER_TYPE = 3 THEN 1 ELSE 0 END),
SUM(CASE WHEN tr.USER_TYPE = 5 THEN 1 ELSE 0 END),
SUM(CASE WHEN tr.USER_TYPE IS NOT NULL THEN 1 ELSE 0 END) AS total
FROM TRANSACTION_STATUS_CODES tsc
LEFT JOIN TRANSACTIONS tr ON tsc.id = tr.status AND tr.User_Type BETWEEN 1 AND 5 AND tr.status != 1 AND tr.update_date BETWEEN TO_DATE('2022-01-01', 'yyyy-mm-dd HH24:MI:SS') AND TO_DATE('2023-01-04', 'yyyy-mm-dd HH24:MI:SS')
LEFT JOIN TRANSACTION_USER_TYPES ut ON ut.id = tr.user_type 
WHERE tsc.id != 1

That does what it does. I've been asked to save it as view and just "Select * from view" which is nice... but as you can see I run

AND tr.update_date BETWEEN TO_DATE('2022-01-01', 'yyyy-mm-dd HH24:MI:SS') AND TO_DATE('2023-01-04', 'yyyy-mm-dd HH24:MI:SS')

This line of code few times there. now. If I save it as view it will just be same result over and over.

I have this csharp code:

requestedDateTable = LocalGeneralDbExecuterService1.call_TransactionsReport_StoredProcedure(fromDateStr, toDateStr);

which is a function that stores the query above in a datatable variable with two dates I'm capturing from two different labels and gives me a modified result set based on those dates.

I'm trying to achieve the same kind of workflow but without having to write dozens lines of query code in my program.

Is that possible? If so, how? I've been trying procedures, views... and my SQL knowledge isn't WOW at all.

Answer 1

From 19.6 you can create SQL table macros. In effect these enable you to create parameterized views.

Here's an example based on the standard HR schema:

create or replace function filter_emps (
  start_date date, end_date date
)
  return clob sql_macro as
begin
  return ' 
    select * from hr.employees 
    where hire_date >= start_date and hire_date < end_date ';
end filter_emps;
/

select employee_id, hire_date
from   filter_emps ( date'2003-01-01', date'2003-06-01' );
/*
EMPLOYEE_ID HIRE_DATE        
----------- -----------------
        115 18-MAY-2003 00:00
        122 01-MAY-2003 00:00
*/

var start_date varchar2(10);
var end_date varchar2(10);

exec :start_date := '2005-01-01'
exec :end_date := '2005-03-01';
select employee_id, hire_date
from   filter_emps ( 
  to_date ( :start_date, 'yyyy-mm-dd' ), to_date ( :end_date, 'yyyy-mm-dd' )
);
/*
EMPLOYEE_ID HIRE_DATE        
----------- -----------------
        131 16-FEB-2005 00:00
        142 29-JAN-2005 00:00
        146 05-JAN-2005 00:00
        150 30-JAN-2005 00:00
        185 20-FEB-2005 00:00
*/

Answer 2

Views cannot take parameters.

Instead, you can write a stored procedure that takes the start and end dates as parameters and returns a cursor.

CREATE PROCEDURE procedure_name (
  i_start_date IN  TRANSACTIONS.UPDATE_DATE%TYPE,
  i_end_date   IN  TRANSACTIONS.UPDATE_DATE%TYPE,
  o_cursor     OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN o_cursor FOR
    <your_query>;
END procedure_name;
/

and replace the hard-coded dates with:

AND tr.update_date BETWEEN i_start_date AND i_end_date

Then you can call the procedure from C# and pass the parameters.

Answer 3

As already explained, views cannot take parameters.

Another approach suiting your requirements could be a table function:

-- define a type fitting your output
    create type t_my_row as object (
      id           number,
      description  varchar2(50)
    );
    
    create type t_my_tab is table of t_my_row;
    
-- write a procedure with the required parameters
    create function get_my_tab(i_start_date in date, i_end_date in date) return t_my_tab as
      l_tab  t_my_tab := t_my_tab();
    begin
                    -- your selection here: (select ... union all select...)
      for l_rec in (select id, descr from mytable where update_date between i_start_date and i_end_date) loop
        l_tab.extend;
        l_tab(l_tab.last) := t_my_row(l_rec.id, l_rec.description);
      end loop;
    
      return l_tab;
    end;
    
-- call it:
    select * from table(get_my_tab(sysdate-1, sysdate));