have this SQL statement that I am trying to build a llbl statement with. I am complete stuck on how to get this to work. Please help.
SELECT C.LVL1_CATEGORY_LVL_ID, COALESCE (NULLIF (CDL.WEB_CATEGORY_LANGUAGE_DESC, N''), CD.CATEGORY_DESC)
AS WEB_PUB_SECTION_DESC
FROM dbo.DI_CATEGORIES AS C INNER JOIN
dbo.DI_CATEGORYDESCS_LANGS AS CDL ON C.PUB_ID = CDL.PUB_ID AND C.LVL1_CATEGORY_LVL_ID = CDL.CATEGORY_LVL_ID INNER JOIN
dbo.DI_CATEGORYDESCS AS CD ON C.PUB_ID = CD.PUB_ID AND C.LVL1_CATEGORY_LVL_ID = CD.CATEGORY_LVL_ID
WHERE (C.PUB_ID = 'KAMP') AND (CDL.LANGUAGE_ID = 'ENG') AND (C.PUB_SECTION_ID = 'PL')
GROUP BY C.LVL1_CATEGORY_LVL_ID, CDL.WEB_CATEGORY_LANGUAGE_DESC, CD.CATEGORY_DESC
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CategoryDescriptionLangFields.LevelId, 0, "LevelId");
fields.DefineField(CategoryDescriptionLangFields.WebLanguageDescription, 1, "WebLanguageDescription");
IRelationPredicateBucket bucket = new RelationPredicateBucket();
// Where clauses
IPredicateExpression pubExp = new PredicateExpression(PredicateFactory.CompareValue(CategoryFieldIndex.PublicationId, ComparisonOperator.Equal, options.PublicationId));
IPredicateExpression sectionIdExp = new PredicateExpression(PredicateFactory.CompareValue(CategoryFieldIndex.SectionId, ComparisonOperator.Equal, options.SectionId));
IPredicateExpression level1exp = new PredicateExpression(PredicateFactory.CompareValue(CategoryFieldIndex.Level1Id, ComparisonOperator.Equal, options.Level1Id));
IPredicateExpression languageExp = new PredicateExpression(PredicateFactory.CompareValue(CategoryDescriptionLangFieldIndex.LanguageId, ComparisonOperator.Equal, options.LanguageId));
// Custom Relation
EntityRelation customRelation = CategoryDescriptionLangEntity.Relations.CategoryDescriptionEntityUsingPublicationIdLevelId;
customRelation.CustomFilter = new PredicateExpression(
CategoryFields.PublicationId == CategoryDescriptionLangFields.PublicationId);
// add the new relation to your bucket with a INNER hint
bucket.Relations.Add(customRelation, JoinHint.Inner);
Thanks
