Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How to fill matrix with the output of recordset query?

Hi all,

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.AddRow()

Matrix.Columns.Item("col1").Cells.Item(i).Specific.value = Recordset.Fields.Item("cardcode").Value

Recordset.movenext()

next

Is there any other way to fill the matrix in this case, which loads the data faster?

Regards,

Former Member
Former Member replied

Hello Binita,

thank you very much for that hint!!

AND:

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:

SELECT

CAST(U_WhatEver AS NUMERIC(19,6))

FROM

...

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:

SELECT

CAST(U_Alpha20 AS NVARCHAR(20))

FROM

...

After that the GUI restricts the user-input to 20 characters

Cheers,

Roland

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question