mySQL query problem

This forum is for programmers who have questions about the source code.
Post Reply
serenityfamilydental
Posts: 14
Joined: Mon Apr 13, 2015 6:27 pm

mySQL query problem

Post by serenityfamilydental » Tue Nov 13, 2018 1:14 pm

Hi, I run a customized query in mySQL workbench to get all appointment for certain day, however I can NOT get all appointments. If I run the same query inside Opendental Custom query, I can get all appointments. I list my query below. Another question, if I run this query in mySQL workbench, appointment.Confirmed and appointment.AptStatus are integers. How do I map them to text so that it make sense, e.g. it should say confirmed, scheduled as if I run insider Opendental Custom query. Any help is appreciated.

____________________________________________________________________________
use opendental;

SET @AptDateTime = '2018-11-15';

SELECT patient.LName,patient.FName, patient.HmPhone, patient.WkPhone, patient.WirelessPhone,
appointment.AptDateTime,appointment.IsHygiene,appointment.IsNewPatient, appointment.Confirmed, appointment.AptStatus,appointment.UnschedStatus
FROM appointment
LEFT JOIN patient ON appointment.PatNum=patient.PatNum
WHERE appointment.AptDateTime LIKE CONCAT(@AptDateTime,'%')
AND AptStatus != 6 /*Planned*/
AND AptStatus != 3 /*UnschedList*/

allends
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: mySQL query problem

Post by allends » Tue Nov 13, 2018 1:40 pm

I run a customized query in mySQL workbench to get all appointment for certain day, however I can NOT get all appointments.
Most likely MySQL Workbench has some kind of row limit on its return set.
https://blog.sqlauthority.com/2014/01/1 ... rkbench-2/
How do I map them to text so that it make sense, e.g. it should say confirmed, scheduled as if I run insider Opendental Custom query. Any help is appreciated.
Mapping them to text outside of OpenDental is mostly a manual process. Looking through our database schema documentation will help you know how to get the correct text. Definitions can be linked to the correct Definition in the Definition table and then the ItemName can be queried. For enumerations you will just need to substitute the text for the values that you receive back (Example: If AptStatus=1, then the text would be "Scheduled")
https://www.opendental.com/OpenDentalDo ... ppointment
Allen
Open Dental Software
http://www.opendental.com

serenityfamilydental
Posts: 14
Joined: Mon Apr 13, 2015 6:27 pm

Re: mySQL query problem

Post by serenityfamilydental » Tue Nov 13, 2018 2:27 pm

Thanks a lot for your reply. It turns out that I point to the wrong database. I am all set.

Post Reply