Solved

In BQl how to cast Int to string using Joins

  • 11 March 2022
  • 4 replies
  • 253 views

Userlevel 1
Badge

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

icon

Best answer by Naveen Boga 11 March 2022, 15:53

View original

4 replies

Userlevel 6
Badge +3

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

Userlevel 7
Badge +11

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


                        }

                    }

Userlevel 7
Badge +17

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!!

Userlevel 1
Badge

hi,

thanks for the reply

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

and also am using in Join Query

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved