Create view from query - oracle

Question

I have query which use in oracle apex. Is it possible from this query create view where is for jasper report (Jasper not support with cte).

Note: P99_DATE_FROM and P99_DATE_TO are parameters!

with cte as (
SELECT
    a.name,
    w.order as order,
    s.num as num,
    w.id,
    (Select count(*) from quant where id = w.id and trunc(date) between :P99_DATE_FROM and :P99_DATE_TO) as QUANT
FROM
    web w
    left outer JOIN tabel_1 a ON a.id = w.id
    left outer JOIN tabel_2 s ON s.id = w.id
WHERE
    (trunc(s.date) between :P99_DATE_FROM and :P99_DATE_TO or :P99_DATE_FROM is null or :P99_DATE_TO is null)

)
    SELECT 
        name,
        SUM(order) as TOTAL,
        SUM(num) as NUM,
        QUANT
    from cte
    group by name,QUANT

Why not just use an inline view instead? – Alex Poole Jan 31 '19 at 19:15 — Alex Poole, Jan 31 '19 at 19:15
@AlexPoole I don't know how use that! – Pointer Jan 31 '19 at 19:17 — Pointer, Jan 31 '19 at 19:17

score 2 · Accepted Answer · answered Jan 31 '19 at 19:19

You can parameterize a view but having the parameters supplied from Apex makes it (even) more complicated.

As you only refer to the CTE once in the main query, it would be simpler to use an inline view instead, turning your CTE query into a subquery:

SELECT 
    name,
    SUM(order) as TOTAL,
    SUM(num) as NUM,
    QUANT
from (
    SELECT
        a.name,
        w.order as order,
        s.num as num,
        w.id,
        (Select count(*) from quant where id = w.id and trunc(date) between :P99_DATE_FROM and :P99_DATE_TO) as QUANT
    FROM
        web w
        left outer JOIN tabel_1 a ON a.id = w.id
        left outer JOIN tabel_2 s ON s.id = w.id
    WHERE
        (trunc(s.date) between :P99_DATE_FROM and :P99_DATE_TO or :P99_DATE_FROM is null or :P99_DATE_TO is null)
)
group by name,QUANT

score 2 · Answer 2 · answered Jan 31 '19 at 19:19

The normal use of the WITH clause is revealed by its alternate name, sub-query factoring. They are handy when want to use the sub-query more than once in our query, say in two whatever IN ( sub-query ) filters.

That doesn't apply in your case, so you could simply convert the CTE into an inline view:

SELECT 
    name,
    SUM(order) as TOTAL,
    SUM(num) as NUM,
    QUANT
from  (
    SELECT
        a.name,
        w.order as order,
        s.num as num,
        w.id,
        (Select count(*) 
         from quant 
         where id = w.id and trunc(date) between :P99_DATE_FROM and :P99_DATE_TO) as QUANT
    FROM
        web w
        left outer JOIN tabel_1 a ON a.id = w.id
        left outer JOIN tabel_2 s ON s.id = w.id
    WHERE
        (trunc(s.date) between :P99_DATE_FROM and :P99_DATE_TO
          or :P99_DATE_FROM is null or :P99_DATE_TO is null)
    ) cte
 group by name,QUANT

Create view from query - oracle

2 Answers2