Forum Discussion

PB's avatar
PB
New Contributor II
9 months ago

Execute sql on data table

Can I execute an sql statement against a data table created in a business rule?  I created a table to use in a data set business rule, which returns data to the data adapter.    e.g. if I created d...
Admin
Dashboards
  • AndreaF's avatar
    AndreaF
    9 months ago

    The datatable exists within the data set business rule, so yes you can run a query against it, but within its limit of existence, as for any other variable, i.e. you cannot refer to it from other business rules or outside the function or loop where it is defined. However, even within its limit of existence, I believe you cannot refer to it with the "select * from dt1" string. You need to use the appropriate function of the BRApi.Database (I don't remember which one at the moment), and pass the table and where/order by etc. as parameters.

Krishna's avatar
Krishna
Valued Contributor
9 months ago

AndreaF - Yes Data table are in memory and you can manipulate by copying to another DTL or you can use the existing DTL as well. See below an example 

									WFICTable1 = WFICTable.Copy()
											
												Dim distinctValues = WFICTable1.AsEnumerable() _
												.Where(Function(row) row.Field(Of String)("Account") <> "Difference")  _
											    .GroupBy(Function(row) New With {
											        Key.Column1 = row.Field(Of String)("Entity"),
											        Key.Column2 = row.Field(Of String)("IC")
											    }) _
											    .Select(Function(Group) New With {
											        .Column1 = Group.Key.Column1,
											        .Column2 = Group.Key.Column2,
											        .OtherColumn1 = Group.First().Field(Of String)("Account"),
											        .OtherColumn2 = Group.First().Field(Of String)("PartnerCurr"),
													.OtherColumn3 = Group.First().Field(Of Decimal)("RptCurrAmount"),
													.OtherColumn4 = Group.First().Field(Of Decimal)("PrimaryCurrAmount"),
													.OtherColumn5 = Group.First().Field(Of Decimal)("PartnerCurrAmount")
											    })

 

 

  • PB's avatar
    PB
    New Contributor II
    8 months ago

    Thanks Krishna - I'll check it out - looks like your doing something on IC mismatch reporting - that's what I'm working on with this issue too.

    • Krishna's avatar
      Krishna
      Valued Contributor
      8 months ago

      PB - Yes but the concept is the same you can try with your SQL table column. Hope this helps.