Query Question

For users or potential users.
Post Reply
User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Query Question

Post by Hersheydmd » Sat Sep 05, 2020 5:09 pm

In creating queries, I notice that if I want a column with the patient name and I type
Select patnum
the result produces a column titled PatNum with the actual patient names (last name, first name).
But if I want the column to be titled "Patient" instead of "PatNum" and I type
Select patnum as Patient
The result produces a column titled "Patient", but lists the patient ID#s and not names.

Example
SELECT s.PatNum as Patient, sf.FieldValue
FROM Sheet s, SheetField sf
WHERE s.SheetNum=sf.SheetNum
And sf.ReportableName='XYZ'
And SheetDefNum = 100

The query works, but I get Patient ID#'s instead of names.
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

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

Re: Query Question

Post by joes » Wed Sep 09, 2020 9:40 am

The User Query window only replaces the PatNum with the patient's name if an alias for the column is not being used. This replacement can be controlled using the "Format" radio buttons. If you want to use a different name for the column, you will have to include the patient table in the query and concatenate the last name and first name.

SELECT CONCAT(p.LName,', ',p.FName) AS Patient, sf.FieldValue
FROM sheet s
INNER JOIN sheetfield sf ON s.SheetNum=sf.SheetNum
INNER JOIN patient p ON s.PatNum=p.PatNum
WHERE sf.ReportableName='XYZ'
AND s.SheetDefNum=100
Joe Sullivan
Open Dental Software
http://www.opendental.com

User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Re: Query Question

Post by Hersheydmd » Thu Sep 10, 2020 8:50 pm

Thanks
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

Post Reply