Skip to main content
Solved

In BQl how to cast Int to string using Joins

  • Mar 11, 2022 12:59 PM UTC
  • 4 replies
  • 469 views
  • Translate All

Forum|alt.badge.img+4

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

Best answer by Naveen Boga

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

View original

4 replies

jinin
Pro I
Forum|alt.badge.img+12
  • Pro I
  • Mar 11, 2022 1:18 PM UTC

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


                        }

                    }

Translate

Forum|alt.badge.img+4
  • Author
  • Captain II
  • Mar 11, 2022 1:21 PM UTC

hi,

thanks for the reply

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

and also am using in Join Query

Translate

Naveen Boga
Captain II
Forum|alt.badge.img+20
  • Captain II
  • Answer
  • Mar 11, 2022 2:53 PM UTC

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

Translate

praveenpo
Semi-Pro III
Forum|alt.badge.img+3
  • Semi-Pro III
  • Mar 11, 2022 3:18 PM UTC

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

Translate