Skip to Main Content
  • Questions
  • Choosing a view based on result of view

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 18, 2021 - 9:28 am UTC

Last updated: January 20, 2021 - 5:57 am UTC

Version: 12C

Viewed 100+ times

You Asked

Hi all,

I am having a struggle with a view. The outcome of the view can be 1 row of 3 different views with the same fields.
Can someone point me in the right direction how to think please? :)

Very basic, this is my problem:

/* MAINVIEW */
SELECT * FROM 

(SELECT * FROM VIEW1)  --returns n rows

/* IF VIEW1 IS EMPTY THEN SELECT ALL FROM VIEW2 */
(SELECT * FROM VIEW2)  -- returns n rows

/* IF VIEW2 IS EMPTY THEN SELECT VIEW3 (=HAS ALWAYS DATA) */
(SELECT * FROM VIEW3)  -- returns 1 row



I don't need full code, just a hint in the right direction ;) .

Thanks in advance.

and we said...

Something like this might work:

select * from view1
union  all
select * from view2
where  not exists ( select * from view1 )
union  all
select * from view3
where  not exists ( select * from view1 )
and    not exists ( select * from view2 )


Though it leads to running the view1 query three times and view2 query twice, so could be slow.

Taking the queries underlying these views and writing the not existence checks into these may be faster. Or there may be an even better method, it's hard to say without example tables and data.

Rating

  (1 rating)

Comments

A reader, January 19, 2021 - 3:20 pm UTC

I will try to make a simple example :) . Sorry for not doing so earlier...

View 1 returns orders from TABLE A
--------------------------------
CUSTOMERID | NAME | #ORDERS
--------------------------------
        1  | CUST1| 3


View 2 returns orders from TABLE B
--------------------------------
CUSTOMERID | NAME | #ORDERS
--------------------------------
        2  | CUST2| 5


View 3 returns only customername from customer table
--------------------------------
CUSTOMERID | NAME | #ORDERS
--------------------------------
        1  | CUST1| null
        2  | CUST2| null
        3  | CUST3| null


So what I am trying to accomplish, is the following:
If I want customerid 1 I only need the data from view1 (not view3).
If I want customerid 2 I only need the data from view2 because there is no data in view1 (view3 not needed so far).
If I want customerid 3 I get the data from view3 because it is not found in view 1 and 2.

This is very simplified but shows what I want.

With your answer and advice I can go further building the views. Thanks a lot :) !!

Connor McDonald
January 20, 2021 - 5:57 am UTC

Another option could be

select *
from (
select min(view_level) over () as found_in_view, t.*
from (
select 1 view_level, v1.* from v1 where [your conditions]
union all
select 2  view_level, v2.* from v2 where [your conditions]
union all
select 3 view_level, v3.* from v3 where [your conditions]
) t
)
where view_level = found_in_view


which is getting data from all three views and then only keeping the level it was first found in. If you needed this by a group (eg by customer) than the analytic function could be modified accordingly

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.