How to fill matrix with the output of recordset query?

[Former Member]

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 · Accepted solution]

HI Roland,

if u can remember, u had asked for some SQL rowcounter function so that u can fetch the row number in the sql query itself.

following is the function (row_number() over (order by NameOfColumn)

select row_number() over (order by docentry) as rowcount, docentry,docnum,..... from ...

hope it helps. (I know is too late, but I just happend to knnw it now )

regards,

Binita

'rowcount' is a keyword so use some other alias for that column.

Edited by: Binita Joshi on Apr 2, 2008 12:37 PM

Sorry Roland.one bad news is, row_number does not work in executequery of datatable(even if u write it in view and fetch it from there).u wont catch any exception but it wont simply print data.

Edited by: Binita Joshi on Apr 2, 2008 12:55 PM

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hello,

The Row_Number() function is not working in SQL 2000. So, is there any way to generate dummy serial no..

thanks in advancce,,,

Regards

Sanjay

[Former Member · Accepted solution]

Hi Roland,

I face the same problem in matrix.loadfromdatasource for the second time will show garbage value in numeric column.

How did you solve it???

Following is my query, I execute the query with B1 datatable. then bind it to matrix column.

SELECT

CAST(row_number() OVER (ORDER BY ORDR.DocEntry) AS INT) AS RowNo,

ORDR.DocEntry, ORDR.DocNum, ORDR.CardCode,

ORDR.DocDueDate, ORDR.DocStatus, RDR1.LineNum,

RDR1.ItemCode, RDR1.Dscription, CAST(RDR1.Quantity AS NUMERIC(19,6)) AS Quantity,

RDR1.Price, RDR1.LineTotal, RDR1.TotalFrgn,

RDR1.WhsCode, RDR1.U_LotNo, RDR1.U_CalWeight,

RDR1.U_WeightQty, RDR1.U_CallOffQty,

ISNULL(RDR1.Quantity,0)-ISNULL(RDR1.U_CallOffQty,0) AS OpenQty,

RDR1.TreeType

FROM

ORDR

INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry

WHERE

ORDR.DocStatus='O' AND RDR1.TreeType='S' AND RDR1.Quantity>0

Thanks for any help...

Hock Soon

[Former Member]

hi

Just check that when ur trying to put value in itemcode no other form is active then the form u r putting itemcode for eg not even choose from the list form should be open

Thanks

Pavana Punja

[Former Member]

Then my friend, u forgot telling the main thing !!

it was the problem. odatatable.clear after every matrix reload is essential. it solved the problem many thanks .

regards,

Binita

[Former Member]

Hi Roland and David ,

thanks both of u for looking into this rather unfathomable problem !! even i get confused.

David, it never happens that my Quantity query writes null values.it always has non zero value.

Roland, as I said, precisely, in add mode, I have just "#" and itemcode column bound to user datasource. as soon as I switch to update mode, i m again rebinding my all columns to the columns of the table into which they should save the respetive values. and consequently, my oMatrix.LoadFromDataSource() code fills up the matrix after passing proper where condition parameters.

but, as I said, some extra rows are printed with 0.00 value.

clue less. let me do some more R n D with databinding.will surely let u know the consequences.

thanks & regards,

Binita

.

[Former Member]

One thought:

What about oDataTable.Clear() ?

(Sorry, I forgot to say: I also do that always beside oMTx.Clear and before the Mtx-Reload).

@David: Thanks for support * schwitz *

[Former Member]

Roland,

if possible, please let me know how can i wipe out the matrix completely without having the quantity/rate type columns with 0.00 values. (please see my previous reply to u in the same thread above ) .its urgent.

regards,

Binita

[Former Member]

I'm doing a simple oMtx.Clear before every RE-loading of a Matrix.

When there is (or are) lines with empty Mtx-Cells of Columns which are bounded to Price, Quantity...etc type I see no way to get rid of the 0,00.

Wasn't the rebinding to a UserDataSource only temporary for the CFL-Problem?

I fear I don't understand the problem correctly....

[Nussi]

Hi Roland,

i was thinking of suggesting her to use CASE.

SELECT CASE IsNull(Quantity,0) WHEN 0 THEN null ELSE Quantity END as 'Quantity'

or maybe to convert it to nvarchar before and than use '' instead of null

what do you think ?

regards

David

[Former Member]

sanjay,

see the thread

David had given some query which should work.

(but how come u have SAP working on SQL 2000? is it old version ? )

regards,

Binita

[Former Member]

Hi Roland ,

The workaround for casting the rownumber to integar worked. for the crypted chaos columns, I had to bind the first "#" column to short number type user datasource.

thanks once again.

( one last question :). even after clearing the matrix, the quantity and measure type columns which were bound to the datatable query columns show 0.00. how can i wipe this out completely? for example, the recordcount for datatable query is 15. now, next time i again bind the same matrix in (update mode) with different datasource, and suppose it has 6 rows to fill, then , it shows 6 rows with perfact data but, rest (14-6) = 8 rows are filled with 0.00. how can I remove that?)

