I 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

What is the part that doesn't work? As I can see you are pretty close. At the _COALESCE _part you should use a **DBFunctionCall **and then set the expression of the second field to that DBFunctionCall. Read LLBLGenPro Help - Using the generated code - Calling a database function. The _NULLIF _parameter part becomes another field with another DBFutionCll applied. Let me know if you need help to build that field

Hi daelmo,

I have been working on this for over a day now. I really need help with this.

I am having problem with the retaionships. And not understanding how to use "DBFunctionCall".

Also for the CustomFiler I need to also add a customFilter

customRelation.CustomFilter = new PredicateExpression( CategoryFields.PublicationId == CategoryDescriptionLangFields.PublicationId)

customRelation.CustomFilter = new PredicateExpression( CategoryFields.Level1Id == CategoryDescriptionLangFields.LevelId)

I also think I need to add another "join" from my SQL statement.

I now this is not correct. HELP HELP

Thanks

Hi snowball,

The DBFunctionCall is for the COALESCE and NULLIF part (I don't see that code). For the relations you just have to add the other one just as you did with the first one.

The DBFunctionCall should look like:

EntityField2 nullIFFunction = <TheInvolvedEntity>Fields.TheInvolvedField.SetExpression(new DbFunctionCall( "NULLIF", new object[] { <TheInvolvedEntity>Fields.<TheInvolvedField>, "N" } ) );
fields[1].ExpressionToApply = new DBFunctionCall("COALESCE", new object[] { nullIFFunction , <TheOtherInvolvedEntity>Fields.<TheInvolvedField>});

I don't got why are you using group by (you are selecting only two fields and grouping by three fields).

Another thing I don't understand is that in your SQL you are filtering three fields, but in your C# code you are filtering four fields. And you didn't add the predicates to the bucket. The filters (as your SQL shows) should look like:

bucket.PredicateExpression.Add(CategoryFields.PublicationId == "KAMP");
bucket.PredicateExpression.Add(CategoryDescriptionLangFields.LanguageId == "ENG");
bucket.PredicateExpression.Add(CategoryFields.SectionId == "PL");

So, is that the real SQL you are trying to code?

Hi Daelmo,

I have built a few LLBL statements.  But I never tried building a SQL statement like this in LLBL with joints, groupby and DbFunctionCall. I really need help with putting this together.  Right now I am just guesting and completely confuse.

Thanks

Ok. What I need to know is whether or not the first SQL posted is the one you are trying to code. Then please read my previous post, I think almost everything is explained briefly there. So give a try, read some docs and post again your code if you still have troubles.

Cheers.

Hi Daelmo,

I did my reading up on this.  I guest because I have looked at this so long that I am getting even more confuse.  Yes, the SQL statement that you are seeing is what I am trying to get working.

Thanks

As I said before, you're pretty close

For this SQL query:

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

An approximate code would be:

// the two fields to retrieve
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CategoryDescriptionLangFields.LevelId, 0, "LevelId");
fields.DefineField(CategoryDescriptionLangFields.WebLanguageDescription, 1, "WebLanguageDescription");

// set the funcion field (the second)
EntityField2 nullIFFunction = <TheInvolvedEntity>Fields.TheInvolvedField.SetExpression(new DbFunctionCall( "NULLIF", new object[] { <TheInvolvedEntity>Fields.<TheInvolvedField>, "N" } ) );
fields[1].ExpressionToApply = new DBFunctionCall("COALESCE", new object[] { nullIFFunction , <TheOtherInvolvedEntity>Fields.<TheInvolvedField>});

// build the filter
IRelationPredicateBucket bucket = new RelationPredicateBucket();

bucket.PredicateExpression.Add(CategoryFields.PublicationId == "KAMP");
bucket.PredicateExpression.Add(CategoryDescriptionLangFields.LanguageId == "ENG");
bucket.PredicateExpression.Add(CategoryFields.SectionId == "PL");

// set the relations and their custom filters
EntityRelation categoryDescsLangsRelation = CategoryEntity.Relations.CategoryDescriptionLangEntityUsingPublicationIdLevelId;
categoryDescsLangsRelation.CustomFilter = new PredicateExpression( CategoryFields.PublicationId == CategoryDescriptionLangFields.PublicationId);
bucket.Relations.Add(categoryDescsLangsRelation, JoinHint.Inner);

