Solved

In BQl how to cast Int to string using Joins

  • 11 March 2022
  • 4 replies
  • 299 views
F
Rank
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

praveenpo
Rank
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

Praveen
J
Rank
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


                        }

                    }

Jini N
Naveen Boga
Rank
Userlevel 7
Badge +19

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

Naveen B
F
Rank
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


Terms & ConditionsCookie settings
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