regards,

Binita

[Former Member]

Rolandddd , God bless u for that workaround !!!

it just worked. thanks.

But, u can further help me....as I m stuck up one more time.

I want to print this rowcounter in the first '#' column.I have posted even question on that. it prints 0 in all the rows.

I am binding it the same way I m binding other columns.

oMatrix.Columns.Item("Col0").DataBind.Bind("PopTable", "ROWC")

should it work?

thanks in advance.

[Former Member]

Did the workaround help for the "crypted chaos" column or do you talk about the RowNumbers?

[Former Member]

HI David and Roland,

its wierd but, attaching a datasource query to column which is already attached to quantity type userdatasource doesn't simply work(atleast in my case). I removed the attached userdatasource. and after doing matrix.loadfromdatasource, I had to again re-attach (setbound true) the quantity column to the original column of my database table. it is quite apparent tht if I don't do this , it should show me garbage values next time I load the column,since the container column is now attached to coulmn of query which dosent have any defined type of its own like quantity or varchar. this is extreemly size and type specific.

Please correct if my understanding is wrong(even after spending a whole day to solve it!!).

regards,

Binita Joshi

[Former Member]

thts what David. thts what I am wondering. if I am doing databind to column of itemcode in datatable query to the column of matrix which is attached to userdatasource of type shorttext, it fetches data. but, if I am fetching quantity column to the matrix column which is attached to userdatasource of type dt_quantity, it fetches data for the first time, when I again populate it, it prints garbage, while itemcode shows perfact values, though it is also attached to userdatasource. so whts wrong with quantity type userdatasource???

or I doubt I m doing something wrong!!

[Former Member]

Hi Binita,

I don't know if it helps. I also had problems with (in my case) date format which was displayed wrong in a DataTable-Matrix (20080331 instead of the choosen format 31.03.2008):

