Menu
I have a select query in which I would like to do a join on a view and another table. The view works fine and so does the table, separately. But when I try to do something like:
select VIEW.col1, VIEW.col2
from VIEW, TABLE
where VIEW.col1 = TABLE.col1
I get The multi-part identifier "VIEW.col1" could not be bound.
Is the syntax wrong, or is that just not allowed on views?
253
A view can contain all rows of a table or select rows from a table.
there is no difference. A view is just a stored query which can be referred to in sql queries as though they are tables. Note that this does not apply to materialized views. A view is only a query stored in the data dictionary: it is not going to make your query run faster or slower.
If you change the structure of any tables in the underlying view, select * may break any applications that rely on the columns being in a specific order etc. It's generally accepted that doing select * anywhere, not just in view definitions, is bad practice.
Because you store data in a table on the database, it can be quicker to access. Once you open the application, you can quickly access the information you seek. Data in a view can take longer to access because you have to run a query first. If you want results for data from multiple tables, this can take even longer.
This should work
select v.col1, t.col2
from VIEW v, TABLE t
where v.col1 = t.col1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
