Skip to main content

Hi,

I have two tables 

CSAttributeGroup

InventoryItem

am join with two columns entityclassid(String) and Itemclassid(Int)

which are int and string but the data is same

i can able to convert int to string in Sql

but how to do same in BQl

Select * from CSAttributeGroup A Join InventoryItem B on A.EntityClassID = cast(B.ItemClassID as varchar(15))
Or A.EntityClassID = B.LotSerClassID
Where B.CompanyID =2 and A.CompanyID=2 and ( A.EntityClassID ='106' or A.EntityClassID='LREX') and B.InventoryID=10759

Please Suggest me with the answers

Thank you

Hi @FarhanaM60 

I faced the same issue before and did a workaround to resolve the issue. Please review the below code sample

 

 foreach (CSAttributeGroup item in PXSelectReadonly<CSAttributeGroup, 
                        Where<CSAttributeGroup.attributeID, Equal<Required<CSAttributeGroup.attributeID>>, 
                        And<CSAttributeGroup.entityType, Equal<Required<CSAttributeGroup.entityType>>>>>.Select(this,”AttributeID”, "PX.Objects.IN.InventoryItem"))
                    {
                       

 INItemClass itemClass = PXSelectReadonly<INItemClass, Where<INItemClass.itemClassID, Equal<Required<INItemClass.itemClassID>>>>.Select(this,Convert.ToInt32(item.EntityClassID));
                        if (itemClass != null)
                        {
                            // Write your logic


                        }

                    }


hi,

thanks for the reply

but am using for the data view it should be data view

and also am using in Join Query


HI @FarhanaM60  I don’t think that is possible to convert the data type in the BQL Query of VIEW.

 

You can achieve this alternative way. Please find the details below.

  1. Create a SQL View for the above query.
  2. Create a DAC for this SQL View(this will be treated a table in Acumatica)
  3. Provide the Key fields in DAC
  4. By using this DAC, you can define a VIEW in your graph.

 

Hope this helps!!


Hi @FarhanaM60 ,

As @jinin  mentioned place that logic in View delegate.

Another approach is creating projection DAC.Please go through below link for sample with this approach.

https://stackoverflow.com/questions/53275019/joining-table-using-pxselectjoin-using-cast-in-bql-statement


Reply