{"action":"created","issue":{"url":"https://api.github.com/repos/kbower/tickettest1/issues/2017","repository_url":"https://api.github.com/repos/kbower/tickettest1","labels_url":"https://api.github.com/repos/kbower/tickettest1/issues/2017/labels{/name}","comments_url":"https://api.github.com/repos/kbower/tickettest1/issues/2017/comments","events_url":"https://api.github.com/repos/kbower/tickettest1/issues/2017/events","html_url":"https://github.com/kbower/tickettest1/issues/2017","id":196673229,"number":2017,"title":"Cust_asp table Frequently is missing acct_cd and thus causes a sqlalchemy primary key issue","user":{"login":"kentbower","id":5034099,"avatar_url":"https://avatars.githubusercontent.com/u/5034099?v=3","gravatar_id":"","url":"https://api.github.com/users/kentbower","html_url":"https://github.com/kentbower","followers_url":"https://api.github.com/users/kentbower/followers","following_url":"https://api.github.com/users/kentbower/following{/other_user}","gists_url":"https://api.github.com/users/kentbower/gists{/gist_id}","starred_url":"https://api.github.com/users/kentbower/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kentbower/subscriptions","organizations_url":"https://api.github.com/users/kentbower/orgs","repos_url":"https://api.github.com/users/kentbower/repos","events_url":"https://api.github.com/users/kentbower/events{/privacy}","received_events_url":"https://api.github.com/users/kentbower/received_events","type":"User","site_admin":false},"labels":[{"id":502436440,"url":"https://api.github.com/repos/kbower/tickettest1/labels/allcustomers","name":"allcustomers","color":"fbca04","default":false},{"id":502425680,"url":"https://api.github.com/repos/kbower/tickettest1/labels/bug","name":"bug","color":"ee0701","default":true},{"id":502436296,"url":"https://api.github.com/repos/kbower/tickettest1/labels/mis","name":"mis","color":"fef2c0","default":false},{"id":502436281,"url":"https://api.github.com/repos/kbower/tickettest1/labels/mor","name":"mor","color":"fef2c0","default":false}],"state":"open","locked":false,"assignee":{"login":"kbower","id":4621154,"avatar_url":"https://avatars.githubusercontent.com/u/4621154?v=3","gravatar_id":"","url":"https://api.github.com/users/kbower","html_url":"https://github.com/kbower","followers_url":"https://api.github.com/users/kbower/followers","following_url":"https://api.github.com/users/kbower/following{/other_user}","gists_url":"https://api.github.com/users/kbower/gists{/gist_id}","starred_url":"https://api.github.com/users/kbower/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kbower/subscriptions","organizations_url":"https://api.github.com/users/kbower/orgs","repos_url":"https://api.github.com/users/kbower/repos","events_url":"https://api.github.com/users/kbower/events{/privacy}","received_events_url":"https://api.github.com/users/kbower/received_events","type":"User","site_admin":false},"assignees":[{"login":"kbower","id":4621154,"avatar_url":"https://avatars.githubusercontent.com/u/4621154?v=3","gravatar_id":"","url":"https://api.github.com/users/kbower","html_url":"https://github.com/kbower","followers_url":"https://api.github.com/users/kbower/followers","following_url":"https://api.github.com/users/kbower/following{/other_user}","gists_url":"https://api.github.com/users/kbower/gists{/gist_id}","starred_url":"https://api.github.com/users/kbower/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kbower/subscriptions","organizations_url":"https://api.github.com/users/kbower/orgs","repos_url":"https://api.github.com/users/kbower/repos","events_url":"https://api.github.com/users/kbower/events{/privacy}","received_events_url":"https://api.github.com/users/kbower/received_events","type":"User","site_admin":false}],"milestone":null,"comments":1,"created_at":"2016-12-20T13:32:24Z","updated_at":"2016-12-20T13:32:24Z","closed_at":null,"body":"_Imported from trac ticket 2017.\nCreated by **sjohnson**\nOpened in trac: Nov 5, 2012, 11:10:56 AM\nLast modified in trac: Dec 23, 2015, 10:11:16 AM_\n***\n_by sjohnson:_\nThe fact that GERS has an index on CUST_CD, AS_CD, ACCT_CD, but that it is not a unique index is a problem because we are telling sqlalchemy it is the primary key.\n\nThat means that if this query ever returns any results, sqlalchemy will assume, as we told it to, that there is only one record though it isn't true:\n\nselect CUST_CD, AS_CD, ACCT_CD, count(*) from CUST_ASP group by CUST_CD, AS_CD, ACCT_CD having count(*) > 1;\n\nAt best this will cause undefined results, but also potential data corruption, data loss, etc.  Any updates to the record what we think is one record will hit more than one record in the database and the data from only one record will ever be visible, though there are multiple records.  Clearly, this isn't acceptable as a final solution.\n\nThe above sql returns results for all our clients:\nKanes: 111 occurrences\nMiskelly: 5688 occurrences\nMorris: 2145 occurrences\nEl Dorado: 7044 occurrences\nIvan Smith: 2 occurrences\nSteinhafels: 1042 occurrences\n\nBest also get Rob's input since he defined this as the primary key and probably Sameer's as well if he uses this table (I am very unfamiliar with how much or where Loft uses this currently).\n\nIf our customers agree there shouldn't ever be more than one account number for any finance company and any given customer, which seems extremely reasonable to me, then a potential solution is to make this a unique index, which would require data cleanup.  I suspect gers couldn't since it allows for null accountnumber. \n\nWe need someone who understands GERS fairly well to explain what it means to have more than one (maybe Wayne/Doug?/JR).  (Rather than speculate on an interpretation, as we did for Wayne below, I think we just need to ask so we don't bias their answer we our preconceived suppositions).\nSecond, we need to find out what it means when there is a null accountnumber.  Are these records used for anything?  What?\nIs it correct to assume there should never be more than one given an actual accountnumber (not null)?  If not, what distinguishes these records?  (EXPIR_DT? APP_CREDIT_TP?, etc?... it still baffles me you'd allow the same account number)\n"},"comment":{"url":"https://api.github.com/repos/kbower/tickettest1/issues/comments/268243716","html_url":"https://github.com/kbower/tickettest1/issues/2017#issuecomment-268243716","issue_url":"https://api.github.com/repos/kbower/tickettest1/issues/2017","id":268243716,"user":{"login":"kentbower","id":5034099,"avatar_url":"https://avatars.githubusercontent.com/u/5034099?v=3","gravatar_id":"","url":"https://api.github.com/users/kentbower","html_url":"https://github.com/kentbower","followers_url":"https://api.github.com/users/kentbower/followers","following_url":"https://api.github.com/users/kentbower/following{/other_user}","gists_url":"https://api.github.com/users/kentbower/gists{/gist_id}","starred_url":"https://api.github.com/users/kentbower/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kentbower/subscriptions","organizations_url":"https://api.github.com/users/kentbower/orgs","repos_url":"https://api.github.com/users/kentbower/repos","events_url":"https://api.github.com/users/kentbower/events{/privacy}","received_events_url":"https://api.github.com/users/kentbower/received_events","type":"User","site_admin":false},"created_at":"2016-12-20T13:32:24Z","updated_at":"2016-12-20T13:32:24Z","body":"_**sjohnson** trac comment on Nov 8, 2012, 9:12:40 AM:_\n\nThat would explain a lot.  The problem: so now you've merged a customer and there are two finance company account records in the database where only one logically exists.  The customer merge should probably have merged the records instead of allowed a duplicate.  How should Loft (or anyone) know which record to use?  What supplementary data besides the primary key is important?\n\nOn 11/7/2012 9:05 PM, Doug Warner wrote:\n> Hi Scott,\n>  \n> This is what I have found. I ran your script  on our live system and found 1042 records. I created a table with those customer codes.  Then I ran this\n>   1  select a.cust_cd from temp_dw a, cust_cmnt b\n>   2  where a.cust_cd =b.cust_cd\n>   3* and text like 'Customer code change%'\n>  \n> 1099 records came back.  Some were merged together wth multiple codes or multiple codes, We cab talk on Friday  when I am back in the office.\n>  \n> Doug\n> On Wed, Nov 7, 2012 at 2:14 PM, Scott Johnson <sj@retailarchitects.com> wrote:\n>\n>     Doug,\n>     We have an issue with data in the cust_asp table data.  We need someone with extensive GERS knowledge like yourself to help us out if you can spare some time.  If you'd prefer to talk about this, we should schedule some time with Kent.\n>     We have a problem with CUST_CD, AS_CD, ACCT_CD, not being a unique index because we are telling sqlalchemy it is the primary key.\n>     That means that if this query ever returns any results, sqlalchemy will assume, as we told it to, that there is only one record though it isn't true:\n>     We get a lot of returns when we run the following query on our other customers:\n>     select CUST_CD, AS_CD, ACCT_CD, count(*) from CUST_ASP group by CUST_CD, AS_CD, ACCT_CD having count(*) > 1;\n>\n>     1)  Do you agree there shouldn't ever be more than one account number for any finance company and any given customer?  If so, then a potential solution is to make this a unique index, which would require data cleanup.  We suspect gers couldn't since it allows for null accountnumber. \n>\n>     2) What does it mean to have more than one account number for any finance company and any given customer?\n>\n>     3)  Do you know what it means when there is a null accountnumber.  Are these records used for anything?  What?\n>\n>     4) Is it correct to assume there should never be more than one given an actual accountnumber (not null)?  If not, what distinguishes these records?  (EXPIR_DT? APP_CREDIT_TP?, etc?... We don't understand why it allows the same account number)\n\n"}}
{"actor":{"display_login":"kentbower"}}