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
Comments
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 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