HI, I have a query and subquery which I use to check the uniqueness of a code. Here's the SQL.

select count(*) 
from
( select tour_op_id, count(*) as CodeCount
from Tour_operator
where tour_op_code1 = 'ALT01'
or tour_op_code2 = 'ALT01'
or tour_op_code3 = 'ALT01'
or tour_op_code4 = 'ALT01'
group by tour_op_id ) as c
where c.tour_op_id <> 16

I have tried to break this down into PredicateExpressions and apply them to a GetScalar call, however I'm having trouble assembling both queries into one expression. I've come up with

           IPredicateExpression theSubquery = new PredicateExpression(new PredicateExpression(TourOperatorFields.TourOpCode1 == TourOpCode));
            theSubquery.Add(new PredicateExpression(TourOperatorFields.TourOpCode2 == TourOpCode));
            theSubquery.AddWithOr(new PredicateExpression(TourOperatorFields.TourOpCode3 == TourOpCode));
            theSubquery.AddWithOr(new PredicateExpression(TourOperatorFields.TourOpCode4 == TourOpCode));

for the inner query, but can't think how to formulate the outer one. Any help would be appreciated.

Michael Mason

This is called a 'derived table' and support for that comes in v2.6 (currently released in beta). (a select as an operand in a JOIN statement). In v2.5, you either have to join the inner tables of the select with the main select and move the predicates to the main query (which is sometimes slower), or create a view from your subquery.

Hi, Thanks for the information. I have discovered the ScalarQueryExpression and tried something like this

            ResultsetFields fields = new ResultsetFields(2);
            IPredicateExpression theSubquery = new PredicateExpression(new PredicateExpression(TourOperatorFields.TourOpCode1 == TourOpCode));
            theSubquery.Add(new PredicateExpression(TourOperatorFields.TourOpCode2 == TourOpCode));
            theSubquery.AddWithOr(new PredicateExpression(TourOperatorFields.TourOpCode3 == TourOpCode));
            theSubquery.AddWithOr(new PredicateExpression(TourOperatorFields.TourOpCode4 == TourOpCode));

            IGroupByCollection groupbyClause = new GroupByCollection();
            fields.DefineField(TourOperatorFields.TourOpId, 0);
            groupbyClause.Add(fields[0]);
            fields.DefineField(new EntityField2("NumberOfCodes", new ScalarQueryExpression(TourOperatorFields.TourOpId.SetAggregateFunction(AggregateFunction.Count),
                                      theSubquery,null,null,groupbyClause)), 1);
            using (IDataAccessAdapter da = new DataAccessAdapter())
            {
                result = ((int)da.GetScalar(fields,(TourOperatorFields.TourOpId == TourOpId),groupbyClause) > 0 );
            }
            return result;

Only I received a runtime error as follows

The length of the list of fields for the select list (2) isn't the same as the length of the list of persistence info objects passed in (1). This happens when using adapter the DBSpecific project is out of sync with the DBGeneric project.

I am open to alternative methods to find duplicate codes.

select count() from ( select tour_op_id, count() as CodeCount from Tour_operator where tour_op_code1 = 'ALT01' or tour_op_code2 = 'ALT01' or tour_op_code3 = 'ALT01' or tour_op_code4 = 'ALT01' group by tour_op_id ) as c where c.tour_op_id <> 16

The above query doesn't make sense to me, you either need to use the inner query with the count, or the outer count of ids but then the inner count would have no use.

So either:

select tour_op_id, count(*) as CodeCount
from Tour_operator
where 
(
tour_op_code1 = 'ALT01'
or tour_op_code2 = 'ALT01'
or tour_op_code3 = 'ALT01'
or tour_op_code4 = 'ALT01'
)
AND tour_op_id <> 16
group by tour_op_id 

or

select count(*) 
from
( select distinct tour_op_id
from Tour_operator
where tour_op_code1 = 'ALT01'
or tour_op_code2 = 'ALT01'
or tour_op_code3 = 'ALT01'
or tour_op_code4 = 'ALT01'
where tour_op_id <> 16)

So which of the above are you after?

Hi, Sorry for the confusion. I played arround a bit with the code and ended up with the following adaptation from the code on page 447/448 of the manual.

        private bool isDuplicate(int TourOpId, string TourOpCode)
        {
            bool result = false;
            ResultsetFields fields = new ResultsetFields(2);
            IRelationPredicateBucket theRPB = new RelationPredicateBucket(new FieldCompareValuePredicate(TourOperatorFields.TourOpId,null,ComparisonOperator.NotEqual,TourOpId));
            IPredicateExpression theCodePredicate = new PredicateExpression(new PredicateExpression(TourOperatorFields.TourOpCode1 == TourOpCode));
            theCodePredicate.AddWithOr(new PredicateExpression(TourOperatorFields.TourOpCode2 == TourOpCode));
            theCodePredicate.AddWithOr(new PredicateExpression(TourOperatorFields.TourOpCode3 == TourOpCode));
            theCodePredicate.AddWithOr(new PredicateExpression(TourOperatorFields.TourOpCode4 == TourOpCode));

            fields.DefineField(TourOperatorFields.TourOpId, 0);
            groupbyClause.Add(fields[0]);
            fields.DefineField(new EntityField2("NumberOfCodes", new ScalarQueryExpression(TourOperatorFields.TourOpId.SetAggregateFunction(AggregateFunction.Count),
                                      theCodePredicate, null,null)), 1);
            DataTable resultsTable = new DataTable();
            using (IDataAccessAdapter da = new DataAccessAdapter())
            {
                da.FetchTypedList(fields, resultsTable, theRPB);
                result = (resultsTable.Rows.Count > 0);
            }
            return result;
        }

I didn't get the results I wanted and gave up using LLBLGen due to time constraints. I ended up using plain SQL to get the result I wanted. Here's the code.

           SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["CaptureV4.ConnectionString"].ConnectionString);

            SqlCommand cmd = new SqlCommand("SELECT count(*) FROM (select tour_op_id FROM Tour_operator where tour_op_code1 = @TCODE or tour_op_code2 = @TCODE or tour_op_code3 = @TCODE or tour_op_code4 = @TCODE) as c where c.tour_op_id <> @TID", cn);
            cmd.Parameters.AddWithValue("@TCODE", TourOpCode);
            cmd.Parameters.AddWithValue("@TID", TourOpId);
            cn.Open();
            Int32 count = (Int32)cmd.ExecuteScalar();
            return (count > 0);

The purpose of this code was to find the number of IDs that had the same code as the selected ID. I would like to understand how to get this query into LLBLGen objects but at the moment I don't have a great deal of time to experiment on this.

Regards

Michael Mason

SELECT count(*) FROM ( SELECT tour_op_id FROM Tour_operator WHERE tour_op_code1 = @TCODE or tour_op_code2 = @TCODE or tour_op_code3 = @TCODE or tour_op_code4 = @TCODE ) as c WHERE c.tour_op_id <> @TID

I think the above query can be re-written as:

    SELECT Count(*) 
    FROM 
    Tour_operator 
    WHERE 
    (
    tour_op_code1 = @TCODE 
    or tour_op_code2 = @TCODE 
    or tour_op_code3 = @TCODE 
    or tour_op_code4 = @TCODE
    )
    AND c.tour_op_id <> @TID

Which can simply be formulated using GetDbCount() method.