Myujth

I have a requirement to write a BQL statement for below SQL query

 SELECT * FROM CSATTRIBUTEGROUP INNER JOIN INVENTORYITEM ON CSATTRIBUTEGROUP.ENTITYCLASSID=CAST(INVENTORYITEM.ITEMCLASSID AS NVARCHAR(10))
 LEFT JOIN CSANSWERS ON INVENTORYITEM.NOTEID=CSANSWERS.REFNOTEID WHERE INVENTORYCD='CPU'

The EntityClassID in CSAttribute & ItemClassID in inventory are different type. How to join the table using BQL.

One would be tempted to create a custom view representing the INVENTORYITEM table with a cast of ITEMCLASSID. However custom views are not recommended. Instead try creating a PXProjection to represent the INVENTORYITEM table as usrINVENTORYITEM. Then use a PXString to convert the Int? to a string. Once exposed you can work out your BQL as shown below.

#region InventoryItem - projection
[PXProjection(typeof(Select<InventoryItem>), Persistent = false)]
public partial class usrInventoryItem : InventoryItem


 #region ItemClassIDStr
 [PXString(10, IsUnicode = true)]
 [PXUIField(DisplayName = "ItemClassIDStr", Visibility = PXUIVisibility.SelectorVisible)]
 public virtual string ItemClassIDStr
 
 get
 
 return $"ItemClassID";
 
 set
 
 this.ItemClassIDStr = value;
 
 
 public abstract class itemClassIDStr : IBqlField 
 #endregion

#endregion

then the BQL statement:

// basic example of your join.
 public PXSelectJoin<CSAttributeGroup, LeftJoin<usrInventoryItem,
 On<usrInventoryItem.itemClassIDStr, Equal<CSAttributeGroup.entityClassID>>>> ExampleJoin;