Duplicate lines but I cant seem to remove them,
I have a script (an ugly one but bear in mind I am new to SQL and I am limited in what I can do so you will no doubt hate the case statements in it) that is basically pulling back information for a bug history table but only the last transition status of the bug. The problem is that the way I have written it it still brings back duplicate bug id's when the date_modified is the same for the transition status.
select distinct bughist.id,bughist.bug_id,bughist.field_name,
case
when bughist.old_value = 10 then 'New'
when bughist.old_value = 20 then 'Feedback'
when bughist.old_value = 40 then 'Confirmed'
when bughist.old_value = 50 then 'Assigned'
when bughist.old_value = 55 then 'Deferred'
when bughist.old_value = 60 then 'RFT'
when bughist.old_value = 80 then 'Resolved'
when bughist.old_value = 90 then 'Released'
when bughist.old_value = 95 then 'Rejects'
Else NULL
End as 'Old_value',
case
when bughist.new_value = 10 then 'New'
when bughist.new_value = 20 then 'Feedback'
when bughist.new_value = 40 then 'Confirmed'
when bughist.new_value = 50 then 'Assigned'
when bughist.new_value = 55 then 'Deferred'
when bughist.new_value = 60 then 'RFT'
when bughist.new_value = 80 then 'Resolved'
when bughist.new_value = 90 then 'Released'
when bughist.new_value = 95 then 'Rejects'
Else NULL
End as 'New_value',bughist.type, FROM_UNIXTIME(bughist.date_modified) as 'Date Modified',FROM_UNIXTIME(bug.date_submitted) as 'Date Submitted',
case
WHEN bug.severity = 10 then 'Feature Request'
WHEN bug.severity = 20 then 'Trivial'
WHEN bug.severity = 50 then 'Minor'
WHEN bug.severity = 60 then 'Major'
WHEN bug.severity = 70 then 'Crash'
else NULL
END as 'Severity',
case
WHEN bug.status = 10 then 'New'
WHEN bug.status = 20 then 'Feedback'
WHEN bug.status = 40 then 'Confirmed'
WHEN bug.status = 50 then 'Assigned'
WHEN bug.status = 55 then 'Deferred'
WHEN bug.status = 60 then 'RFT'
WHEN bug.status = 80 then 'Resolved'
WHEN bug.status = 90 then 'Released'
WHEN bug.status = 95 then 'Rejects'
else NULL
END as 'Status',
case
WHEN bug.resolution = 10 then 'New'
WHEN bug.resolution = 20 then 'Fixed'
WHEN bug.resolution = 30 then 'Reopened'
WHEN bug.resolution = 40 then 'Not able to reproduce'
WHEN bug.resolution = 50 then 'Not Fixable'
WHEN bug.resolution = 60 then 'Duplicate'
WHEN bug.resolution = 70 then 'No CVhange Required'
WHEN bug.resolution = 100 then 'Not a Defect'
WHEN bug.resolution = 110 then 'As Designed'
WHEN bug.resolution = 120 then 'Feature Request'
else NULL
END as 'Bug Table Resolution'
from bugsystem_bug_history_table bughist
left join bugtracker.bugsystem_bug_table bug
on bughist.bug_id = bug.id
left outer join bugsystem_bug_history_table bughist2
on (bughist.bug_id = bughist2.bug_id and bughist.date_modified < bughist2.date_modified)
where bughist.field_name = 'status' and bug.project_id in (1,3,4,13) and bughist2.bug_id is null and bughist.date_modified >= '1356998400'
group by bughist.id
order by bughist.bug_id
This returns rows like this but in the example below you can see I have a row with same bug id because the date modified is the same as the previous one. (The second row is the correct status
id|bugid|field_name|old_value| New_Value|type|date_Modified |Date_submitted.......
1 |1 |Status |Confirmed|Released |0 |2013-12-04 16:04:10|......
2 |1 |Status |Released |Rejected |0 |2013-12-04 16:04:10|......
3 |2 |Status |New |Confirmed |0 |2013-02-02 09:00:00|...
I have tried using distinct but it does not seem to rid me of those duplicates. Each line has a unique ID so was thinking perhaps I need to do something in my query to get rid of the duplicates using the ID but I can't figure it out.
Any advice would be appreciated.
Subject
Written By
Posted
Duplicate lines but I cant seem to remove them,
February 18, 2014 02:58AM
February 19, 2014 07:27PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.