Can you use a view in a sub-query?

SQL, Caché

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. 


       MsgId = ReportId
    ) as ReportName

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


  • 0
  • 0
  • 90
  • 0
  • 1


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,,
from Sample
.Person main
left outer join sample.personview sub on

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