Informix Outer Join

Databases
1

I know this is a bit long but bear it with me.

I have an Informix database with thousands of records. The query I need to do needs information from four tables.

This Informix database records all the calls received and done in a call center.

TABLE A has all the information about the call itself: ID (the id is really a four field combination), start, end, duration, originator_nbr, destination_nbr…

TABLE B has information about how the called was handled: how long was in queue, how long it took to be answered, the time elapsed till the call was answered, the id of the agent who answered the call, etc

TABLE C has information about the agent: agent_id, agent_name….

TABLE E has information about the queue itself: Queue_id , Queue_name… but it doesn’t have a direct relation with TABLES A, B or C. A fifth table, TABLE D, needs to be used instead.

The relationships are as follows

TABLE A and TABLE B are related by four fields: sessionid, sessionseqnum, profileid, nodeid
TABLE A and TABLE D are related by four fields: sessionid, sessionseqnum, profileid, nodeid
TABLE D and TABLE E are related by two fields: targeted, profileid
TABLE B and TABLE Care related by only one field: resourceid

Right now, my query looks like this:

 
SELECT  DISTINCT a.nodeid ||'-'|| a.sessionid ||'-'|| a.sessionseqnum as CODE, a.contactdisposition AS DISPOSITION, a.originatordn AS ORIGIN, a.callednumber AS DESTINATION, b.ringtime, b.talktime, a.startdatetime AS START, a.originatortype,  e.csqname as QUEUE,                        c.resourcename as AGENT
FROM    a, b, e, d, c
WHERE   
                          a.sessionid=b.sessionid AND a.sessionSeqNum=b.sessionSeqNum AND a.profileid=b.profileid AND a.nodeid=b.nodeid AND
                          a.sessionid=d.sessionid AND a.sessionSeqNum=d.sessionSeqNum AND a.profileid=d.profileid AND a.nodeid=d.nodeid AND
                          d.targetid=e.recordid AND d.profileid = e.profileid AND
                          c.resourceid=b.resourceid AND
        (a.startdatetime >= TO_DATE('2013-1-1 00:00:00', '%Y-%m-%d %H:%M:%S')-INTERVAL(1) HOUR TO HOUR) AND 
        (a.startdatetime <= TO_DATE('2013-1-31 23:59:59', '%Y-%m-%d %H:%M:%S')-INTERVAL(1) HOUR TO HOUR) AND
                          a.callednumber='123456789'

This old style inner join is not mine, is the translation that any tool I use does when I connect using a ODBC connection to Informix.
This query works fine but it only gives me the list of calls that have been answered by an agent when the user dialled ‘123456789’ directly. It doesn’t include the calls that were missed because all agents were busy and didn’t have the time to answer the call.

So I need to do an outer join for that… but for more information that I have searched, everything that I tried, I don’t seem to be able to include those records.

2

can we see some of what you tried, and the error messages that they produced?

3

There were no errors. The results were exactly the same as the inner join. I don’t have the exact queries because I was testing and testing and everything on the original query

I’ve tried to write regular outer join syntax (you know FROM a left outer join b on a.id=b.id style) and the syntax used in IBM Informix help website
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls679.htm

I also tried to do a single query to get the non-answered calls so I could do a UNION with the original

4

would you please try it again, and show that query

5

Sure. I’ll try to reproduce it and post it here

6

This is an example. In this case I tried to simplify a bit and I only used tables A, B and C

SELECT a.callednumber, a.applicationname, a.sessionid, a.contactdisposition, a.startdatetime, c.resourcename
FROM 
(a INNER JOIN b ON (a.sessionid = b.sessionid) AND (a.sessionseqnum = b.sessionseqnum) AND (a.nodeid = b.nodeid) AND (a.profileid = b.profileid)) LEFT JOIN c ON b.profileid = b.profileid
WHERE 
(((a.callednumber)='123456789') AND ((Year(a.startdatetime))=2013) AND ((Month(a.startdatetime))=1))

i also tried this but it takes ages and it doesn’t give the desired result, no even close

SELECT a.callednumber, a.applicationname, a.sessionid, a.contactdisposition, a.startdatetime, c.resourcenameFROM (a LEFT JOIN b ON (a.sessionid = b.sessionid) AND (a.sessionseqnum = b.sessionseqnum) AND (a.nodeid = b.nodeid) AND (a.profileid = b.profileid)) LEFT JOIN c ON b.profileid = c.profileid
WHERE (((a.callednumber)='123456789') AND ((a.applicationname)<>'') AND ((Year(a.startdatetime))=2013) AND ((Month(a.startdatetime))=1))
ORDER BY a.startdatetime

I’ve tried Informix syntax like this

SELECT  DISTINCT         a.nodeid ||'-'|| a.sessionid ||'-'|| a.sessionseqnum as ID, 
		 a.contactdisposition AS DISPOSITION, 
		 a.originatordn AS ORIGIN, 
         a.callednumber AS DESTINATION, 
		 b.ringtime, 
		 b.talktime, 
		a.startdatetime, 
        a.originatortype,
		e..e.name as cola,
		c.resourcename as Agente
FROM  c  (OUTER b, OUTER a), e, d
WHERE   
		a.sessionid=b.sessionid AND a.sessionSeqNum=b.sessionSeqNum AND a.profileid=b.profileid AND a.nodeid=b.nodeid AND
		a.sessionid=d..sessionid AND a.sessionSeqNum=d.sessionSeqNum AND a.profileid=d.profileid AND a.nodeid=d.nodeid AND
		d.targetid=e.recordid AND d.profileid = e.profileid AND
		c.resourceid=b.resourceid AND
        (a.startdatetime >= TO_DATE('2013-1-1 00:00:00', '%Y-%m-%d %H:%M:%S')-INTERVAL(1) HOUR TO HOUR) AND 
        (a.startdatetime <= TO_DATE('2013-1-31 23:59:59', '%Y-%m-%d %H:%M:%S')-INTERVAL(1) HOUR TO HOUR) AND
		a.callednumber='123456789'

This one does result in a error:

Error: -201
[Informix][Informix ODBC Driver][Informix]A syntax error has occurred. (SQLPrepare[-201] at ext\pdo_informix\informix_driver.c:131)

7

sorry, i don’t think i’m going to be able to help you

that informix syntax is creepy

8

I think so too. It drives me crazy. Sometimes I can write standard SQL and it gets “translated” into Informix SQL but it doesn’t always work.

Thank you for trying anyway.

I’m going to check the data too and see if there’s any other condition that I can apply. This software also adds a level of complexity. The way they built the database is kind of complicated.