EntityRelation categoryDescsRelation = CategoryEntity.Relations.CategoryDescriptionEntityUsingPublicationIdLevelId;
categoryDescsRelation.CustomFilter = new PredicateExpression( CategoryFields.PublicationId == CategoryDescriptionFields.PublicationId);
bucket.Relations.Add(categoryDescsRelation, JoinHint.Inner);

// group by clause
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
groupByClause.Add(CategoryDescriptionFields.CategoryDescription);

// retrieve the dynamic list (remember to pass the bucket and the groupBy objects

Thanks Daelmo,

This has made my day. This also gives me better knowledge on how to build a (SQL) LLBL statement.

Thanks again.

Good to know you made it... enjoy

I have been able to get some part the statement working. I am getting the following error message

Error Message :

The multi-part identifier "dbo.DI_CATEGORYDESCS_LANGS.PUB_ID" could not be bound. The multi-part identifier "dbo.DI_CATEGORYDESCS_LANGS.WEB_CATEGORY_LANGUAGE_DESC" could not be bound. The multi-part identifier "dbo.DI_CATEGORYDESCS_LANGS.WEB_CATEGORY_LANGUAGE_DESC" could not be bound.

Here my statement

ResultsetFields fields = new ResultsetFields(3); fields.DefineField(CategoryDescriptionFieldIndex.LevelId, 0, "LevelId"); fields.DefineField(CategoryDescriptionLangFieldIndex.WebLanguageDescription, 1, "WebDescription"); fields.DefineField(CategoryFieldIndex.CategoryId, 2, "CategoryId");

// set the funcion field (the second) EntityField2 nullIFFunction = CategoryDescriptionLangFields.WebLanguageDescription.SetExpression(new DbFunctionCall("NULLIF", new object[] { CategoryDescriptionLangFields.WebLanguageDescription, "N" })); fields[1].ExpressionToApply = new DBFunctionCall("COALESCE", new object[] { nullIFFunction, CategoryDescriptionFields.WebDescription });

// Create filter filter IRelationPredicateBucket bucket = new RelationPredicateBucket();

// set the relations and their custom filters IEntityRelation categoryDescsLangsRelation = CategoryEntity.Relations.CategoryDescriptionEntityUsingPublicationIdLevel1Id; categoryDescsLangsRelation.CustomFilter = new PredicateExpression(CategoryFields.PublicationId == CategoryDescriptionLangFields.PublicationId); bucket.Relations.Add(categoryDescsLangsRelation, JoinHint.Inner);

IEntityRelation categoryDescsRelation = CategoryEntity.Relations.CategoryDescriptionEntityUsingPublicationIdLevel1Id; categoryDescsRelation.CustomFilter = new PredicateExpression(CategoryFields.PublicationId == CategoryDescriptionFields.PublicationId); bucket.Relations.Add(categoryDescsRelation, JoinHint.Inner);

// group by clause IGroupByCollection groupByClause = new GroupByCollection(); groupByClause.Add(fields[0]); groupByClause.Add(fields[1]); groupByClause.Add(CategoryDescriptionFields.WebDescription); groupByClause.Add(fields[2]);

DataTable categoryTable = new DataTable("Categories"); IMsglDataAccessAdapter da = null; da = GetDataAccessAdapter(-1); bool allowDuplicates = true; da.FetchTypedList(fields, categoryTable, bucket, 0, null, allowDuplicates, groupByClause);

Shouldn't this:

// set the relations and their custom filters
IEntityRelation categoryDescsLangsRelation = CategoryEntity.Relations.CategoryDescriptionEntityUsingPublicationIdLevel1Id;
            categoryDescsLangsRelation.CustomFilter = new PredicateExpression(CategoryFields.PublicationId == CategoryDescriptionLangFields.PublicationId);
bucket.Relations.Add(categoryDescsLangsRelation, JoinHint.Inner);

be this:

// set the relations and their custom filters
IEntityRelation categoryDescsLangsRelation = CategoryEntity.Relations.CategoryDescriptionLangEntityUsingPublicationIdLevel1Id;
            categoryDescsLangsRelation.CustomFilter = new PredicateExpression(CategoryFields.PublicationId == CategoryDescriptionLangFields.PublicationId);
bucket.Relations.Add(categoryDescsLangsRelation, JoinHint.Inner);

?? (note the CategoryDescriptionLangEntityUsingPublicationIdLevel1Id; categoryDescsLangsRelation)

Thanks daelmo, I need to update to version 2.5 in order to do this.