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