[;

It's weird:

The solution was to set DisplayDesc = False for that column.

Cheers,

Roland

[Former Member]

Hi David,

yes. I m writting exactly the same thing and it is not giving me any syntactic or functional error but just printing garbage !!

regards.

Binita

[Nussi]

well Binita,

are you sure the udf is quantity ? because the matrix column is always defined like the field you bind to it

in my sample: if U_Quantity is type quantity it is displayed as float in the matrix.

regards

David

[Nussi]

and another point:

be sure that you dont use UserDataSources

oForm.DataSources.UserDataSources.Add("txtProjekt", SAPbouiCOM.BoDataType.dt_SHORT_TEXT, 😎

and of course not .DataBind.SetBound(true, "", "ColQuantity")

maybe this makes the problem.

David

[Former Member]

implementation error for datatable query

[Former Member]

HI Roland and David,

I know , I am posting this thing really late but, I happend to implement it only now.

one strange error I am facing is, if the column which is attached to userdatasource of type dt_Quantity is databound to one of the column of datatable query, first time it shows proper data. on subsequent immediate fetch(or execution of the same query), all other rows show same data while this particular column shows some garbage like encrypted varchar chararcters!!!

Dont really know, what to do with this.

(also, if i remove userdatasource and attach it to the table column of type quantity, it works just fine. I know, it has been discussed before in the same post that you cant have userdatasource attached to the same column to which you are binding column of datatable query but this thing works just fine with itemcode column of userdatasource of type shorttext. so I was just wondering what's wrong with quantity??)

any clues ??? its urgent

thanks in advance.

regards,

Binita

[Nussi]

do you want to say that the problem is when you use this

oForm.Items.Item("mtxList").Specific.Columns.Item("ColAusdruc").DataBind.Bind "RESULT", "U_Quantity"

that the value is not displayed correctly ?

regards

David

[Former Member]

HI Roland,

I have a reply from SAP and it works fine:

wht u need to do is add the following line to you CFL event.

matrix.Columns.Item("col_1").DataBind.SetBound(True, "", "itcodeDS")

form.DataSources.UserDataSources.Item("itcodeDS").ValueEx = val

matrix.SetLineData(pVal.Row)

even if u have bound the column while loading the form or menu , you will again have to bind it in CFL event..

try it...

Binita

[Former Member]

Hello Binita,

thank you very much for that tip! )

I think it is the best to rebind the CFL-column back to the UserDataSource directly after every oMtx.LoadFromDataSource() with DataTable...

...because if there are functions which are doing calculations on the MTX-rows, these functions can always rely on a UserDataSource for the CFL-column.

If we would do the rebind only at CFL those functions would have to read values sometimes from DataTable and sometimes from UserDataSource.

OK: Cell.Item("XX").specific.value should always work but I try to get rid of EditText.Value/String as much as I can...

Cheers,

Roland

[Former Member]

Hi Roland,

Thanks again for the code. I already tried it with no luck

according to me, to get system CFL work on matrix column, u will have to bind it with user data source and do matrix.setlinedata(rowindex).

But this works only when u don't load the matrix with the datatable but copy it with 'specific.value' property to each column, as I have mentioned in my first scrap. but again , we will be back to square one as it takes minutes to load the data if done that way.

I Have posted the issue to SAP as I want it with datatable only.....will let u know for sure as soon as I get it.

till then..........

regards,

Binita

[Former Member]

Hi Roland and David,

Thank u so very much for replying back. the code given by roland worked perfactly fine. but, It has one problem. in my user defined form, one of the column of the matrix has system CFL attached (CFL for itemcode). to do this, I will have to bind that column to user datasource. now, for attaching datatable, I will have to bind my columns with the columns in the datatable query. after, the matrix get loaded with the code given by Roland, I am adding one empty row and trying to bind the column with user datatsource. it gives me "Matrix-line exists' error. as it requires "matrix.clear()" to work. if I do this, my matrix gets wiped out. now, How can I do this?

Regards..............

[Nussi]

iam not sure if this is your problem but i post it:

if you apply a datasource to a column you can't have any rows already. do this before the matrix has rows.

[Former Member]

Hello Binita,

I've tested the CFL for DataTable-Matrixes in my test-AddOn. But there's still one problem left. Maybe you find it out (I will need this in the future too...).

The ChooseFromList must be added behind the DataTable-Bind, which is done on every MTX-Load (it's not from the sample above, so the UIDs are new ones):

            oDt = oDts.Item("dt_test")

            query = "SELECT  * FROM [@T_CONVTOOLS01]"
            oDt.ExecuteQuery(query)

            oMtx.Columns.Item("0").DataBind.Bind("dt_test", "Code")
            oMtx.Columns.Item("1").DataBind.Bind("dt_test", "U_Alpha01")
            oMtx.Columns.Item("2").DataBind.Bind("dt_test", "U_Price01")
            oMtx.Columns.Item("3").DataBind.Bind("dt_test", "U_Quant01")
            oMtx.Columns.Item("4").DataBind.Bind("dt_test", "U_Date01")


            AddChooseFromLists()

            oMtx.LoadFromDataSource()

...where AddChooseFromLists() is...

  Private Shared Sub AddChooseFromLists()
        Try
            Dim oCfls As SAPbouiCOM.ChooseFromListCollection
            Dim oCons As SAPbouiCOM.Conditions
            Dim oCon As SAPbouiCOM.Condition
            Dim oCfl As SAPbouiCOM.ChooseFromList
            Dim oCflCreationParams As SAPbouiCOM.ChooseFromListCreationParams
            Dim oBtn As SAPbouiCOM.Button
            Dim oMtx As SAPbouiCOM.Matrix
            Dim oCol As SAPbouiCOM.Column
            Dim oEtx As SAPbouiCOM.EditText

            oMtx = oForm.Items.Item("MTX_TEST02").Specific
            oCfls = oForm.ChooseFromLists


            oCflCreationParams = SboCon.SboUI.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_ChooseFromListCreationParams)
            oCflCreationParams.MultiSelection = False
            oCflCreationParams.ObjectType = SAPbouiCOM.BoLinkedObject.lf_BusinessPartner
            oCflCreationParams.UniqueID = "CFL_C1" 
            oCfl = oCfls.Add(oCflCreationParams)
            '#
            '### OPTION:
            '# Shown ChooseFromList restricted by Conditions 
            oCons = oCfl.GetConditions()
            oCon = oCons.Add()
            oCon.Alias = "CardType"
            oCon.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL
            oCon.CondVal = "C"
            oCfl.SetConditions(oCons)
            '#
            '###
            '#
            oCol = oMtx.Columns.Item("1")
            oCol.ChooseFromListUID = "CFL_C1"
            oCol.ChooseFromListAlias = "CardCode"
            '#########################################################
        Catch e As Exception
            Microsoft.VisualBasic.MsgBox(className & ".AddChooseFromLists()" & vbCrLf & "Exception:" & vbCrLf & e.Message.ToString)
        End Try
    End Sub

But I'm running into problems when trying to set the MTX-Cell via DataTable at the CFL-Event (see comment):

            If pVal.EventType = SAPbouiCOM.BoEventTypes.et_CHOOSE_FROM_LIST Then
                Dim oMtx As SAPbouiCOM.Matrix
                Dim oCFLEvent As SAPbouiCOM.IChooseFromListEvent
                Dim oCFL As SAPbouiCOM.ChooseFromList
                Dim oDataTable As SAPbouiCOM.DataTable
                Dim cflID As String
                Dim oDt As SAPbouiCOM.DataTable = oForm.DataSources.DataTables.Item("dt_test")

                oCFLEvent = pVal
                cflID = oCFLEvent.ChooseFromListUID
                'Dim oForm As SAPbouiCOM.Form
                'oForm = SboConnection.SboUI.Forms.Item(FormUID)
                oCFL = oForm.ChooseFromLists.Item(cflID)
                If Not oCFLEvent.BeforeAction Then
                    oDataTable = oCFLEvent.SelectedObjects
                    If oDataTable Is Nothing Then Exit Sub
                    '############## Matrix Test #######################################################################
                    If (oCFLEvent.ItemUID = "MTX_TEST02") Then
                        oMtx = oForm.Items.Item("MTX_TEST02").Specific

                        '### CFL Results to UserForm
                        '#
                        oDt.Rows.Offset = oCFLEvent.Row - 1
                        MsgBox(oDt.GetValue("U_Alpha01", oCFLEvent.Row - 1))
                        MsgBox(oDataTable.GetValue("CardCode", 0))

                        ' PROBLEM - can't get this working - don't know at the moment:
                        ' on other forms SetValue works....
                        oDt.SetValue("U_Alpha01", oCFLEvent.Row - 1, oDataTable.GetValue("CardCode", 0))
                        ' this also gives me problems...:
                        'oMtx.Columns.Item("1").Cells.Item(oCFLEvent.Row).Specific.value = oDataTable.GetValue("CardCode", 0)


                        oForm.Update()
                        '#
                        '###


                    End If

                End If
            End If

The CFL-Form opens and returns the choosen value(s) at the CFL-event. But writing to MTX still not works with this code...

Maybe you find the last piece. If so: please write here.

Cheers,

Roland

p.s.:

David Nussböck wrote

LOL - ROLAND WAS FASTER ))

