Any ideas how to find some duplicates?

The database is INFORMIX- Its closest relative, also by IBM, would be DB2

This is the result of summarizing a stored procedure of more than 900 lines

But if you want the schema, be my guest :stuck_out_tongue:

Just look the info for the very first table, AgentCallDetail. As you can see, most of the relationships are based on 4-5 fields. I don’t understand why but that’s how it is.

[quote=“SpacePhoenix, post:20, topic:115193”]( @r937 would that be the same query to get the table structure no matter what database server @molona is using) [/quote]sorry, no

SHOW CREATE TABLE is mysql only

A bit off topic, but if your working on cisco I just wanted to point out that do you have the privileges to link that document publicly here? Just to make sure you won’t get in trouble by doing so.

It is public information out there, this is a link to their own resources and you can find it in Google, so I think so, yes.

Does not really look that bad, other than it will have horrible performance :slight_smile:

I will come back and take a look tonight or tomorrow night after work when I have some time to digest the query.

Though, since you are actually using a Cisco service, I believe the duplicates are created by the query/subqueries, instead of being a race condition. As a side question, have you verified that the information you pull actually exist as duplicates in the said tables?

It actually takes less than 2 secs

[quote=“TheRedDevil, post:25, topic:115193”]
I believe the duplicates are created by the query/subqueries, instead of being a race condition
[/quote]It’s the table agentconnectiondetail the one that has the 13 records, so it looks like the duplicates are real

Just to clarify, is this the only agent records that is duplicated in the entire table. Or is it one of many?

This is the only agent record duplicated in the month of December 2014. I suspect that there will other duplicated in other months but that’s what I’m checking right now.

@molona, @molona, @molona

Part of the problem is you violated what I consider one of the basic tenets of SQL query design

"Never use a sub-query when a join will do"

That query is so daunting, it’s no wonder everyone is struggling to try and help. I tried to strip out all the unnecessary sub-queries and bake it down to the essentials, then added the only sub-query I thought it really needed, and which I hope solves your need (and doesn’t have too many syntax errors - it’s been nigh on forever since I did any informix work - thank goodness :pray:) .

SELECT DISTINCT ccd.sessionid
     , ccd.sessionseqnum
     , ccd.contactdisposition
     , ccd.callednumber
     , ccd.origcallednumber
     , ccd.profileid
     , ccd.nodeid
     , crd.qindex
     , crd.qindex
     , cqdr.queuetime
     , acdr.talktime
     , acdr.ringtime
     , acdr.holdtime
     , acdr.worktime
  FROM contactcalldetail ccd
     , contactroutingdetail crd
     , contactqueuedetail cqdr
     , constactservicequeue csq
     , OUTER (SELECT acd.qindex
		   , acd.sessionid 
		   , acd.sessionseqnum 
		   , acd.talktime
		   , acd.ringtime
		   , acd.holdtime
		   , acd.worktime
		FROM agentconnectiondetail acd,
		     (SELECT sessionid
			    , MAX(ringtime) 
		         FROM agentconnectiondetail 
			GROUP BY sessionid) acd2
			WHERE acd.sessionid = acd2.sessionid
			  AND acd.ringtime = acd2.ringtime)) acdr
 WHERE ccd.startdatetime BETWEEN '2013-12-1 00:00:00' AND '2013-12-31 23:59:59' 
   AND ccd.callednumber = '6666' 
   AND DATEDIFF('ss',ccd.startdatetime, ccd.enddatetime) >=10
   AND cqdr.sessionid = crd.sessionid 
   AND cqdr.sessionseqnum = crd.sessionseqnum 
   AND cqdr.qindex = crd.qindex 
   AND cqdr.nodeid = crd.nodeid 
   AND cqdr.profileid = crd.profileid 
   AND cqdr.targetid = t.recordid
   AND csq.csqname like 'CSQ_CC%'
   AND ccd.sessionid = acdr.sessionid 
   AND ccd.sessionseqnum = acdr.sessionseqnum 
   AND crd.profileid = acdr.profileid 
   AND ccd.nodeid = acdr.nodeid 
   AND (Nvl(crd.qindex, acdr.qindex) = acdr.qindex or acdr.qindex <= 0) 

Don’t you think I tried that? I’m trying to simplify this whole thing in just one query!

It doesn’t work

And trust me, I know it is daughting although I appreciate the help and I’m very grateful.

But it gives an error right where the OUTER is

edit: You removed the resource table for any particular reason? whenever I tried that, I got a different number

Looks like there is an extra ) before )) acdr, it should be ) acdr

1 Like

That should be it. Too much copy/pasting to try to organize it…

Now it tells me that it can’t find the column ringtime

Error: Column (ringtime) not found in any table in the query (or SLV is undefined).
SQLState:  IX000
ErrorCode: -217
Position: 790

Change

, MAX(ringtime)

to

, MAX(ringtime) AS ringtime

Unbelievable!

Error: Column (t) not found in any table in the query (or SLV is undefined).
SQLState:  IX000
ErrorCode: -217

It seems that “t” comes from this

AND cqdr.targetid = t.recordid

Ugh…

I think you it’s just you needing to add these two fields to the OUTER sub-query

acd.profileid
acd.nodeid

edit:

Change the t. to constactservicequeue. as well

I think this is it. As t as an alias doesn’t seem to be defined.

Executing… but it is taking longer than normal. The original neded around 2 secs

But didn’t produce what you needed right?

No but I’m suspicious that this one is taking so long… it is more than one minute now.