How to fill matrix with the output of recordset query?
I want a user matrix to load data when I hit a button and the data to be filled is the output of an sql query. binding the matrix columns with table columns and then load the matrix with DBDatasource attached to that table will not do in this case as the columns in the sql query are from several different tables or views.
writing following line to do this takes several minutes to load the data:
For j = 0 To Reordset.RecordCount - 1
Matrix.Columns.Item("col1").Cells.Item(i).Specific.value = Recordset.Fields.Item("cardcode").Value
Is there any other way to fill the matrix in this case, which loads the data faster?
thank you very much for that hint!!
I've found a way to use it with DataTable.
The solution is to use a CAST for the ROW_NUMBER-Column:
SELECT CAST( ROW_NUMBER() OVER (ORDER BY T1.U_LogNo ASC) AS INT ) AS RowCnt, T1.Code, T1.U_ParcelNo, T1.U_LogNo FROM [@XXX_EXAMPLE] T1 WHERE T1.U_ParcelNo='132006' ORDER BY T1.U_LogNo ASC
And the CAST leads me back to your problem (some thoughts):
First: I think your UserDataSource must be of the same type as the DB-Table column. Why is your DB-Table column not of a defined type when it's created with SAP or SDK? Is it a calculation like SELECT U_Sum1 + U_Sum2 ?
I think B1 is regarding the DB-Col type - also for DataTable querys because B1 shows (normally...) the correct user defined format for price, date... also for DataTables.
So maybe CAST is helping you too for the DataTable-query. Try to SELECT your problem column with CAST:
CAST(U_WhatEver AS NUMERIC(19,6))
But I also found that B1 does not regard everything of the B1-ColTypes for DataTables. For example a ShortText(20) in a DataTable-Matrix is not always restricted to 20 characters in the GUI. The user can type more than 20 characters without the red alarm-message at the bottom.
Although sometimes this works - it depends on the real column-type in DB that SAP has created: Sometimes it's nvarchar(max) and sometimes nvarchar(20) as expected.
If it's nvarchar(max) then a CAST has helped me again for the DataTable-query:
CAST(U_Alpha20 AS NVARCHAR(20))
After that the GUI restricts the user-input to 20 characters