Hi, I am trying to perform a "select distinct" subquery like the following in llblgen 3.0 using the Adapter configuration:

select count(*) from referral
where datecreated < '2010-07-14' and incoming = 1
and
(
    referral_ID NOT in (select distinct referral_ID from booking where referral_ID is not null and deleted = 0)
    AND referral_ID NOT in (select distinct referralno from bill_invoice where referralno > 0)
)

I am using the following method using FieldCompareSetPredicate to create the subqueries

        private FieldCompareSetPredicate NotAttachedToInvoicePredicate()
        {
            var peHasReferral = new PredicateExpression();
            peHasReferral.Add(Bill_InvoiceFields.Referralno > 0); // has referral

            return new FieldCompareSetPredicate(ReferralFields.Referral_ID,
                                                                                    null,
                                                                                    Bill_InvoiceFields.Referralno,
                                                                                    null,
                                                                                    SetOperator.In,
                                                                                    peHasReferral, true);
        }

which generates the following sql query:

AND NOT [dbo].[Referral].[referral_ID] IN (SELECT [dbo].[Bill_Invoice].[referralno] AS [Referralno] FROM [dbo].[Bill_Invoice]   WHERE ( [dbo].[Bill_Invoice].[referralno] > 0)

Which is exactly what I want, but without the distinct constraint.

How can I add the distinct constraint to this query?

Cheers,

Actually you don't need it, and on the contrary it might hurt performance.

ref: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3153

I see, thanks for your help!