Patients under an Employer

[jonesdds12]

Trying to run a report showing all patients and their info under a certain employer. Using Query Example 131 which has: /*131 Patients with insurance of specific employers, uses insurance employer(s) you should repeat the employer name if you only want one, many times employers are listed more than one way, like HP and Hewlett Packard*/
SELECT EmpName AS 'Employer', PatNum, p.address, p.address2,p.city,p.state,p.zip
FROM patient p

For some reason OD is not recognizing EmpName. The error message is "Invalid query: Unkwon column 'EmpName' in 'field list'

Any reason this is happening. I am designating an employer in that area that is exact with what's in OD.

[jsalmon]

That query is outdate. The employer feature got updated and is part of a different table and is now referenced as EmployerNum, hence the "unknown column" error. I'll have it updated soon.

[jsalmon]

Actually, after looking closer at the query, it is updated correctly. What version of Open Dental are you using? I recommend updating to our most recent stable version.

Or are you just running part of the query (the section you pasted above)? Because to have the query be successful, you need to run the entire thing.

Code: Select all

SELECT EmpName AS 'Employer', PatNum, p.address, p.address2,p.city,p.state,p.zip
FROM patient p
INNER JOIN inssub iss ON iss.Subscriber=p.PatNum
INNER JOIN insplan i ON i.PlanNum=iss.PlanNum
INNER JOIN employer e ON i.EmployerNum=e.EmployerNum
WHERE p.PatStatus=0 AND
((empname LIKE ('%IBM%')) OR (empname LIKE ('%Frijole Taco Stand%')))
ORDER BY EmpName, p.lname;

[jonesdds12]

Nevermind, Stupid mistake!