Forum Stats

  • 3,826,337 Users
  • 2,260,628 Discussions
  • 7,896,905 Comments

Discussions

Parameterized Views

ApexBine
ApexBine Member Posts: 153 Silver Badge
edited May 7, 2020 6:42PM in Database Ideas - Ideas

I have query like this

SELECT

isin,

MAX(price_date)                                       AS price_date,

MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_date) AS price

FROM share_price

WHERE trunc(price_date,'DD') <= to_date('30.11.2014','DD.MM.YYYY')

GROUP BY isin;

and I need it every month, so I would like to put it in a view.

Selecting from that view using an ISIN in the where clause is no problem, but as the reporting date changes every month, it would be great to have a date parameter that I can pass to the view when calling it.

So instead of using a fixed date, I would declare:

CREATE VIEW share_price_date AS

SELECT

isin,

MAX(price_date)                                       AS price_date,

MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_date) AS price

FROM share_price

WHERE trunc(price_date,'DD') <= :reporting_date

GROUP BY isin;

A plain SELECT would then look like

SELECT isin, price_date, price

FROM share_price_date (to_date('30.11.2014','DD.MM.YYYY'))

WHERE isin = 'DE123456789022'

and would save me from inventing any workarounds.

I have similar requirements for views that contain even more time intervals and 'count() over' functions.

Update: this has been implemented in Oracle 20c:

CREATE OR REPLACE FUNCTION budget(job varchar2) RETURN VARCHAR2 SQL_MACRO IS

BEGIN

  RETURN q'{

  select deptno, sum(sal) budget

    from emp

      where job = budget.job

        group by deptno

  }';

END;

/

SELECT * FROM budget ('MANAGER');

    DEPTNO     BUDGET 

---------- ---------- 

        20       2975 

        30       2850 

        10       2450

William RobertsonGeert GruwezctriebApexBineuser7023476user13075257user5536356Franck PachotKayKNimish GargborneselSven W.Kevan GellingFatMartinRN.B.doberkoflerHari_639commi235trenttns42David Krch-OracleTexasApexDeveloperLukas Edergetamyulohmannjnicholas330berxDejan T.KalpatarupattonjgdbitmanPhilipp SalvisbergBryn.Llewellyn-OracleNiall LitchfieldpcpaascheErik van Roon960326Alex NuijtenChristian Neumueller-OracleAhmad AbutalibMike KutzLudovicoCaldaraa_rintoulRobert LockardMettemusens2connor_mc_d-OraclegassenmjpnoskocormacojbbarretoTony AndrewsNenad Noveljic3471715Anthony EvansSpike HouseAndrey DokuchaevMartin Preissuser5962917user-brandon_1943619Alexey MarinFilipe M MendesBPeaslandDBAPeter HraškoThorsten Kettneruser4377404user4112672Racer I.Mustafa_KALAYCIUser_ORA1AChoc Caccd0dfee2-6b2e-4913-9da4-f62cb4b92afdLasse JenssenLothar FlatzBruno PerillokasimvaliNiels Heckerrober584812Jeppe
75
81 votes

Delivered · Last Updated

«13456

Comments

  • Geert Gruwez
    Geert Gruwez Member Posts: 1,011

    bad for performance.

    your where clause negates the use of an index on column price_date

    unless you have create a function index ?

    BEDE
  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    So your idea wold be a view functioning like a function, instead of the conventional way which is something like this?

    SELECT isin, price_date, price

    FROM share_price_date_view

    WHERE isin = 'DE123456789022'

       AND trunc(price_date,'DD') <= to_date(:reporting_date,'DD.MM.YYYY');

  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    bad for performance.

    your where clause negates the use of an index on column price_date

    unless you have create a function index ?

    Just assume the date has no time part then we don't need the trunc.

  • Geert Gruwez
    Geert Gruwez Member Posts: 1,011

    Just assume the date has no time part then we don't need the trunc.

    Assumptions are the mother of all screwups !

    Never make assumptions. Those are open invitations for Murphy, and the cool thing about Murphy ... He never fails to show up.

  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    So your idea wold be a view functioning like a function, instead of the conventional way which is something like this?

    SELECT isin, price_date, price

    FROM share_price_date_view

    WHERE isin = 'DE123456789022'

       AND trunc(price_date,'DD') <= to_date(:reporting_date,'DD.MM.YYYY');

    I'm not sure if we have the same understanding here.

    The parameter I use in my 'view definition' is used for finding the max date before the given date.

    My select

    SELECT isin, price_date, price

    FROM share_price_date (to_date('30.11.2014','DD.MM.YYYY'))

    WHERE isin = 'DE123456789022'


    would return one line like

    'DE123456789022', '28.11.2014', 67.89


    Thorsten Kettner
  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    Assumptions are the mother of all screwups !

    Never make assumptions. Those are open invitations for Murphy, and the cool thing about Murphy ... He never fails to show up.

    Your remark is absolutely valid but I think we are digressing from the actual suggestion.

    Peter HraškoTony Andrews
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown
    edited Apr 18, 2018 9:59AM

    In this example I'm not sure why you couldn't just expose the date in the view's SELECT list. However I can see it could be useful in more complex cases, for example:

    select a, b, c from   sometab t connect by prior t.id = t.parent_idstart with t.id = 123

    When this is part of a view, there is currently no way to expose the 'start with' predicate to allow it to be reused for other values of t.id (or if there is, it's complicated and error-prone).

    I think the syntax would need an explicitly named and typed parameter similar to functions:

    create view share_price_date (dateparam date) as ...

    This could even be extended to WITH clauses:

    with cte (dateparam date) as
         ( select b.c
           from   blahblah b
           where  b.d >= dateparam -1 )select * from cte (sysdate);

    Edit: ...although not this actual syntax, because it already exists (without the datatypes) for specifying the column names.

    ApexBineNimish Garg
  • ctrieb
    ctrieb Member Posts: 314 Gold Trophy

    Good idea, I think this wiil help to write better code.

    ApexBinePeter Hraško
  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    Good idea, I think this wiil help to write better code.

    Thanks Christian,

    indeed, I think it would help encapsulating logic in a way that it could easily be re-used in different contexts.

    If my package uses that view and logs some error message, for instance, the specialist could use the underlying view for checking the issue.

  • berx
    berx Member Posts: 219 Silver Badge

    maybe you can use this example to implement what you want to do already?

    create table Blume as

    select rownum rn, dbms_random.value num

    from

            dual

    connect by

            level <=1000;

    --  grant EXECUTE on DBMS_SESSION to &user;

    --  grant create any context to &user;

    -- some code borrowed from http://www.adp-gmbh.ch/ora/sql/create_context.html

    CREATE CONTEXT BIENE using Wiese;

    create or replace package Wiese as

        procedure set_value_in_context(some_value in varchar2);

    end Wiese;

    /

    create or replace package body Wiese as

        procedure set_value_in_context(some_value in varchar2) is

        begin

            dbms_session.set_context('BIENE', 'random_filter', some_value);

        end set_value_in_context;

    end Wiese;

    /

    create view Rasenmaeher as

    select count(*) Bluemchen

    from Blume b

    where b.num >

         to_number(sys_context('BIENE', 'random_filter') );

    exec Wiese.set_value_in_context(to_char(42/100));

    select sys_context('BIENE', 'random_filter') from dual;

    select * from Rasenmaeher;

    -- 572 in my testcase

    exec Wiese.set_value_in_context(to_char(99/100));

    select * from Rasenmaeher;

    -- 4 in my testcase

    jnicholas330