Hi everybody,

I create SQL dynamically as a text and then execute it. I want to select records satisfying user's profile. The User's profile may have a filter based on the Insurance Carrier. Now, an account can have multiple insurances (Trans_Insurances records). The Trans_Insurances table has iSequence_Number field. In my select statement I want to select the account with the insurance with the greatest number in sequence (the last number in sequence).

Now, here is my additional filter logic

if not empty(m.tcAdditionalFilter) and 'carriers' $ lower(m.tcAdditionalFilter)
  text to lcAdditionalJoins noshow pretext 7
	 INNER JOIN Trans_Insurances on ;
	 Trans.cTrans_pk = Trans_Insurances.cTrans_fk ;
	 INNER JOIN Insurances on ;
	 Trans_Insurances.cInsurances_fk = Insurances.cInsurances_pk ;
	 INNER JOIN Carrier_Branches on ;
	 Insurances.cCarrier_Branches_fk = ;
          Carrier_Branches.cCarrier_Branches_pk ;
 	 INNER JOIN Carriers on ;
	 Carrier_Branches.cCarriers_fk = Carriers.cCarriers_pk
   ENDTEXT
endif

And that's the select itself (quite complex) and I don't see a way of specifying that I need the insurance with the maximum number.

select top <<m.tnNumAccounts>> trans.cTrans_pk, ;
	space(16) as cCommission_Owner_UsGrLink_fk, ;
	space(16) as cQueue_names_fk, ;
	0 as iAssigned, ;
	step1.tScheduled_Time, ;
	step1.cTrans_Employees_Queues_Pk, ;
	step1.tExpire_date ;
	from trans INNER join ;
	(select ;
	NVL(EQS.tScheduled_Time, <<m.lcFutureDate>>) ;
	as tScheduled_Time, TEQ.tExpire_date, ;
	TEQ.cTrans_Employees_Queues_Pk,  ;
	TEQ.cTrans_fk from Trans_Employees_Queues TEQ ;
	LEFT JOIN Employee_Queue_Schedules EQS ON ;
	TEQ.cTrans_Employees_Queues_pk = ;
	EQS.cTrans_Employees_Queues_fk ;
	and EQS.iActive_Flag = 1 and ;
	EQS.tScheduled_Time < <<m.lcAssignDate>> ;
	where TEQ.cCommission_Owner_UsGrLink_fk IS NULL ;
	and TEQ.cQueue_names_fk = '<<m.tcQueuePk>>' ;
	and TEQ.iActive_Flag = 1) step1 ;
	on Trans.cTrans_pk = step1.cTrans_fk ;
	<<m.lcAdditionalJoins>> ;
	where <<m.lcFilter>>

Can you please give me a hand?

Thanks in advance.