Open Dental Forum

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.

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

Thanks