Unknown column 'cp.payplannum' in 'field list'

This forum is for programmers who have questions about the source code.
Post Reply
ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Unknown column 'cp.payplannum' in 'field list'

Post by ajhalls » Thu May 07, 2020 4:10 pm

I am getting an odd error of "Unknown column 'cp.payplannum' in 'field list'".

I was working with an office using 19.4 and their paysplit table was missing along with the updatehistory table. Looking at their DB backups that go back to 2017, they have been missing for some time. How are tables and columns staying missing after version upgrades? I am shocked that Open Dental is actually working considering what is missing.

I have about 2 offices getting the same error, but the other is on version 13, so I expect that.
Here is my query that I am running:

Code: Select all

SELECT "proc"                                     TranType, 
       pl.patnum, 
       pl.procdate                                TranDate, 
       pl.procfee * ( pl.unitqty + pl.baseunits ) TranAmount, 
       0                                          PayPlanAmount, 
       0                                          InsWoEst, 
       0                                          InsPayEst, 
       pl.procfee 
FROM   procedurelog pl 
WHERE  pl.procstatus = 2 
       AND pl.procfee != 0 
       AND pl.datetstamp >= Date_format("2020-05-07 10:53:56", "%y-%m-%d") 
UNION ALL 
SELECT "claimproc" TranType, 
       cp.patnum, 
       cp.datecp   TranDate, 
       ( CASE 
           WHEN cp.status != 0 THEN( CASE 
                                       WHEN cp.payplannum = 0 THEN -cp.inspayamt 
                                       ELSE 0 
                                     END ) - cp.writeoff 
           ELSE 0 
         END )     TranAmount, 
       ( CASE 
           WHEN cp.payplannum != 0 
                AND cp.status IN( 1, 4, 5 ) THEN -cp.inspayamt 
           ELSE 0 
         END )     PayPlanAmount, 
       ( CASE 
           WHEN cp.status = 0 THEN cp.writeoff 
           ELSE 0 
         END )     InsWoEst, 
       ( CASE 
           WHEN cp.status = 0 THEN cp.inspayest 
           ELSE 0 
         END )     InsPayEst, 
       0           procfee 
FROM   claimproc cp 
WHERE  cp.status IN( 0, 1, 4, 5, 7 ) 
       AND cp.secdatetedit >= Date_format("2020-05-07 10:53:56", "%y-%m-%d") 
HAVING tranamount != 0 
        OR payplanamount != 0 
        OR inswoest != 0 
        OR inspayest != 0 
UNION ALL 
SELECT "adj"     TranType, 
       a.patnum, 
       a.adjdate TranDate, 
       a.adjamt  TranAmount, 
       0         PayPlanAmount, 
       0         InsWoEst, 
       0         InsPayEst, 
       0         procfee 
FROM   adjustment a 
WHERE  a.adjamt != 0 
       AND a.secdatetedit >= Date_format("2020-05-07 10:53:56", "%y-%m-%d") 
UNION ALL 
SELECT "patpay"   TranType, 
       ps.patnum, 
       ps.datepay TranDate, 
       ( CASE 
           WHEN ps.payplannum = 0 THEN -ps.splitamt 
           ELSE 0 
         END )    TranAmount, 
       ( CASE 
           WHEN ps.payplannum != 0 THEN -ps.splitamt 
           ELSE 0 
         END )    PayPlanAmount, 
       0          InsWoEst, 
       0          InsPayEst, 
       0          procfee 
FROM   paysplit ps 
WHERE  ps.splitamt != 0 
       AND ps.secdatetedit >= Date_format("2020-05-07 10:53:56", "%y-%m-%d") 
UNION ALL 
SELECT "ppcomplete"     TranType, 
       pp.patnum, 
       pp.payplandate   TranDate, 
       -pp.completedamt TranAmount, 
       0                PayPlanAmount, 
       0                InsWoEst, 
       0                InsPayEst, 
       0                procfee 
FROM   payplan pp 
WHERE  pp.completedamt != 0 
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save

joes
Posts: 239
Joined: Tue Aug 13, 2019 12:41 pm

Re: Unknown column 'cp.payplannum' in 'field list'

Post by joes » Fri May 08, 2020 1:24 pm

Hello, Dr. Halls. The PayPlanNum column was added to the claimproc table in version 14.3.1, so I would expect that error when your query is run against the database of the office that is using version 13, but if you also get this error for a database beyond version 14.3.1 I would suspect that the database is corrupted.

As for the missing paysplit and updatehistory tables, I launched Open Dental, version 19.4, after deleting these tables from a test database. I was able to navigate between modules without error, but the program threw an unhandled exception once I selected a patient and navigated to the account module. With a patient selected, is that office able to navigate to the Account Module without causing an error?
Joe Sullivan
Open Dental Software
http://www.opendental.com

ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Re: Unknown column 'cp.payplannum' in 'field list'

Post by ajhalls » Mon May 11, 2020 2:11 pm

I would assume so, but it isn't my practice. I only know that when I connected remotely that the current installed version was 19, and that those tables were missing. I asked them to contact OD Support to fix it. They were out of contract with their support, but I encouraged them to fix it.
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save

Post Reply