SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,216
    Mentioned
    236 Post(s)
    Tagged
    1 Thread(s)

    Informix Outer Join

    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:
    Code:
     
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by molona View Post
    So I need to do an outer join for that…
    can we see some of what you tried, and the error messages that they produced?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,216
    Mentioned
    236 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    can we see some of what you tried, and the error messages that they produced?
    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/infoce...oc/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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by molona View Post
    I've tried to write regular outer join syntax
    would you please try it again, and show that query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,216
    Mentioned
    236 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    would you please try it again, and show that query
    Sure. I'll try to reproduce it and post it here

  6. #6
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,216
    Mentioned
    236 Post(s)
    Tagged
    1 Thread(s)
    This is an example. In this case I tried to simplify a bit and I only used tables A, B and C

    Code:
    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

    Code:
    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

    Code:
    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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't think i'm going to be able to help you

    that informix syntax is creepy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,216
    Mentioned
    236 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    that informix syntax is creepy
    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.


Bookmarks

Posting Permissions

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