Announcement

Collapse
No announcement yet.
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • CCS Director SQL question

    I have created a IVR application in which customers can call in and access their account information.

    I am using the ECC ver 4.35. Right now it is working perfectly. The customer calls in, enteres their account number and zip. I store those values in a cp field, make sure they match a record line in the Oracle table and if they do, I give them their account balance, next payment due and payment due date. These SQLExecute actions are all SELECT statements.

    I would like to populate another table so we have record of what the automated system told the customer and also use this table to analize who is using our automated system. I have created an Insert statement that I cannot seem to make work. I am sure it is a syntax issue.. Anyone have any suggestions? This is what I have in there now..

    insert into PWD_IVR_CALLED
    select * from PWD_IVR
    WHERE PWD_IVR.acctno = '%acctnum%';

    I am trying to say, populate table PWD_IVR_CALLED from PWD_IVR with the complete row of information for account number that is stored in the CP Filed %acctnum%

    Thanks...
    Last edited by chadd39; 08-14-2008, 01:05 PM.

  • #2
    Insert Statement

    Has anyone ever used an insert statement within the ECC? Just wondering if insert even works within the CCS

    Comment


    • #3
      Inserts do work.

      Do this.

      1. Make a new table to insert your data into, mytable for example. Give the table columns that will match up with the call profiles you are going to insert.. i.e. ani, acctnum, zip, etc. Make new call profiles in the ECC Director for any information you want to insert

      2. Make a new SQL Execute action and insert it into your script where it seems appropriate.. (I would insert it before you announce back to the customer the data they want so you capture the data on the call even if the customer hangs up after they get what they want.)

      3. Make sure you have actions to populate all the call profiles you create.. ani and dnis get populated for you.

      4. Put this statement (or modify it for your use) into your SQL Execute action:

      insert into mytable (ani, dnis, acctnum, zip, date) values (%ani%, %dnis%, %acctnum%, %zip%, sysdate);

      That should do it. Let me know if you want help.

      Comment


      • #4
        Inserts

        Hello.

        Your information has been helpful. I am making progress thanks to you and I am actually inserting data now!!!

        My problem is that I do not want to populate table STEVE_PWD_IVR_CALLED with CP fileds. I want to populate with data from another table named STEVE_PWD_IVR. This table has all of the account data that we are giving to the customer. The way you have it formated, I would need to create a CP field for each value in STEVE_PWD_IVR. I can do this, but there are 40 fields in the table.

        For testing I have done this in an SQLEXECUTE action

        Insert into steve_pwd_ivr_called
        (ACCTKEY, TOTALBAL,
        WATERBAL, SEWERBAL, PAYDATE, PAYMENT, POSTFLAG,
        BLKEY, BILLDATE, DUEDATE, PRINCIPAL, PENALTY, BLTO,
        BLFROM, DLNQDATE, BLRDDATE, PERIOD, NEXTDUEDT,
        INSTDUEAMT, ACCTNO, ACCTSTAT, ACCTCLASS, ACCTGRP,
        CUSTDESC, ADDRKEY, STNO, STNAME, STREETADDRESS,
        CITY, ZIP, DUNLEVEL, ARNGDWNAMT, ARNGDWNDUE,
        NXTBLDATE, LASTVALPADT, BILLZIP, CUTDATE, THETIME,
        TRACK)

        values

        (%balance%, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null, null, null,
        null, null, null, null, null, null, null, null, null, null, null, null, null,
        null, null, null);

        This populates acctkey in steve_pwd_ivr_called with the %balance% cp field. The value of the CP field balance is gotten by quering steve_pwd_ivr for the account number that the customer entered. I have attached a screen shot of how %balance% is created.

        I was hoping to populate the whole record line in one swoop by somehow saying copy everything in record line x from STEVE_PWD_IVRto STEVE_PWD_IVR_CALLED where the account number is equal to what the customer entered. (%acctnum%)

        Your method means that in order to get every value over to the STEVE_PWD_IVR_CALLED table, I need to create 40 sqlexecute statement for each field name and assign it to a cp field and then insert it that way. Your method works great if there ARE only a few values. If it has to be done that way, I guess i need to get to work, but any other suggestions would be welcomed..

        Thanks
        Attached Files
        Last edited by chadd39; 08-15-2008, 10:31 AM.

        Comment


        • #5
          I see.. yeah.. I don't know how to do it any other way..

          If you figure it out, post it and share.

          Comment


          • #6
            I have started down your path. I am still having issues. First i created a script called CPDECLARATIONS. Here it is:

            SELECT STEVE_PWD_IVR.ACCTKEY as acctkey,
            steve_pwd_ivr.TOTALBAL as totalbal,
            steve_pwd_ivr.WATERBAL as waterbal,
            steve_pwd_ivr.SEWERBAL as sewerbal,
            steve_pwd_ivr.PAYDATE as paydate,
            steve_pwd_ivr.PAYMENT as payment,
            steve_pwd_ivr.POSTFLAG as postflag,
            steve_pwd_ivr.BLKEY as blkey,
            steve_pwd_ivr.BILLDATE as billdate,
            steve_pwd_ivr.DUEDATE as duedate,
            steve_pwd_ivr.PRINCIPAL as principal,
            steve_pwd_ivr.PENALTY as penalty,
            steve_pwd_ivr.BLTO as blto,
            steve_pwd_ivr.BLFROM as blfrom,
            steve_pwd_ivr.DLNQDATE as dlnqdate,
            steve_pwd_ivr.BLRDDATE as blrddate,
            steve_pwd_ivr.PERIOD as period,
            steve_pwd_ivr.NEXTDUEDT as nextduedt,
            steve_pwd_ivr.INSTDUEAMT as instdueamt,
            steve_pwd_ivr.ACCTNO as acctno,
            steve_pwd_ivr.ACCTSTAT as acctstat,
            steve_pwd_ivr.ACCTCLASS as acctclass,
            steve_pwd_ivr.ACCTGRP as acctgrp,
            steve_pwd_ivr.CUSTDESC as custdesc,
            steve_pwd_ivr.ADDRKEY as addrkey,
            steve_pwd_ivr.STNO as stno,
            steve_pwd_ivr.STNAME as stname,
            steve_pwd_ivr.STREETADDRESS as streetaddress,
            steve_pwd_ivr.CITY as city,
            steve_pwd_ivr.ZIP as zip,
            steve_pwd_ivr.DUNLEVEL as dunlevel,
            steve_pwd_ivr.ARNGDWNAMT as arngdwnamt,
            steve_pwd_ivr.ARNGDWNDUE as arngdwndue,
            steve_pwd_ivr.NXTBLDATE as nxtbldate,
            steve_pwd_ivr.LASTVALPADT as lastvalpadt,
            steve_pwd_ivr.BILLZIP as billzip,
            steve_pwd_ivr.CUTDATE as cutdate,
            steve_pwd_ivr.THETIME as thetime,
            steve_pwd_ivr.TRACK as track
            From steve_pwd_ivr
            WHERE STEVE_PWD_IVR.ACCTNO = '%acctnum%';

            I next have created my insert statement:

            Insert into steve_pwd_ivr_called (ACCTKEY, TOTALBAL,
            WATERBAL, SEWERBAL, PAYDATE, PAYMENT, POSTFLAG,
            BLKEY, BILLDATE, DUEDATE, PRINCIPAL, PENALTY, BLTO,
            BLFROM, DLNQDATE, BLRDDATE, PERIOD, NEXTDUEDT,
            INSTDUEAMT, ACCTNO, ACCTSTAT, ACCTCLASS, ACCTGRP,
            CUSTDESC, ADDRKEY, STNO, STNAME, STREETADDRESS,
            CITY, ZIP, DUNLEVEL, ARNGDWNAMT, ARNGDWNDUE,
            NXTBLDATE, LASTVALPADT, BILLZIP, CUTDATE, THETIME,
            TRACK)
            values
            (%ACCTKEY%, %TOTALBAL%,
            %WATERBAL%, %SEWERBAL%, null, %PAYMENT%, %POSTFLAG%,
            %BLKEY%, null, null, %PRINCIPAL%, %PENALTY%, null,
            null, null, null, %PERIOD%, null,
            %INSTDUEAMT%, %ACCTNO%, %ACCTSTAT%, %ACCTCLASS%, %ACCTGRP%,
            %CUSTDESC%, %ADDRKEY%, %STNO%, %STNAME%, %STREETADDRESS%,
            %CITY%, %ZIP%, %DUNLEVEL%, %ARNGDWNAMT%, %ARNGDWNDUE%,
            null, null, %BILLZIP%, null, null,
            %TRACK%);

            Here are the field types:

            ACCTKEY NOT NULL NUMBER(9)
            TOTALBAL NUMBER
            WATERBAL NUMBER
            SEWERBAL NUMBER
            PAYDATE DATE
            PAYMENT NUMBER
            POSTFLAG VARCHAR2(1)
            BLKEY NUMBER
            BILLDATE DATE
            DUEDATE DATE
            PRINCIPAL NUMBER
            PENALTY NUMBER
            BLTO DATE
            BLFROM DATE
            DLNQDATE DATE
            BLRDDATE DATE
            PERIOD NUMBER
            NEXTDUEDT DATE
            INSTDUEAMT FLOAT(126)
            ACCTNO VARCHAR2(24)
            ACCTSTAT VARCHAR2(6)
            ACCTCLASS VARCHAR2(6)
            ACCTGRP VARCHAR2(10)
            CUSTDESC VARCHAR2(30)
            ADDRKEY NUMBER(9)
            STNO VARCHAR2(6)
            STNAME VARCHAR2(20)
            STREETADDRESS VARCHAR2(57)
            CITY VARCHAR2(20)
            ZIP VARCHAR2(10)
            DUNLEVEL NUMBER
            ARNGDWNAMT FLOAT(126)
            ARNGDWNDUE DATE
            NXTBLDATE DATE
            LASTVALPADT DATE
            BILLZIP VARCHAR2(20)
            CUTDATE DATE
            THETIME VARCHAR2(8)
            TRACK NUMBER

            Problems are:

            If a field in steve_pwd_ivr is blank, script will fail out. I remied this by making sure there is at least a 0 in a field if it is blank.

            Date fields will not insert over. I have nulled them for now. I have no idea why they will not insert

            Certain fields cannot use just %cpfield% they need '%cpfield%'. Even then, somtimes the value that gets inserted into steve_pwd_ivr_called is %. Attached is the data that is currently being inserted. Does anyone have any ideas? Basically, shoretel is not complying with basic SQL syntax,
            Attached Files

            Comment


            • #7
              I have been unable to insert all records. I am now just going to insert %acctno% into a holding table when a customer calls. At the end of the day, I have our DB guy insert all of the records who have their account number poplated in the holding table. Since the table steve_pwd_ivr is a daily static cut, this is working good.

              Comment


              • #8
                I would use a trigger to fire after insert.

                Comment


                • #9
                  Trigger is the path we ended up going. Works slick and is much less cumbersome than trying to do the insert through shoretel

                  Comment

                  Working...
                  X