I'm thinking I can inner join to (select max(iSequence_Number)...) in my additional join statement. Do you know if we have a limitation for number of projections? BTW, I can not figure out how to write this inner select either.

The SQL becomes very complex...

>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.