Question
· Oct 17, 2018

Can you use a view in a sub-query?

I have created a view to stage some data in a different format and then want to reference that view in a SQL query from a table that filters the data from the view using a property of the table. 

Example:

select
   MsgId,
   FileName,
   (select
      ReportName
     from
      custom_view
     where
       MsgId = ReportId
    ) as ReportName
from
   main_table

Is this even possible? When I try this, I get an error table not found for the view?

David

Discussion (1)0
Log in or sign up to continue

YES it is possible.

- see your sub_query first working 
- just custom_view seems to miss the typical dot in table and view names 

Views are just a kind of shortcut to a query formula.

 in namespace SAMPLES you could do this

select Home_City, DOB, name,
(select Name from sample.personview sub where sub.DOB=main.DOB ) SubName
 from Sample.Person main

The example doesn't make much sense but it shows that this works

Anyhow, why don't you just use a JOIN  like this?

select Home_City,main.DOB, main.name, sub.name
from Sample
.Person main
left outer join sample.personview sub on
sub.DOB=main.DOB

or in your case 

select    MsgId,   FileName, ReportName
from main_table LEFT OUTER JOIN   custom_view
ON     MsgId = ReportId

Again: both table name and view name look odd to me