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

Robert Cemper · Oct 17, 2018

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.Personmain left outer join sample.personview sub onsub.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

0 0