Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    St. Charles, IL
    Posts
    2

    Unanswered: Error when creating a view

    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,
    Ryan

  2. #2
    Join Date
    Apr 2002
    Posts
    23

    Cool

    Why 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.
    DTS -> Oracle

  3. #3
    Join Date
    Apr 2002
    Location
    St. Charles, IL
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •