I'm getting the following error when I try to excute a view against my SQL 7.0 database:
The query processor encountered an unexpected error during execution.
When I click the help button this is what I get:
An ODBC error has been generated. You might have deleted a record that has a foreign key value related to it, or you might have violated a check constraint.
The view I'm trying to create is a query between a table and another view. Here is the other view:
SELECT CSQLocation.Location_ID, CSQMeasure.Measure_ID,
FROM CSQLocation, CSQMeasure
Here is the view that I'm getting the error on
SELECT ISNULL(CSQObjective.Objective, 0) AS Objective,
FROM CSQObjective RIGHT OUTER JOIN
CSQObjective.Location_ID = vw_getLocationMeasure.Location_ID
CSQObjective.Measure_ID = vw_getLocationMeasure.Measure_ID
There are 4 measures, there are about 100 locations, and there is 1 objective per measure per location. Does that make sense? So there will be about 400 obectives in the objective table. What I was trying to do is to return all the Locations, and all the Measures even if there was not and Objective associated with them. If there are no objectives I would like to return a 0, that is where the ISNULL came from. This is the output I would like to have
LOCATION - MEASURE - OBJECTIVE
Chicago - Clean Air - 78.8
Chicago - Clean Water - 75
Chicago - Clean Dumps - 0
Chicago - Clean Roads - 0
Dallas - Clean Air - 90
Dallas - Clean Water - 70
Dallas - Clean Dumps - 25
Dallas - Clean Roads - 0
etc.... Does that make sense? With all the joins I've done I have only been able to return the Locations and Measures where there is acutally a value for the Objective. I would like to return 0 if there is no objective.