...just one minute...

[Nussi]

Hi Binita,

here's a sample

oForm.DataSources.DataTables.Add ("RESULT")

oForm.DataSources.DataTables.Item("RESULT").ExecuteQuery ("SELECT U_Ausdruck, U_Verbuch FROM

")

oForm.Items.Item("mtxList").Specific.Columns.Item("ColAusdruc").DataBind.Bind "RESULT", "U_Ausdruck"

oForm.Items.Item("mtxList").Specific.Columns.Item("ColVerbuch").DataBind.Bind "RESULT", "U_Verbuch"

regards

LOL - ROLAND WAS FASTER ))

David

Edited by: David Nussböck on Feb 13, 2008 9:39 AM

[Former Member]

Hi!

You may use the DataTable:

Every time the form loads:

oDts = oForm.DataSources.DataTables
oDts.Add("DT_MYDATATABLE")

Every time the MTX loads:

Dim .......
Dim oMtx......
Dim query As String

query="SELECT U_one, U_two [...] FROM [@MY_TABLE] [...] WHERE [...]"

oDt = oDts.Item("DT_MYDATATABLE")

oDt.ExecuteQuery(query)

With oMtx.Columns
                .Item("0").DataBind.SetBound(True, "", "UDS_0") ' <- one column as UserDataSource
                .Item("1").DataBind.Bind("DT_MYDATATABLE", "U_one")
                .Item("2").DataBind.Bind("DT_MYDATATABLE", "U_two")
End With

oMtx.LoadFromDataSource()

A disadvantage is that there is no line-numbering. Except you bind the first col to a UserDataSource and fill it again line by line after the "power"-LoadFromDataSource. But this is time consuming again.

But the best thing would be if there were a SQL-function which could select the row-number directly in the query. Does anybody know such a wished function "ResultRowCounter()" ?

SELECT dbo.ResultRowCounter(), U_Col1, U_Col2 FROM.......

Cheers,

Roland

Edited by: Roland Toschek on Feb 13, 2008 11:23 AM

wrong UID at "oDts.Add"