mySQL query problem

This forum is for programmers who have questions about the source code.

mySQL query problem

Postby serenityfamilydental » Tue Nov 13, 2018 2: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*/
serenityfamilydental
 
Posts: 11
Joined: Mon Apr 13, 2015 6:27 pm

Re: mySQL query problem

Postby adearmondsattler » Tue Nov 13, 2018 2: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/12/mysql-change-the-limit-of-row-retrieved-in-mysql-workbench-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/OpenDentalDocumentation18-3.xml#appointment
“If debugging is the process of removing bugs, then programming must be the process of putting them in.”

Allen DeArmond-Sattler
Open Dental Software
http://www.opendental.com
User avatar
adearmondsattler
 
Posts: 163
Joined: Fri Aug 23, 2013 11:29 am

Re: mySQL query problem

Postby serenityfamilydental » Tue Nov 13, 2018 3:27 pm

Thanks a lot for your reply. It turns out that I point to the wrong database. I am all set.
serenityfamilydental
 
Posts: 11
Joined: Mon Apr 13, 2015 6:27 pm


Return to Developers

Who is online

Users browsing this forum: No registered users and 7 guests

cron