Hi Franz,

I'm having a bit of trouble trying to work out how to do a particular SQL statement in LLBL.

This is the SQL that i would like to do in LLBL:

SELECT XCoord, YCoord, MAX(ZCoord) as ZCoord,
(SELECT SMS_DestinationUID FROM SMS_Block 
WHERE XCoord = B.XCoord AND YCoord = B.YCoord AND ZCoord = MAX(B.ZCoord)
AND SMS_PadUID = @PadUID
AND BlockInTime <= @StartDate
AND (BlockOutTime IS NULL OR BlockOutTime >= @StartDate)) as SMS_DestinationUID
FROM SMS_Block B
WHERE B.SMS_PadUID = @PadUID
AND B.BlockInTime <= @StartDate
AND (B.BlockOutTime IS NULL OR BlockOutTime >= @StartDate)
AND B.XCoord >= @minX AND B.XCoord <= @maxX
AND B.YCoord >= @minY AND B.YCoord <= @maxY
AND B.ZCoord >= @minZ AND B.ZCoord <= @maxZ
GROUP BY XCoord, YCoord

In another section i'm returning the max of Z grouped by X and Y by using the following code:

            IDataAccessAdapter da = DataManager.CreateAdapter();
            HelperClasses.EntityCollection blockCollection = new EntityCollection();
            blockCollection.EntityFactoryToUse = new SMS_BlockEntityFactory();
            
            //Get the standard block predicate
            IRelationPredicateBucket relationBucket = GetBlockPredicate(dt);
            
            //Allow you to specify a maximum height
            relationBucket.PredicateExpression.Add(PredicateFactory.CompareValue(SMS_BlockFieldIndex.ZCoord, ComparisonOperator.LessEqual, this.GetZBlockFromCoord(zMax)));
            
            //Set up fields to return X,Y and Z max
            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(SMS_BlockFieldIndex.XCoord, 0, "XCoord");
            fields.DefineField(SMS_BlockFieldIndex.YCoord, 1, "YCoord");
            fields.DefineField(SMS_BlockFieldIndex.ZCoord, 2, "ZCoord");
            fields[2].AggregateFunctionToApply = AggregateFunction.Max;
            
            //Group by X,Y
            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            groupByClause.Add(fields[1]);

            da.FetchTypedList(fields, internalSurfaceTable, relationBucket, 0, null, true, groupByClause);

Is there anyway to do this without including the SMS_Destination in the Group By statement?

Table Definition:

SMS_BlockUID uniqueIdentifier primary key

XCoord float

YCoord float

ZCoord float

SMS_DestinationUID uniqueIdentifier

Thanks,

Daniel

Is there anyway to do this without including the SMS_Destination in the Group By statement?

I think Not

But you can work around this by using a View (if you want)

You may create a view for the outer Select as follows

CREATE VIEW  MAXZ AS
SELECT XCoord, YCoord, MAX(ZCoord) as ZCoord
FROM SMS_Block
GROUP BY XCoord, YCoord

Then for the SQL Query solution you may do the following

SELECT SMS_DestinationUID, XCoord, YCoord, ZCoord 
FROM SMS_Block s
INNER JOIN MAXZ m 
ON  s.XCoord = m.XCoord 
AND s.YCoord = m.YCoord 
AND s.ZCoord = m.ZCoord
WHERE SMS_PadUID = @PadUID
AND BlockInTime <= @StartDate
AND (BlockOutTime IS NULL OR BlockOutTime >= @StartDate)
AND B.XCoord >= @minX AND B.XCoord <= @maxX
AND B.YCoord >= @minY AND B.YCoord <= @maxY
AND B.ZCoord >= @minZ AND B.ZCoord <= @maxZ

And for the LLBLGen corresponding solution, you would map an Entity to that view and then you can easily formulate this query

CREATE VIEW MAXZ AS
SELECT XCoord, YCoord, MAX(ZCoord) as ZCoord
FROM SMS_Block
GROUP BY XCoord, YCoord

I'm not sure this is going to work because the max value is based on a particular datetime, so the max value on the 1/1/05 might be 10 and then it might be 15 on the 2/1/05 so if we wanted to get the max Zcoord on the 1/1/ using this view it would return 15 instead of 10.

The other option is to use a stored procedure, but i wanted to avoid this in case we ever switch over to another DB platform as it would be a bit of a pain to maintain two sets of these procedures in different languages.

Aslo, how do you join a view to a regular expression within LLBL?

Ok you are right, I guess my last resort as a workaround will be a stored procedure. (I don't like them either)

Aslo, how do you join a view to a regular expression within LLBL?

I meant, if you were going to use a View, you could map it to an Entity then build a relation between it and the other Table in the Join either in the Designer or dynamically at runtime (in your code).

the query inside the select list: (SELECT SMS_DestinationUID FROM SMS_Block WHERE XCoord = B.XCoord AND YCoord = B.YCoord AND ZCoord = MAX(B.ZCoord) AND SMS_PadUID = @PadUID AND BlockInTime <= @StartDate AND (BlockOutTime IS NULL OR BlockOutTime >= @StartDate)) as SMS_DestinationUID

can be rewritten if you use a join and move the where part to the query's where clause. It requires some work though, but in most times it can be done. If not, it can't be done in llblgen pro: selects inside the select list aren't supported (yet)