Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Utpal
Posting Yak Master
179 Posts |
 Posted - 2002-03-08 : 05:18:42
|
| Hi everybody,My problem is that I want to create a view with the following SQL statement :SELECT Item_Master.*, QStdCost_Master_Current.cost FROM Item_Master, QStdCost_Master_Current WHERE Item_Master.part_no *= QStdCost_Master_Current.part_noQStdCost_Master_Current is a view defined with the SQL statement :SELECT A.part_no, A.cost_date, A.cost FROM StdCost_Master A, (SELECT part_no, MAX(cost_date) FROM StdCost_Master GROUP BY part_no) B WHERE A.part_no = B.part_noIt doesn't allow this statement because QStdCost_Master_Current is a view created with a join of 2 tables which is not permitted in an Outer join ( the error message says so ). I need the outer join because the QStdCost_Master_Current view may not contain the standard cost of all items.Can I achieve this by some other means ? Basically I want to have a column of the latest standard costs of the items in the view. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-08 : 07:48:04
|
| Do not use the *= syntax for outer joins, use the ANSI LEFT/RIGHT/INNER JOIN syntax for joins in SQL Server. The older syntax is obsolete and causes join issues like the one you're experiencing. |
 |
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-09 : 01:15:08
|
The ANSI LEFT/RIGHT/INNER JOIN syntax worked. Thanks a lot !  |
|
|
|
|
|
|
Outer join with view