• Replies 7 replies
  • Subscribers 338 subscribers
  • Views 28 views
  • Users 0 members are here
Related

NCOA in the UK

Mark Steel (Past Staff Member)
$organization

Hello

I have been looking at the NCOA utility for our Box Office Manager. However I have come across a problem during the processing stage. I believe the field of sstate must reference TR_STATE, however TW_NCOA_INCOMING only accepts 2 characters in the sstate field (which does not match our TR_STATE table which features ID's with more than 2 characters).

Firstly I was unable to import the data as sstate contained more than two characters, so for testing purposes I amended the table to allow more than two characters.

I then tried to process the data however I recieved an error relating to FK_T_ADDRESS_TR_STATE_state.

Has anyone else experienced this problem or does anyone have a solution to this.

We would like to begin cleaning our data using QAS however QAS will return a state value of more than two characters.

Any help would be much apprechiated.

Thanks

 

Mark

Parents
  • $organization

    Hi Mark,

    We'd like to step in and take a look at this in more detail.  Would you please get a support ticket opened up?  Someone in development is waiting to pick it up when it comes in.

    Richie

  • $organization in reply to Mark Steel (Past Staff Member)

    Hi Guys,

    I'm also having this problem after having cleaned our data with QAS.  This is what I've found that I had to do atm, (all on test!).

     

    1.  Modify the tw_ncoa_incoming table as many of the fields are too small, specifically the zip/postcode fields.

    2.  Make sure that there is a rule in the TX_NCOALINK_RESULT_ACTION for the QAS result codes.

    3.  Make sure all the states in the return data are in the TR_States table.

     

    I've even run the following query to make sure that there are no states there shouldn't be, but still throws that error.

    select    tw_ncoa_incoming.*
    from    tw_ncoa_incoming
    inner join    t_address
        on    tw_ncoa_incoming.address_no = t_address.address_no
    where    not exists (select    1
                        from    tr_state
                        where    id = tw_ncoa_incoming.nstate and
                                country = t_address.country)

     

    If you could pass on any details you find from the ticket would be grateful.

     

    Thanks!

     

  • $organization in reply to Simon Davidson

    I think I've found the problem.

    Looking at the AP_NCOA_PROCESS procedure, when it tries process the data it loads the data from the cursor into multiple variables.  I've noticed the state variable is only two characters, so even though all of the states are within the TR_State table, they get truncated down.  I'm in the process of modifying the stored procedure and going to test it.

    Will let you know how it goes.

Reply
  • $organization in reply to Simon Davidson

    I think I've found the problem.

    Looking at the AP_NCOA_PROCESS procedure, when it tries process the data it loads the data from the cursor into multiple variables.  I've noticed the state variable is only two characters, so even though all of the states are within the TR_State table, they get truncated down.  I'm in the process of modifying the stored procedure and going to test it.

    Will let you know how it goes.

Children