Hi,

I need to use a SubQuery alias name used in column list from a group by clause. Please let me know how to proceed. Sample Query is Below

SELECT 
DISTINCT Territory.Territory.id AS TerritoryId, 
Locations.Location.subBrickId AS SubbrickId, 
[b](SELECT Persons.PersonResourceDetail.value AS Value FROM (( Persons.PersonResource  INNER JOIN Persons.Potential LPA_P2  ON  Persons.PersonResource.id=LPA_P2.descriptionId) INNER JOIN Persons.PersonResourceDetail  ON  Persons.PersonResource.id=Persons.PersonResourceDetail.resourceId AND ( ( Persons.PersonResourceDetail.languageId = 1))) WHERE ( LPA_P2.weight = MAX(LPA_P1.weight))) AS POT, [/b]
COUNT(DISTINCT Persons.Person.id) AS NoOfPersonsByPotential 
FROM (((((((( Territory.Territory  INNER JOIN Territory.TerritoryDetail  ON  Territory.Territory.id=Territory.TerritoryDetail.territoryId AND ( ( Territory.TerritoryDetail.startPeriod <= 103 AND ( Territory.TerritoryDetail.endPeriod >= 103 OR Territory.TerritoryDetail.endPeriod IS NULL)))) INNER JOIN Persons.PersonLocation  ON  ( ( Territory.TerritoryDetail.personId = Persons.PersonLocation.personId AND Territory.TerritoryDetail.locationId = Persons.PersonLocation.locationId AND Persons.PersonLocation.status = 1))) INNER JOIN Locations.Location  ON  Locations.Location.id=Persons.PersonLocation.locationId) INNER JOIN Persons.Person  ON  Persons.Person.id=Territory.TerritoryDetail.personId) INNER JOIN Persons.PersonProduct  ON  Persons.Person.id=Persons.PersonProduct.personId) INNER JOIN Territory.ProductPortfolioDetail  ON  Persons.PersonProduct.productId=Territory.ProductPortfolioDetail.productId) INNER JOIN Territory.ProductPortfolio  ON  Territory.ProductPortfolio.id=Territory.Territory.productPortfolioId) INNER JOIN Persons.Potential LPA_P1  ON  LPA_P1.id=Persons.PersonProduct.potentialId) 
WHERE ( ( Territory.Territory.id = 14 AND Locations.Location.subBrickId = 240)) 
GROUP BY 
Territory.Territory.id, 
Locations.Location.subBrickId, [b]POT[/b]

ORDER BY POT ASC

Help is very much appreciated.

Using LLBLGen 2.5 Version.SQLServer 2005

Regards

Prabhu

For the specified query, you should use a DynamicList, and then you can use a ScalarQueryExpression. Hint: you sould pass the alias when you define the field. (please check the code example in the previous link).

Then when Grouping use the same Field defined in the resultSetFields. A code example can be found here showing how to use the fields[i] in the group by: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11627