The BqlTableMapper example doesn't work for me because Map() is not a member of BqlTableMapper or IBqlTableMapper so it doesn't compile, and the second parameter to the BqlTableMapper type is expecting an IBqlFieldMappingResolver - which the class isn't. So then I tried to use the BqlFieldMapper example, and that mapper definition seems ok because there is a Map() member. However, when I then try the MappedSelect with my BqlFieldMapper classes, the compiler says they need to support IBqlTableMapper. All MappedSelect type derivations seem to only take an IBqlTableMapper.
Anyone try these examples before and get them to work? How did you define your table and/or field mappers and get MappedSelect to work?
Looks like this post from months ago is similar and is unanswered so far…
Best answer by Dmitrii Naumov
Here is an example
using PX.Data; using PX.Data.BQL;
namespace UnionBQLExample { public class DAC1 : PXBqlTable, IBqlTable { public abstract class recordID : PX.Data.BQL.BqlInt.Field<recordID> { } [PXDBInt(IsKey = true)] public virtual int? RecordID { get; set; }
public abstract class parentID : PX.Data.BQL.BqlInt.Field<parentID> { } [PXDBInt] public virtual int? ParentID { get; set; } }
public class DAC2 : PXBqlTable, IBqlTable { public abstract class recordID : PX.Data.BQL.BqlInt.Field<recordID> { } [PXDBInt(IsKey = true)] public virtual int? RecordID { get; set; }
public abstract class parentID : PX.Data.BQL.BqlInt.Field<parentID> { } [PXDBInt] public virtual int? ParentID { get; set; } }
public class DAC1Mapped : BqlFieldMapper<DACUnionProjection, DAC1> { }
public class DAC2Mapped : BqlFieldMapper<DACUnionProjection, DAC2> { }
[PXCacheName("Union")] [PXProjection(typeof(MappedSelect<DACUnionProjection, From<BqlTableMapper<DAC1, DAC1Mapped>, Union<BqlTableMapper<DAC2, DAC2Mapped>>>>))] public class DACUnionProjection : PXBqlTable, IBqlTable { public abstract class recordID : PX.Data.BQL.BqlInt.Field<recordID> { } [PXDBInt(IsKey = true)] public virtual int? RecordID { get; set; }
public abstract class parentID : PX.Data.BQL.BqlInt.Field<parentID> { } [PXDBInt]
@Dmitrii Naumov, That gets me closer, thanks much!. It does compile now. I had an extra class in there that I didn’t need. I’ll report back after testing it more.
After some work to resolve some runtime errors when using my union projection in a GI, I’m much closer. Now I’m getting a SQL error, though, because Acumatica generated an extra column for the first SELECT in the union named DatabaseRecordStatus, but not the 2nd SELECT.
“All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.”
This is apparently a reserved column used for document archival. How do I remove it from the 1st SELECT or add it to the 2nd so the columns match?
