Hello,
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,
CSQLocation.Location, CSQMeasure.Measure_Name
FROM CSQLocation, CSQMeasure
Here is the view that I'm getting the error on
SELECT ISNULL(CSQObjective.Objective, 0) AS Objective,
vw_getLocationMeasure.Location,
vw_getLocationMeasure.Measure_Name,
vw_getLocationMeasure.Measure_ID
FROM CSQObjective RIGHT OUTER JOIN
vw_getLocationMeasure ON
CSQObjective.Location_ID = vw_getLocationMeasure.Location_ID
AND
CSQObjective.Measure_ID = vw_getLocationMeasure.Measure_ID
Does anyone have any ideas?
Thanks,
RyanWhy you need to use the other view (getLocationMeasure)? It seems the view doesn't add any value. Why don't you just use the two original tables? Just a thought.|||The reason I'm querying a view is because I wasn't able to get the information I was needed with one query and joins. Here is a break down of my db and what I need
MEASURE TABLE
Measure_ID
Measure_Name
LOCATION TABLE
Location_ID
Location
OBJECTIVE TABLE
Objective_ID
Location_ID
Measure_ID
Objective
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.
Thanks
Ryan
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment