Hi!

This is propably a very noobish question. I have this query that I need to work in llblgen pro v2.6.

SELECT DISTINCT REKV.UNAME AS "Init", REKV.FIRSTN || ' ' || REKV.LASTN AS "Navn", AFD.HEAD AS "Afdeling", REKV.DIRECT AS "Tlfnr"
FROM         Hest.CDMUSER REKV INNER JOIN
                      Hest.CDMUD AFD ON REKV.PRIMDEPT = AFD.ID

I thought I should use a dynamiclist and just add the fields as customfields, but that wount give me the Hest.CDMUD table and will ofcourse say that AFD.HEAD is not a valid identificator.

Please bare with my noobness and ask for more information if needed.

The code I tried.

        ResultsetFields fields = new ResultsetFields(4);
        fields.DefineField(CdmuserFields.Uname, 0, "Init", "Rekv");
        fields.DefineField(CdmuserFields.Firstn, 1, "Fornavn", "Rekv");
        fields.DefineField(CdmuserFields.Lastn, 2, "Efternavn", "Rekv");
        fields.DefineField(CdmudFields.Head, 3, "Afdeling", "Afdeling");
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
                adapter.FetchTypedList(fields, rekvirenter, null, false);
        }

Regards Kasper

You should add the involved relation and pass it to the FetchTypedList method:

...
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(CmdUserEntity.Relations.CmdUdEntityUsingPrimDept, "REKV", "AFD", JoinHint.Inner);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
     adapter.FetchTypedList(fields, rekvirenter, bucket, false);
}

You also could:

1. Fetch an CmdUserEntity collection prefetching the desire related entity.

2. Write a custom property for the concatenating fields at CmdUserEntity USER_CODE_REGIONS (or use partial classes).

public string FullName
{
     get 
     {
           return this.FisrtName + " " + this.LastName;
     }
}

1. Once you fetch the collection, you could access the related fields like this:

someUser.CmdUd.Head;

or, you can add Fields on related fields. Then you would access the related field like this:

someUser.Head;

Hi,

thank you for your help. If I use the first option, that will not give me a concatenated field of Firstn and Lastn, but it gives me the inner join of the tables. Is that not possible while still using FetchTypedList, to concatenate the two fields?

I need to bind the FetchTypedList to a read-only formview, is it just as simple using the FetchEntityCollection?

/Kasper

Your code had 2 issues, the first one is the missing relation/join. David's code should help you out in this.

The second one is the concatenation, and this has been asked before here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3503 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14084

Thank you, that first thread is excactly what I need. But, when I implement and run the code it tells me that the second field is an invalid identificator. But when I look into the exception and take the query executed and execute it manually it works fine.

       IRelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.Relations.Add(CdmuserEntity.Relations.CdmudEntityUsingPrimdept, "Rekv", "Afd", JoinHint.Inner);
        ResultsetFields fields = new ResultsetFields(4);
        fields.DefineField(CdmuserFields.Uname, 0, "Init", "Rekv");
        fields.DefineField(CdmuserFields.Lastn, 1, "Navn", "Rekv");
        fields.DefineField(CdmuserFields.Direct, 2, "Tlfnr", "Rekv");
        fields.DefineField(CdmudFields.Head, 3, "Afdeling", "Afd");
        fields[1].ExpressionToApply = new DbFunctionCall("{0}||' '||{1}", new object[] { CdmuserFields.Firstn, CdmuserFields.Lastn });
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
                adapter.FetchTypedList(fields, rekvirenter, bucket, false);
        }

This is the error I get

ORA-00904: "HEST"."CDMUSER"."LASTN": invalid identificator

The query which actually is correct looks like this.

SELECT DISTINCT "LPA_R2"."UNAME" AS "Init", "HEST"."CDMUSER"."FIRSTN"||' '||"HEST"."CDMUSER"."LASTN" AS "Navn", "LPA_R2"."DIRECT" AS "Tlfnr", "LPA_A1"."HEAD" AS "Afdeling" FROM  "HEST"."CDMUD" "LPA_A1" , "HEST"."CDMUSER" "LPA_R2"  WHERE  "LPA_A1"."ID"="LPA_R2"."PRIMDEPT"

Is any more information needed?

/Kasper

I think that's because you have used aliases (which is not needed in your case), and you haven't specified them in the fields used in the DBFunctionCall.

Would you please try the following simpler code instead?

        IRelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.Relations.Add(CdmuserEntity.Relations.CdmudEntityUsingPrimdept);

        ResultsetFields fields = new ResultsetFields(4);
        fields.DefineField(CdmuserFields.Uname, 0, "Init");
        fields.DefineField(CdmuserFields.Lastn, 1, "Navn");
        fields.DefineField(CdmuserFields.Direct, 2, "Tlfnr");
        fields.DefineField(CdmudFields.Head, 3, "Afdeling");

        fields[1].ExpressionToApply = new DbFunctionCall("{0}||' '||{1}", new object[] { CdmuserFields.Firstn, CdmuserFields.Lastn });

        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
                adapter.FetchTypedList(fields, rekvirenter, bucket, false);
        }

This worked great. Thank you. Should've seen the aliases thing, was looking blindly at another example.

/Kasper