JIONs-Query as MatrixDataSourse

[AlexGrebennikov]

Please, help me, my dear friend!

How should i code the following task:

i need to bind with Matrix a result of query-method of DBDataSource object,

which consists of several JOIN-statements

(

..FROM x LEFT JOIN y ON ... LEFT JOIN z ON ...

), and include a subquery in WHERE-statement..

addition for question: how can i code the Condition-object for the following WHERE-part:

...
WHERE
  NOT(x = 1 AND y = 0)
...

[Former Member · Accepted solution]

You can't have several table in a DBDatasource.

So you can't have a JOIN....

for you second question

NOT(x = 1 AND y = 0)

can be

x<>1 OR y<>0

so it would be

dsa_Conditions = New SAPbouiCOM.Conditions

dsa_Condition = dsa_Conditions.Add
dsa_Condition.BracketOpenNum = 2
dsa_Condition.Alias = "x"
dsa_Condition.Operation = BoConditionOperation.co_NOT_EQUAL
dsa_Condition.CondVal = "1"
dsa_Condition.BracketCloseNum = 1
dsa_Condition.Relationship=BoConditionRelationship.cr_OR
dsa_Condition = dsa_Conditions.Add
dsa_Condition.BracketOpenNum = 1
dsa_Condition.Alias = "y"
dsa_Condition.Operation = BoConditionOperation.co_NOT_EQUAL
dsa_Condition.CondVal = "0"
dsa_Condition.BracketCloseNum = 2

[AlexGrebennikov · Accepted solution]

Hi, Sébastien!!

Your 2-nd reply is very helpful, it works..

Thanx!

Is there any work arround to solve the JOIN-trouble?

Can i fill a Matrix thru a RecordSet, for example?

[Former Member · Accepted solution]

Yes you can, you have to link the columns of your matrix to user Data Source

here is a code to do it

msa_Matrix is the matrix

dst_UserDataSourceName(i) is the userdatasource of the column i of my matrix

    Public Sub LoadDataFromSQL(ByVal pst_CodeSQL As String, ByVal psa_Company As SAPbobsCOM.Company, ByVal pbo_FirstColumnToBeIncrement As Boolean)
        Dim dsa_Recordset As SAPbobsCOM.Recordset, i As Int32
        'Get Recordset Object
        dsa_Recordset = psa_Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        'Clear the Matrix
        msa_Matrix.Clear()
        'Query the Recordset
        dsa_Recordset.DoQuery(pst_CodeSQL)
        If dsa_Recordset.RecordCount <> 0 Then
            'The recordset returns more than 1 line
            Do Until dsa_Recordset.EoF
                'for each line of the recordset, I go thru each column of the Matrix
                For i = 0 To msa_Matrix.Columns.Count - 1
                    'dst_UserDataSourceName(i) = the user define DataSource of the column(i) of the Matrix
                    With msa_Form.DataSources.UserDataSources
                        If pbo_FirstColumnToBeIncrement And i = 0 Then
                            'Case when the first column contain an number (1, 2 3 ....10
                            .Item(dst_UserDataSourceName(i)).ValueEx = msa_Matrix.RowCount + 1
                        Else
                            If pbo_FirstColumnToBeIncrement Then
                                'Case when the first column contain an number, The recordset Field must be i-1
                                .Item(dst_UserDataSourceName(i)).ValueEx = dsa_Recordset.Fields.Item(i - 1).Value
                            Else
                                .Item(dst_UserDataSourceName(i)).ValueEx = dsa_Recordset.Fields.Item(i).Value
                            End If
                        End If
                    End With
                Next
                msa_Matrix.AddRow()
                dsa_Recordset.MoveNext()
            Loop
        End If
        System.Runtime.InteropServices.Marshal.ReleaseComObject(dsa_Recordset)
    End Sub

[former_member185703]

Hi Alexey,

Just FYI:

The capability to use SQL statements to fill grids is planned to be available with version 2005.

Regards,

Frank

[Former Member]

What a good news !

thanks Frank

Do you know how we can know the futur feature of the SDK ?