Hi everybody,

I'd like to use one select statement for the following problem, but don't see a way so far.

I have referrals (accounts) in Trans file with cTrans_pk as a primary key. These referrals are for patients, which have information in Patients table with cPatients_pk as PK. The name of the patient is stored in the Names table. One patient can have a primary name and some aliases (AKAs). If the patient has aliases, the iAlias_flag is set to 1, and cAlias_name_fk field holds the reference of the primary name (e.g. if the primary name is James Smith with the id of 1 and Jim Smith is an alias with the id 2, we would have:

ID      Name          Alias_fk    iAlias_flag
1       James Smith               1
2       Jim Smith     1           ?  (not sure, I think, it's 0)

Now I don't want to show the same account multiple times. E.g. I always want to show the primary name for each account. However, I should allow to search by AKAs as well and therefore sometimes I would have AKAs records returned only.

====================================
Bellow is my present code. DISTINCT keyword is enough for COUNT (first run), but it's not sufficient for actual data retrival. What do you think? Is my goal achievable?

*---------------------- Location Section ------------------------
*   Library: 	Asearchbiz.vcx
*   Class: 		Searchobject
*   Method: 	RunSearch()
*----------------------- Usage Section --------------------------
*)  Description:
*)

*   Scope:      Public
*   Parameters:
*$  Usage:
*$
*   Returns:
*--------------------- Maintenance Section ----------------------
*   Change Log:
*       CREATED 	05/18/2005 - NN
*		MODIFIED
*----------------------------------------------------------------
local loSelect, loDE, loCursor, lcSelectCMDFilter, lcCountSelectCMD, ;
	lcBaseSelectCMD, lcCountSelectCMD, loCursorAdapter, lnReccount

loDE = this.GetDataEnvironment()
loCursor = m.loDE.GetCursorObject("ca_QuickSearch")
loCursorAdapter = m.loCursor.oCursorAdapter
lnReccount = 0
this.nRecords = 0

text TO lcCountSelectCMD NOSHOW PRETEXT 2
	SELECT COUNT (distinct cTrans_pk) as cntRecs ;
		FROM trans INNER JOIN Patients ;
		ON Patients.cPatients_pk = Trans.cPatients_fk INNER JOIN names ;
		ON Patients.cPatients_pk = Names.cPointer_fk
ENDTEXT

lcCountSelectCMD = m.lcCountSelectCMD + iif(empty(this.cJoinExpr),"", " " + this.cJoinExpr)
lcCountSelectCMD = strtran(strtran(m.lcCountSelectCMD,";",[]), chr(13) + chr(10), [])

text TO lcBaseSelectCMD NOSHOW PRETEXT 2
	SELECT DISTINCT Trans.cTrans_pk AS ctrans_pk, ;
		Trans.cClient_account_number, ;
		Patients.dDob, ;
		Trans.tAdmit_date AS tDOS, ;
		PADR(NVL(ALLTRIM(Names.cl_name)+", ", "") + ;
		NVL(ALLTRIM(Names.cf_name)+" ", "") + ;
		NVL(Names.cm_initial, ""), 50) AS cpatient_name, ;
		Product_lines.cProduct_line, ;
		Trans.cCommissioned_Owner as cUserID,  ;
        IIF(ISNULL(Trans.cResolution_Codes_fk) OR cResolution_Codes_Fk=="", ;
        VisC.cCode_Description, VisCo.cCode_Description) as cStatus ;
		FROM trans INNER JOIN Patients ;
		ON Patients.cpatients_pk = Trans.cpatients_fk INNER JOIN names ;
		ON Patients.cpatients_pk = Names.cpointer_fk ;
		INNER JOIN product_lines ;
		ON Product_lines.cproduct_lines_pk = Trans.cproduct_lines_fk ;
		LEFT JOIN VisCodes VisC ON Trans.cStatus_codes_fk = VisC.cVisCodes_pk ;
		LEFT JOIN VisCodes VisCo ON Trans.cResolution_codes_fk = VisCo.cVisCodes_pk
ENDTEXT

lcBaseSelectCMD = m.lcBaseSelectCMD + iif(empty(this.cJoinExpr),"", " " + this.cJoinExpr)
lcBaseSelectCMD = strtran(strtran(m.lcBaseSelectCMD,";",[]), chr(13) + chr(10), [])

lcSelectCMDFilter = this.cWhereExpr

with this
	.lRepeatRequery = .f.

** These commands should change CursorAdapter's SelectCMD
	loCursorAdapter.cBaseSelectCmd = m.lcCountSelectCMD
	loCursorAdapter.cSelectCmdFilter = m.lcSelectCMDFilter

	_cliptext = loCursorAdapter.selectcmd
	local lnRequeryResult
	lnRequeryResult = .requery(.t., "ca_QuickSearch")

	do while m.loCursorAdapter.lRepeatRequery = .t. and m.lnRequeryResult <> REQUERY_SUCCESS && the user tried to stop it
		lnRequeryResult = .requery(.t., "ca_QuickSearch")
		loCursorAdapter.lRepeatRequery = .f.
	enddo

	if vartype(m.plStop) = "L" and m.plStop = .t.
		lnReccount = 0
	else
		lnReccount = ca_QuickSearch.cntRecs
		.nRecords = m.lnReccount
		if .nRecords = 0
			.lNoRecordsSelected = .t.
		endif
	endif
	loCursorAdapter.cBaseSelectCmd = m.lcBaseSelectCMD
	if  .nRecords > 0 and .nRecords <= .nMaxRecordsToDisplay
		lnReccount = .CreateFinalResult()
	endif
endwith

return m.lnReccount