3 Replies Latest reply: Nov 21, 2014 2:41 PM by Jared Papador RSS

    Script Finished... but still code left

    Jeremy Schmitt


      I am reworking an incrimental qvd that I noticed was not working correctly. I made some slight changes but nothing that should affect the script. I run debug with 1,000 records and after it stores data into the qvd it says "Script Finished", even though there is more code to run. When I hit ok, it says "Execution of script failed". I don't understand what is happening. The log says "general scipt error" after the store script that I marked below. Any thoughts?


      /*********Set the max date of VW_CUSTOMER_CONTACT_ALL table from yesterday as a variable*********/
      IncrimentalMax:
      load * from max_contacts.qvd (qvd);
      let maxdate = text(date(peek('temp1',0,'pointless'),'YYYYMMDD'));
      drop table IncrimentalMax;

      /**************load of VW_CUSTOMER_CONTACT_ALL table****************/
      Contacts:
      LOAD
       
      HOST_ACCOUNT_NBR,
       
      SOURCE_XREF_NBR,
       
      AGENT,
       
      PHONE_NBR    as [Call Phone Number],
      LVOX_ATTEMPTS_DISPO   as [Call Result Code],
      SOR_ID    as [Call SOR ID],
      SYSTEM_OF_RECORD,
      TALKTIME                as Total_Talk_Time,
      WORKTIME                                      as Total_Work_Time,
      SEQUENCE_NBR,
      ADD_SOURCE                                    as [Call Add Source],
      SEQUENCE_NBR &''& CALL_START_TIME             as Primary_Key,/*Multiple records/calls can exist for a sequence#, so call time added to create unique key*/
      ADD_DATE,
      RESULT_CODE,
      CALL_END_TIME,
      CALL_START_TIME
      ;


      SQL SELECT
          ADD_DATE,
           ADD_SOURCE,
           AGENT,
           CALL_END_TIME,
           CALL_START_TIME,
           DIALER_CALL_DATE,
           DIALER_CALL_TIME,
           HOST_ACCOUNT_NBR,
           PHONE_NBR,
      LVOX_ATTEMPTS_DISPO,
           SEQUENCE_NBR,
           SOR_ID,
           SOURCE_XREF_NBR,
           SYSTEM_OF_RECORD,
           TALKTIME,
           WORKTIME,
           RESULT_CODE

      FROM BTOffer.dbo.VW_CUSTOMER_CONTACT_ALL with (nolock);


      /**************Manipulation of fields and limitation of data****************/
      Contacts2:
      LOAD
        
      if(SYSTEM_OF_RECORD='BFRAME',num(HOST_ACCOUNT_NBR,'0000000000'),
        
      if(SYSTEM_OF_RECORD='MJCP',num(HOST_ACCOUNT_NBR,'000000000000'),
        
      if(SYSTEM_OF_RECORD='FDR',num(HOST_ACCOUNT_NBR,'0000000000000000'))))  as [Call Account ID],
        
      num(SOURCE_XREF_NBR,'0000000000000')  as [XRef Number],
        
      num(AGENT,'00000')  as [Call Agent],
        
      date(CALL_END_TIME,'hh:mm:ss')                              as [Call End Time],
        
      date(CALL_START_TIME,'hh:mm:ss')                              as [Call Start Time],
        
      date(CALL_START_TIME,'MM/DD/YYYY')                          as [Call Date],
      [Call Phone Number],
      [Call Result Code],
      [Call SOR ID],
      SYSTEM_OF_RECORD as [Call Database Source],
      Total_Talk_Time,
      Total_Work_Time,
      [Call Add Source],
      ADD_DATE,
      RESULT_CODE,
      CALL_END_TIME,
      CALL_START_TIME,
      Primary_Key

      Resident Contacts

      WHERE text(date(ADD_DATE,'YYYYMMDD')) > '20121231' /*only data starting in 2013*/
      ///*comment these lines for full reload*/ and text(date(ADD_DATE,'YYYYMMDD')) >= '$(maxdate)' ;
      ///*comment these lines for full reload*/ CONCATENATE LOAD * FROM QVD_CONTACTS.qvd (qvd) where not exists(Primary_Key) AND  ADD_DATE >= Date(today(0)-90)

      -------->;
      STORE * FROM Contacts2 INTO QVD_CONTACTS.qvd (qvd); SLEEP 5000;

      Drop Table Contacts;

      /*****Capture the max (most recent) date from the VW_CUSTOMER_CONTACT_ALL table*****/
      currentmax:
      load *;
      sql select max(ADD_DATE) as temp1
      from BTOffer.dbo.VW_CUSTOMER_CONTACT_ALL ;
      STORE * FROM currentmax INTO max_contacts.qvd(qvd);
      DROP TABLE currentmax;


      Drop Table Contacts;

      BFRAME_Contacts:
      LOAD
      [Call Account ID]  as  [Call BFRAME Account ID],
      [Call Agent]     as  [Call BFRAME Agent],
      [Call End Time]   as  [Call BFRAME End Time],
      [Call Start Time]     as  [Call BFRAME Start Time],
      [Call Date]     as  [Call BFRAME Date],
      [Call Phone Number]     as  [Call BFRAME Phone Number],
      [Call Result Code]  as  [Call BFRAME Result Code],
      [Call SOR ID]     as  [Call BFRAME SOR ID],
      [Call Database Source] as  [Call BFRAME Database Source],
      Total_Talk_Time     as  [Call BFRAME Total Talk Time],
      Primary_Key         as  [Call BFRAME Primary Key],
      [Call Add Source]     as [Call BFRAME Add Source]

      RESIDENT Contacts2 where [Call Database Source]='BFRAME' ;
      STORE * FROM BFRAME_Contacts INTO Contacts_BFRAME.qvd (qvd); SLEEP 5000;
      Drop Table BFRAME_Contacts;


      MJCP_Contacts:
      LOAD
      [Call Account ID]  as  [Call MJCP Account ID],
      [Call Agent]     as  [Call MJCP Agent],
      [Call End Time]   as  [Call MJCP End Time],
      [Call Start Time]     as  [Call MJCP Start Time],
      [Call Date]     as  [Call MJCP Date],
      [Call Phone Number]     as  [Call MJCP Phone Number],
      [Call Result Code]  as  [Call MJCP Result Code],
      [Call SOR ID]     as  [Call MJCP SOR ID],
      [Call Database Source] as  [Call MJCP Database Source],
      Total_Talk_Time     as  [Call MJCP Total Talk Time],
      [Call Primary Key]     as  [Call MJCP Primary Key],
      [Call Add Source]     as [Call MJCP Add Source]

      RESIDENT Contacts2 where [Call Database Source]='MJCP';
      STORE * FROM MJCP_Contacts INTO Contacts_MJCP.qvd (qvd); SLEEP 5000;
      drop table MJCP_Contacts;

      drop table Contacts2;

       

        • Re: Script Finished... but still code left
          Jonathan Poole

          The line of code is actually doing 3 things...

          ;STORE * FROM Contacts2 INTO QVD_CONTACTS.qvd (qvd); SLEEP 5000;


          1. the first ';' is finishing the preceding load

          2. Writing a loaded table to disk in QVD format:  STORE * FROM Contacts2 INTO QVD_CONTACTS.qvd (qvd);

          3. Pausing for 5 seconds after the QVD write


          I would suggest splitting the code into 3 lines and then tracking which one is the cause of the issue


          ;

          STORE * FROM Contacts2 INTO QVD_CONTACTS.qvd (qvd);

          SLEEP 5000;

          • Re: Script Finished... but still code left
            Srikanth P

            Some times we got this error even though there is no script errors.

             

            1. If you are writing the qvd into a folder, check the folder physically exist or not ?

             

            2. Please close all the session & try to reload the app. (some time file lock)

            • Re: Script Finished... but still code left
              Jared Papador

              Make sure you have permission to write to the folder that you are writing the QVD to.  If you are overwriting an existing qvd sometimes it helps to delete it from the file location and have it create a new one.  Otherwise in your load script change the name of the QVD you are trying to write and see if it completes.