December 18, 2008 at 11:49 am
This query works fine pulling from a SQL 2000 database. However, the EXACT same query pulling from a SQL 2005 import of the that same SQL 2000 database (development server is SQL 2005) returns the same 3 records, in addition to one of those records being repeated 21 times (3 unique records - 23 rows).
The Change_Request table holds the main data. All that I need from from Change_Approval is the first and last name of the approver. I don't need the Change_Assessment table, as the results are the same with all those references stripped out, but I left the query intact for this question.
How do I get that one record from repeating 21 times???
Here's the script:
SELECT "Change_Request"."Sequence", "Change_Request"."Status ID", "Change_Request"."Start Date", "Change_Request"."End Date", "Change_Request"."Initiator LName", "Change_Request"."Initiator FName", "Change_Approval"."Approver LName", "Change_Approval"."Approver FName", "Change_Request"."Actual End Date", "Change_Request"."Urgency ID", "Change_Assessment"."Tech Assessment", "Change_Assessment"."Bus Assessment", "Change_Request"."Request Date", "Change_Request"."State", "Change_Request"."Change Description"
FROM ("Magic"."_SMDBA_"."Change Request" "Change_Request" LEFT OUTER JOIN
"Magic"."_SMDBA_"."Change Approval" "Change_Approval" ON "Change_Request"."Sequence"="Change_Approval"."Seq.Change") LEFT OUTER JOIN
"Magic"."_SMDBA_"."Change Assessment" "Change_Assessment" ON "Change_Request"."Sequence"="Change_Assessment"."Seq.Assessor"
WHERE (("Change_Request"."Urgency ID"=N'1 - critical' OR "Change_Request"."Urgency ID"=N'2 - high' OR "Change_Request"."Urgency ID"=N'3 - medium') OR ("Change_Assessment"."Tech Assessment"=N'HIGH' OR "Change_Assessment"."Tech Assessment"=N'MEDIUM') OR ("Change_Assessment"."Bus Assessment"=N'HIGH' OR "Change_Assessment"."Bus Assessment"=N'MEDIUM')) AND "Change_Request"."State"=N'O' AND
("Change_Request"."Request Date">= dateadd("day",-8,getdate()))
ORDER BY "Change_Request"."Status ID", "Change_Request"."Sequence", "Change_Request"."Start Date"
December 18, 2008 at 12:49 pm
I hate to state the obvious here, but since you say you get the same number of results even without the joins, it would seem that when you imported your data from one db to another you duplicated something. Take a look at the data in your tables and look for someplace you have multiples. You seem to only have a few records so this should be pretty easy to spot by hand. Look for perhaps something that's the same but with 2 different statuses or some such.
-Luke.
December 18, 2008 at 1:21 pm
BINGO! You're correct. I had too much faith in our data. I queried just the "Change Request" view, and it became obvious right away. Many records have been repeated in that view. Now I have to solve that problem!
Thanks!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply